Creating field names from table values

[apoena]

Hey everybody

Is there any way I can have my field names in a query be populated from values in a different table?

I have 2 tables. The first with all the data I want to show under generic field names such as Location1, Location2, etc. The second table has the actual location names, such as Minneapolis, Detroit, Denver, etc. (each in different fields, so it's a table with one line of data). So basically, one has all the data and the other has the field names for the data. Both look exactly the same - same number of fields and same field names.

I hope this wasn't confusing. Let me know if you need more information.

Thanks in advance!!

 

[apoena]

anybody? ;-)

*bump*

 

[loveTech]

use an UPDATE STATEMENT

UPDATE [tableWithCorrectFieldName] SET [tableWithCorrectFieldName].Field1 = [TableWithData.]Filed
WHERE (([tableWithCorrectFieldName] = "Detroit"

 

[cmmrfrds]

Why do you need the location names in a table. Why not just put the location names in the header line of your Form or Report.

 

[apoena]

lovetech:
My problem there is that you are hardcoding "Detriot".... I use this database as a template to all the customers I create, so for Customer1, I'll create Datebase1, and Location1 will be Minneapolis. Now for Customer2, I'll use the same template database to create Database2, and the Location1 will be Detriot - for example.

I have a bunch of queries and forms that runs off those tables, so it's too time consuming to actually name those location fields what they are supposed to be - I would then have to redo all my form links, queries, etc.

My plan is to have the generic names at all times (Location1, Location2, etc.) and have one "key" table where I could easily input the right location name (Minneapolis, Detroit, etc.). From this I would like to update all my form and report headers - which isn't to hard. But I also have a couple queries that I need with the Minneapolis & Detroit as the field names instead of the Location1 & Location2.....

Does that make sense?

cmmrfrds:
I think that also answers your question too.

Thanks for your time guys, I really appreciate the help!!

 

[apoena]

*bump*

 

[loveTech]

I can't think for you apoena... it was an example for you to build upon...

 

[cmmrfrds]

Here is another way to do it, but like your solution it has its own set of limitations.

Since you know when you are creating the database for the customer the location and other variables, create the table(s) dynamically and assign the field names to match the customer. The problem is you would need to use Select * type queries, but this will probably be okay on a small database. You could also assign the caption for the field name when you are creating the field.

You can create tables dynamically using either the DAO or ADOX object model.

 

[apoena]

Thanks for the help guys. Sorry if I was a little insistent, but I wanted to make sure I explained the problem correctly. I knew this was a tough one - just hoping somebody knew a shortcut around it.

Two heads think better than one. That's what this forum is all about, right? ;-)