How to join 2 fields to another field?

  • Thread starter Thread starter shelter
  • Start date Start date
S

shelter

I am running Access 2003 in a WinXP environment.

One table includes a field named sysbreedprimaryID and a field named
sysbreedsecondaryID. These fields each contain a number that
cross-references a breed in another table. The other table includes a
field named sysbreedID and a text field named breed.

My trouble is that I cannot figure out how to return a field that
combines the two breeds: breedprimary/breedsecondary as text, not the
ID numbers. Ex: lab/shepherd not 24/119.

Thank you for your time.
 
You need a query that links all three tables. You need to set the links to
be left join then set the description field criteria to Is Not Null. Make a
third field that is a concatenation of the two descriptions. This will give
you what you need.


SELECT Prim.Desc, SecondID.Desc, [Prim.Desc] & "/" & [SecondID.Desc] AS
Expr1
FROM ([primary] LEFT JOIN Prim ON primary.primID = Prim.PrimID) LEFT JOIN
SecondID ON primary.secID = SecondID.SecondID
WHERE (((Prim.Desc) Is Not Null) AND ((SecondID.Desc) Is Not Null));
 
You need to include the cross-reference table twice in your query. Link the
primaryId to one copy of the table and link the secondaryId to the other
copy of the table.

If you are doing this in the query grid, the second copy of the table will
have "_1" appended to its name.
 
"...include the cross-reference table twice in your query..." worked
like a charm. Thank you!
 
Back
Top