Complex query on 3 tables

[ejhatch]

I need to create a query based on the following 3 tables.

table PA0001 has the following entries (first line is the header data)

EmployeeNumber - ContractType0001
1234 - 1
1235 - 2
1236 - 1
1237 - 1
1238 - 2
1239 - 3
1240 - 2

PA0016 has the following entries:

EmployeeNumber - ContractType0016
1234 - 11
1235 - 12
1236 - 13
1237 - 11
1238 - 11
1239 - 13
1240 - 12

There is a mapping table called, lets say tblMappingTable which has the following fields:

Contract0001 - Contract0016
1 - 11
2 - 12
3 - 13

Basically I need to write a query which interrogates all the records PA0001 checks the mapping table and gives me all the records in PA0016 which do not have the expected mapping result.

So based on the above data I would expect it to return only the non-matched data, which will be:

EmployeeNumber - ContractType0001 - ContractType0016
1236 - 1 - 13
1238 - 2 - 11

I would probably need to do another query which does the exact same thing but the other way around. It would need to interrogate all the records from PA0016 and using the mapping table check the records in PA0001. In my example above I would get the exact same result, but I suspect with large amounts of data, I would get different results, especially if the number of records were not the same in both tables.

I suspect I would need to write some SQL code or VB code to extract the above.

If one of you kind folk could give me some basic pointers that would be great.

Thanks,

Evan

 

[MStef]

Hello Evan!

Look at "DemoComplexQueryA97.mdb"
I think it is what you need. Open qry "PA0016 Without Matching Query3"

 

[ejhatch]

Hi MStef,

Thanks very much for that. It seems to work perfectly.

Thanks,

Evan