Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2006
    Posts
    213

    Unanswered: sql for access query

    Im importing a file into access and i want to add querys. The problem is the field im querying wont always be in the same place or be the same name. The only thing that remains the same is that the field i want to query will always be the last column. Anyone know what to do for this??

    Thanks
    Marley.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    You are looking at code to do this I am afraid - SQL alone can't handle it.

    You will want to do something like:
    declare a DAO TableDef object. Instantiate it to your table. Read the name of the last field. Have vb knock together your SQL statement, using the field name you have just looked up and the table name.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by marleyuk
    Im importing a file into access and i want to add querys. The problem is the field im querying wont always be in the same place or be the same name. The only thing that remains the same is that the field i want to query will always be the last column. Anyone know what to do for this??

    Thanks
    Marley.
    Querying in any database can't help you on this ... There is no SQL construct that I know of that will query on "the last column" of a table ... Every DB I've seen needs at least a column name. You might want to look at your import specification to see if you cannot get a standardized column name ...
    Back to Access ... ADO is not the way to go for speed ...

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by pootle flump
    Hi

    You are looking at code to do this I am afraid - SQL alone can't handle it.

    You will want to do something like:
    declare a DAO TableDef object. Instantiate it to your table. Read the name of the last field. Have vb knock together your SQL statement, using the field name you have just looked up and the table name.

    Pootle to the rescue ...
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by M Owen
    There is no SQL construct that I know of that will query on "the last column" of a table ... Every DB I've seen needs at least a column name.
    I have a feeling I had seen an rdbms that allowed you to select columns based on their ordinal position (I know Access lets you use ordinals in the order by clause) but I couldn't get it to work. If it had then getting the last column to be moved to the first might have done it....

    Perhaps I just imagined the ordinal thing
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by pootle flump
    I have a feeling I had seen an rdbms that allowed you to select columns based on their ordinal position (I know Access lets you use ordinals in the order by clause) but I couldn't get it to work. If it had then getting the last column to be moved to the first might have done it....

    Perhaps I just imagined the ordinal thing
    Dan,

    Only problem is (even with ordinals), you have to know WHAT the last column # is ... Specific: I can't think of a rdms that has a query like: SELECT [The Last Column Exact # Unknown] FROM SomeTable; ... When I do ADO recordsets, I STRICTLY use the ordinal position when referencing the columns ... I've been burned in the past where the driver gets confused as to what column to return ...

    - Mike
    Back to Access ... ADO is not the way to go for speed ...

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by M Owen
    Only problem is (even with ordinals), you have to know WHAT the last column # is ... Specific: I can't think of a rdms that has a query like: SELECT [The Last Column Exact # Unknown] FROM SomeTable; ...
    Oh aye - my suggestion was to see if you can't get the last column to come in first Academic either way - it ain't supported. SQL Anywhere was the dbms I had in mind btw.
    Quote Originally Posted by pootle
    If it had then getting the last column to be moved to the first might have done it....
    Quote Originally Posted by M Owen
    When I do ADO recordsets, I STRICTLY use the ordinal position when referencing the columns ... I've been burned in the past where the driver gets confused as to what column to return ...
    Yeah - I need to get a bit more disciplined with that sort of thing
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by pootle flump
    Oh aye - my suggestion was to see if you can't get the last column to come in first Academic either way - it ain't supported. SQL Anywhere was the dbms I had in mind btw.



    Yeah - I need to get a bit more disciplined with that sort of thing
    Yes. Indeed. The funny thing about Queries and recordsets is that when you query for the same named column from 2 different tables, you qualify that name with the corresponding table name (ok so far ...) BUT in the returned recordset, ONLY the column names comes back. The table from whence they came is lost. THEN YOU MUST use the ordinal position to get the correct column ... Then, you'll never go back ...
    Back to Access ... ADO is not the way to go for speed ...

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Or you know, you could provide meaningful aliases...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  10. #10
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Teddy
    Or you know, you could provide meaningful aliases...
    You take all the fun out of this job Ted ...
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •