Looking for a way to match records querying 2 different tables

[Shales]

Hi,

I have 2 tables that contain very similar data and I am trying to find matches.

For example, I have Surname1, Surname2, DOB1 & DOB2 coming from 2 separate tables. I am now after a 5th field in my query that that will give me a 1 if it can match either just the 2 surnames or just the DOB, and then a 2 if it matches both.

Hope this makes sense.

Regards
Gavin

 

[Shales]

Might be worth adding that I have already joined the tables on Surname, so the count will always show at least a 1 based on my example. I have more fields I need to look for matches but I'll be able to figure that myself once I can obtain a working formula. Above is just an example of what i am working with.

Thanks

 

[xenou]

Try along the lines of:

Join ....
ON
(
(Surname = Surname)
OR
(Dob = Dob)
)

The field you want will be able to populate with the logic:
((Surname = Surname) + (Dob = Dob)) As MyField

Since MSAccess treats true as the value -1 (negative one) you will get a -1 for one match and -2 for two matches. If you prefer positive numbers then just negate the result:
(((Surname = Surname) + (Dob = Dob)) * -1) As MyField

This is off the top of my head. If problems or if someone has a clearer answer please post again.