how to pull records based on multiple field criteria

[gs2222]

Hello. I know this should be fairly simple, but it has literally been almost 10 years since I've had to to any real hands on with Access. And my google skills just don't seem to be working on this one.



Here is what I have:
I have two tables that have some fields in common.

Table 1 is a list of records with information to move to a new system. Essentially, it lists payroll withholdings. Each employee is identified by their employee number. As there are multiple withholdings, there are multiple recurrences of each employee ID. The two columns I am concerned about here are employee number and DeductNum, which is just an identifier of the type of deduction.

Table 2 is a list of records from the last pay period. Essentially, it also lists payroll withholdings, but with different fields (amounts, etc). Just as in Table 1, each employee is identified by their employee number. There are multiple occurrences of each employee ID listing what DeductNums were actually taken out of the employee's check.

What I need to do is identify all of those records in Table 1 that do not have matching DeductNums for each Employee ID. I.E. Match each employee ID and DeductNums in Table 1 against the corresponding employee ID and DeductNums in Table 2. If an employee has a DeductNum that appears in Table 1 but is NOT in Table 2, then I need the employee record from Table 1.

I think I'm getting lost somehow with the multiple occurrences of Employee ID. I've been playing with this most of the afternoon and am going a bit brain dead, so I wouldn't be surprised if I'm missing something very simple.

I know this should be fairly simple. I can figure out the logic. For each Employee ID and DeductNum in Table 1, return the record if there is not a corresponding Employee ID and DeductNum in Table 2. I just can't figure out how to get Access to let me do this.

Any help greatly appreciated.

Thank you.

[ssanfu]

There is a wizard, "Find Unmatched Query Wizard", to help create the query. Have you tried that?

[gs2222]

Unfortunately, yes, I tried the unmatched query wizard. I can't figure out how to get it to work with two field criteria. I can pull unmatched employee IDs, or unmatched DeductNum, but not unmatched Deduct for each instance of every employee ID. Good thought, but sorry.

Any other suggestions?

Thank you.

[gs2222]

Okay, I figured it out, so I'm putting it here in case anyone happens to need it for future reference.

Went to Create -> Query Design
Added both tables and drew relationships between the Emp ID in each table, and the DeductNum in each table.
Then right clicked on the Emp ID relationship line and opened join properties. Selected "2", "Include ALL records from Table 1 and only those records from Table 2 where the joined fields are equal." Hit OK to save
Did the same for the DeductNum relationship.
Then dragged fields I wanted down to the query builder grid.
Next went to Criteria under Emp ID for Table 2 and entered "is null"
Did the same for Criteria under DeductNum for Table 2.
Saved and ran the query.
It returned the correct results..

Thank all of you who looked and thought about this, and many thanks to ssanfu for the suggestion.

Hope this comes in helpful to someone someday. Probably me the next time I need to do it, lol.