Multiple lookups in same table

kev_79 · February 13th, 2004, 09:41 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

Clive Astley · February 13th, 2004, 02:32 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

kev_79 · February 13th, 2004, 03:49 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

Steven · February 15th, 2004, 05:38 PM

Access [u]shouldn't</u> 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:

Code:

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