Results 1 to 5 of 5
  1. #1
    rashokku is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Nov 2017
    Posts
    3

    Selecting data table columns based on column location instead of column names

    I have a table name "Patient" in MS Access and the table has following columns.




    Member_ID (First Column)
    Member_Name (Second Column)
    Member_DOB (Third Column)
    Member_Address (Fourth Column)
    Member Zip (Fifth Column)


    I would like to select the columns based on the column location/number/sequence than the column name.


    What is the syntax in SQL to locate the column location?


    Thanks for your help,
    Ram

  2. #2
    rashokku is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Nov 2017
    Posts
    3
    We have health plan data file that need to be imported in to MS Access table. The column header names keeping changing or not constant/fixed
    but the column location/sequence/order is fixed. So, instead of selecting/reading the columns by column names/header names, I would like
    to read the columns by column number/column locations and import it into exsiting table or drop existing table & create new table.


    Please see the attachment for example.
    Click image for larger version. 

Name:	11-28-2017 11-24-41 AM.png 
Views:	21 
Size:	8.7 KB 
ID:	31451

  3. #3
    Join Date
    Apr 2017
    Posts
    1,673
    The field name in database systems is an essential feature. It's location in table is really irrelevant.

    Columns are spreadsheet things (we sometimes name fields in Access columns too, but really it is a sloppy use of termins). In spreadseet, the position is main characteristic - mostly you can make calculations withoud using column headers at all.

    The only way I think what you want is possible, is to read the whole Excel table into recordset (in VBA module), manipulate it there, and write the result into standard Access table row-wise executing composed query string.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  5. #5
    NoellaG's Avatar
    NoellaG is offline VIP
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi,
    a simple solution would be to rename the column names in Excel, you can easily write a macro to do this.
    Kind regards
    Noëlla

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 07-06-2016, 09:48 AM
  2. Replies: 15
    Last Post: 07-30-2015, 01:38 PM
  3. Replies: 1
    Last Post: 11-12-2013, 02:19 AM
  4. Replies: 3
    Last Post: 08-08-2011, 11:02 AM
  5. Replies: 5
    Last Post: 04-24-2011, 03:14 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums