Query that references the name of a field rather than the value

[BillMcD37]

I have a table that has the following field names in each record.

Field name Field value

ProjectID 12345
Y1 address1
Y2 address2; address 3; address4
Y3 address6

I have a query that pulls data from this table plus pulls one field from another table that is the "primary address' for each projectID. For example, Project ID 12345 has its primary address key in field Y1 (address1) while another Project ID has its primary address key in Field Y3 (address6).

I want to reference the field name ("Y1" or "Y3") based on the primary address key from the query but I cannot figure out how to reference the field name based on the value of the primary address field.

Anyone have any suggestions?

Thanks in advance,
Bill:banghead:

 

[plog]

How do you know where the primary address is going to be? Do you have another field for that? If so, what's its name?

 

[jdraw]

Bill,
We have no idea what you're trying to do nor what you currently have. How about you start by telling us WHAT you are trying to do --- just pretend you're in a line up at McDonalds, you turn to the person behind you who has no idea what database is, no familiarity with you or your company and you tell him/her WHAT you are trying to do.
What do you say to him?

 

[BillMcD37]

Each column contains the addresses of the program's team members by Function. Y1 is always only one member who is the Primary Internal Technical Contact. Y2 always has secondary internal team members and so on. This is standard within the organization.

The business rule that determines which address is the primary address is determined based on the type of program. Internal Financial programs go to the Primary Internal Financial contact ID --always found in Column Y3. External Technical programs always get Y5 assigned as the Primary contact.

Column 1 ProjectID
Y1 Primary Internal Technical contact
Y2 Secondary internal Technical contacts
Y3 Primary Internal Financial Contact
Y4 Secondary internal Financial Contacts
Y5 Primary External Technical contact
Y6 Secondary External Technical contacts
Y7 Primary External Financial contact
Y8 Secondary External Financial contacts

These columns contents are always the same--every project has at least the Primary Internal Technical and the Primary Internal Financial filled in, but there can be 8 columns of addresses--Y1 through Y8.

I have a table built from a crosstab that has projects as the row and Functions as the columns. The crosstab works fine.

What I need is a way to pick a column based on the business rule that identifies which is the primary contact for that particular program.

Example, Project 1 has a Rule assigned to it that says Column Y3 is the primary contact. Project 2 has a Rule assigned to it that says Column Y1 is the primary contact. How do I reference the appropriate column's contents to get the right Primary contact so that I can send the report to him first and all the others as CC's in the email list?

Sorry if I'm not clear. I know the data very well, but I don't know the technology that well.

Thanks for your help.

 

[plog]

Pat wins. You need to normalize your data. Once you do, obtaining the primary address for each project becomes trivial.

http://en.wikipedia.org/wiki/Database_normalization