Multiple Column Query between two tables

[bdavid50]

Help

I need to generate a query between two table, no one column is unique between the two tables. What is unique to both tables is the combination of 4 columns from each table. The query I need to generate is a difference query between the two tables using 4 columns from each table. How would I go about generating a query like this.

Thanks in advance for your help
Bdavid50

 

[HowieG]

Try something like:

SELECT tblTableA.Field1, tblTableA.Field2, tblTableA.Field3, tblTableA.Field4, tblTableA.Desc, tblTableB.Field1, tblTableB.Field2, tblTableB.Field3, tblTableB.Field4, tblTableB.Date, tblTableB.Desc
FROM tblTableA INNER JOIN tblTableB ON (tblTableA.Field1 = tblTableB.Field1) AND (tblTableA.Field2 = tblTableB.Field2) AND (tblTableA.Field3 = tblTableB.Field3) AND (tblTableA.Field4 = tblTableB.Field4);

(One JOIN with four conditions in the ON statement)

You can do this in design view by dragging each field over to its counterpart, so you'd have 4 lines connecting the two tables.

I'm just experimenting with this myself, so let me know how you make out.

 

[HowieG]

Incidentally, you can create this multi-field relationship in the relationships window by selecting four fields in the first table, and dragging them to the second. A window will pop up, just select the equivalent four fields under "Related Table/Query"

...Learning as I go...

 

[bdavid50]

Howie

Thanks a lot, I never thought about doing it with 4 joins in one query. I will give it a try this morning and let you know how I made out.

bdavid50

 

[bdavid50]

The query did what it was designed to do but I did not explain myself clearly enough early, what I am wanting to do with this query is to find out what items don't match based on a join of 4 column names. I tried using the Access Query Wizard for finding unmatched data but it only allows you to choose one set of matched records. Any ideas would be appreciated.


Thank you
Bdavid50

 

[HowieG]

Go through the wizard as if you only had one field, then in the design view, create the other three links like you did earlier. Make sure the connection lines are the same type (right click on each line and make sure the new ones match the one the wizard created).