Assigning a value based on the imported field name?

[tad0075]

Hey guys -
I've been getting a lot of help from searching your forums, but I have a problem I haven't been able to solve yet.

I'm setting up a db that will house data imported from small sets. One key component is the location the data came from (it's reading water pressure at different points around the city). The location isn't named in the table, but the column headings include the location... ie: "locationname locationcode min/avg/max." The raw data sets are in tables like:

timestamp - minvalue - avgvalue - maxvalue

And I want my resulting data set to look like:

primarykey - location - timestamp - minvalue - avgvalue - maxvalue

... with the location acting as a foreign key to details on the location of the read. With the locations indicated in the column heading, is there any way to read the column heading to assign a location? I'll also be looking into some sort of VBA-automated import, so if anything involves formatting on import I can make that work.

Any suggestions?

-Tyler

 

[tad0075]

Came up with a solution for this after I slept on it.

Set the spec to import without using first row as column heading. Then, part of the import query runs a DLookUp to find the column name based in min of ID (auto-numbered), and this gets plugged into the location name in the master table. Works great, except the DLookUp seems to be running on every row of the import, making the query run a bit slow. I'm sure this can be fixed by setting up a TempVar to be assigned as the location name before the import query runs.

I would love to hear any better ideas if anyone has them

-Tyler