Matching records

[Rose]

I might have a few posts today.

In this scenario, I need to match records between two different tables using
multiple fields. We're comparing the records in a new application that is
supposed to find the commission allocations that the old app was missing.
What makes a record unique in both tables is the combination of ordernum,
badgenum and badgenum2 (representing an order, who took the order, and a boss
or associate who is getting a cut of the commission). Each ordernum should
have 7 records because 7 people beyond the order taker get commission.

The Matched/Unmatched Records query is only good for one field to match, but
I need 3 to match before I've got a hit. How do I word this?
Thanks again!

 

[OfficeDev18 via AccessMonster.com]

Hi, Rose,

Assuming you have an ordernum, badgenum, and badgenum2 fields in both tables,
why can't you make the INNER JOIN on all 3 fields having identical data? I'm
confused!

Sam

 

[Rose]

Don't be confused - I haven't done this in maybe years. Besides that some
people looking for help on Microsoft.com are not VBA experts.
Can you be more specific on how to word the code? Not just the join, but
running the match (and then a second query for a list of those that did not
match from one list to the second one).

 

[OfficeDev18 via AccessMonster.com]

Hi, Rose,

I'm hesitant, because I'm not sure of you databases' tables contents, but in
general the statement would go like this:

SELECT * FROM Table1 INNER JOIN Table2 ON Table1.Ordernum=Table2.FieldName1,
Table1.Badgenum=Table2.FieldName2, Table1.badgenum2=Table2.FieldName3....

The reason I'm using FieldNameX instead of the actual names is because the
field names don't have to be the same, as long as the contents is. Please
change all the field/table names to their real names when you transpose the
code.

Hope this helps,

Sam

PS I'd rather work slowly; get one thing working at a time.