Can one Column Lookup Different Tables...

[chris9499]

If I Use a look up to a table containing data A through C in column 1 is there a way to have column 2 reference table "aa" if column 1 = B, table "bb" if column 1 = B, and so on?

Is there maybe some logic that I am not applying which would create a simple solution.

Thanks for any help. I am new with it, but learning pretty quickly.

 

[PHV]

Have a look at UNION query:
SELECT T.column1, T.column2, A.ValueField AS [Value]
FROM yourTable AS T INNER JOIN tableAA AS A ON T.column2 = A.KeyField
WHERE T.column1 = 'A'
UNION SELECT T.column1, T.column2, A.ValueField
FROM yourTable AS T INNER JOIN tableBB AS A ON T.column2 = A.KeyField
WHERE T.column1 = 'B'
UNION SELECT T.column1, T.column2, A.ValueField
FROM yourTable AS T INNER JOIN tableCC AS A ON T.column2 = A.KeyField
WHERE T.column1 = 'C'
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[chris9499]

thanks PHV, a new toy to work with...

what do the T.* and the A.* indicate prior to the column names?

 

[PHV]

They are alias for table names.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[chris9499]

ok - i am writing a program to track stats for different sports. it started out in excel and the spreadsheets are becoming so complex and i'm having to type info in repeatedly that this is an undertaking i am willing to try.

just want to make sure that you understood what i was trying to do because as i review the code i don't know if it will kick out the results that i was trying.

in excel i would set up a double validation list...

in column "Sport" i will select NFL or MLB. the adjacent column is "Team." what i was wondering was if this union will create a situation whereby if i select MLB as my data under the Sport header will i then have a lookup listing the MLB Teams?

Therefore, if i select NFL i'd like to get a lookup of NFL teams in the adjacent field, and if i select MLB i'd like to get a look up of MLB teams in the same field.

Is this what the union will do for me?

 

[lespaul]

try something like this:

SportsLeagues
LeagueType
NFL
MLB
NHL

LeagueTeams
TeamID
TeamName
LeagueID (FK to SportsLeagues)

You may want to read The Fundamentals of Relational Database Design


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[chris9499]

Anything worth doing is a lot more difficult than it's worth." That's pretty good.

Really appreciate the assistance, I'll get after my help menu and this article and get something worked out.

Best regards.