Selecting data from MS Access table columns based on column location instead of colum

[rashokku]

I have a table name "Patient" in MS Access database 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

[rashokku]

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.

21080

[jonh]

Off the top of my head there are a few ways you could probably do this.

Open the file and change the headers.
Import.

Or, import everything as text into a temp table, column headers no.
Delete row 1. (e.g. the headers)
Insert to destination.

Or, connect the file as a linked table and use a query or build an insert into statement in vba.

[jonh]

cross posted

https://www.access-programmers.co.uk/forums/showthread.php?t=297195

[OBP]

The correct terminology in Access is a Field unlike Excel where it is a Column. The table has a Fields Collection which can be referred to by it's position in the collection.
You can either import the data in to a new table and transfer it to the Master table, you can refer to either table by Filed position or both tables by field position.
Or you can use the VBA to import the data directly in to the Master table.