Query with two lookups to same table

[atledreier]

Hello!

I have a table with cable info:

CabName - CabDescription - FromTag - ToTag - more fields of data.....

The FromTag and ToTag are lookups to the Tags table below

I have another table with Tag info:

TagName - TagDescription - more fields of data.....


What I need to do is this:

I need a query that return the field like this:

CabName - CabDescription - FromTag -FromtagDescription - ToTag -
ToTagDescription.

I've tried alot of stuff, but I just can't make it work.
The CabName and TagName are indexed and used as primary keys for each
table.

Any hints to how I can do this?

 

[John Spencer]

You need to include the Tag Table twice in your query.
If you are using design view, the second instance of the Tag table will be
labeled Tag_1.

Now
-- Join [Cable].[FromTag] to [Tag].[TagName] (or whatever field is the join field)
-- Join [Cable].[ToTag] to [Tag_1].[TagName]

You now should be able to get the associated fields from each instance of the
Tag table.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Jeff Boyce]

You don't mention whether your tables are using "lookup" data types or not
.... this can cause confusion and difficulties.

If not,

open a new query in design view.

add both tables.

join the tables on their common field (i.e., the FromTag joined to the
???? field).

add another copy of the "tag" table.

join the cable table to the second tag table (i.e., the ToTag joined to
the ???? field).

Select the fields you want to display.

Run it.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/