|
Subject:
|
Multiple lookups in same table
|
|
Posted By:
|
kev_79
|
Post Date:
|
2/13/2004 8:41:58 AM
|
I have a collection table that has a collector listed in it up to 5 times. The table uses the "collectors" table to populate the lookup, and that table is dynamic.
I am having trouble using the query grid and getting the names to show up in the collection table. When I link it, I can only link it to one, b/c when I do a second it gets confused.
How do you handle this.
Thanks,
Kevin
|
|
Reply By:
|
Clive Astley
|
Reply Date:
|
2/13/2004 1:32:55 PM
|
Sorry Kevin, but I think you'll have to give more details of what you are trying to achieve. I read your query several times and still can't work out what you mean.
Clive Astley
|
|
Reply By:
|
kev_79
|
Reply Date:
|
2/13/2004 2:49:59 PM
|
The tables are Collector and Collection
Within Collection, collector.CollectorID=Collection.CollectorID
Except, Collection has Collector1, Collector2 and Collector3 all related to the Collector lookup table.
When I query using query grid, I can only select Collector once, otherwise it gets confused.
I am just wondering how to do this and get it to return the names of the collector for all 3 fields. The true data in the field is the primary key of Collector and I need it to reference the name field in collector.
Thanks,
Kevin
|
|
Reply By:
|
Steven
|
Reply Date:
|
2/15/2004 4:38:19 PM
|
Access shouldn't get confused by that, it should just add "Collector" with an Alias, like "Collector_1", in which case you can just create the relationship between the fields, which is fine. So that in the end, if you looked at the SQL of the query, it would be something like:
SELECT *
FROM ((Collector INNER JOIN Collection ON Collector.CollectorID = Collection.Collector1)
INNER JOIN Collector AS Collector_1 ON Collection.Collector2 = Collector_1.CollectorID)
INNER JOIN Collector AS Collector_2 ON Collection.Collector3 = Collector_2.CollectorID;
Steven
I am a loud man with a very large hat. This means I am in charge
|
