Comparing 'matched' data

[Leslie Isaacs]

Hello All

I have two large tables {each with around 4500 records}:
NHSPA with the following fields
[firstname]
[surname]
[dob]
[comdate]
[sdnumber]
[ninumber]

and STAFFS with the following fields
[first_name]
[surname]
[dob]
[com_date]
[sd_number]
[ni_number]

I need a query that will check for matched records in the tables, but the
trouble is that it is unknown which - if any - of the fields match exactly.
The rule for determining a match would be that one or more of the following
3 conditions must be met:

condition 1
[firstname] = [first_name] and
[surname] = [surname] and
[dob] = [dob]

or
condition 2
[sdnumber] = [sd_number]

or
condition 3
[ninumber] = [ni_number]

The query needs to identify all matched records, and for these records it
needs to return any mis-matches in the other fields.
e.g.
For all matches identified under condition 1 the query must return any
records where
[comdate] <> [com_date]
or
[sdnumber] <> [sd_number]
or
[ninumber] <> [ni_number]

I think I can do this with 3 seperate queries {one corresponding to each
condition}, but it would be better if it could be done with one query -
partly because I need to display all the results in one report. I cannot
work out how to do this though.

Hope someone can help.

Many thanks
Les

 

[Leslie Isaacs]

I have just realised that my statement below that each of the tables has
around 4500 records is wrong - and that this may be significant. In fact
NHSPA has around 50 records, and STAFFS has around 4500.
I wish I could get this myself, but I can't seem to.
Thanks for any help.
Les

 

[John Spencer]

Sounds as if you would need a UNION query using the three queries with
various combinations of criteria. Something like the following

SELECT *, "Name DOB" as MatchType
FROM NHPSA INNER JOIN STAFFS
ON NHPSA.FirstName = STAFFS.First_Name
AND NHPSA.Surname = STAFFS.Surname
AND NHPSA.DOB =Staffs.DOB
WHERE NHPSA.ComDate <> Staffs.Com_date
OR NHPSA.sdNumber <> Staffs.sd_Number
OR NHPSA.niNumber <> Staffs.NI_Number
UNION ALL
SELECT *, "NI Number" as MatchType
FROM NHPSA INNER JOIN STAFFS
ON NHPSA.NiNumber= STAFFS.Ni_Number
WHERE NHPSA.ComDate <> Staffs.Com_date
OR NHPSA.sdNumber <> Staffs.sd_Number
OR NHPSA.Surname <> STAFFS.Surname
OR NHPSA.DOB <>Staffs.DOB
OR NHPDA.FirstName <> Staffs.First_Name
UNION ALL
SELECT *, "sdNumber" as MatchType
FROM NHPSA INNER JOIN STAFFS
ON NHPSA.SdNumber= STAFFS.sd_Number
WHERE NHPSA.ComDate <> Staffs.Com_date
OR NHPSA.niNumber <> Staffs.ni_Number
OR NHPSA.Surname <> STAFFS.Surname
OR NHPSA.DOB <>Staffs.DOB
OR NHPDA.FirstName <> Staffs.First_Name

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Jack Isaacs]

John

Thanks for your suggestion - which worked a treat!

Thanks again
Les

Sounds as if you would need a UNION query using the three queries with
various combinations of criteria. Something like the following

SELECT *, "Name DOB" as MatchType
FROM NHPSA INNER JOIN STAFFS
ON NHPSA.FirstName = STAFFS.First_Name
AND NHPSA.Surname = STAFFS.Surname
AND NHPSA.DOB =Staffs.DOB
WHERE NHPSA.ComDate <> Staffs.Com_date
OR NHPSA.sdNumber <> Staffs.sd_Number
OR NHPSA.niNumber <> Staffs.NI_Number
UNION ALL
SELECT *, "NI Number" as MatchType
FROM NHPSA INNER JOIN STAFFS
ON NHPSA.NiNumber= STAFFS.Ni_Number
WHERE NHPSA.ComDate <> Staffs.Com_date
OR NHPSA.sdNumber <> Staffs.sd_Number
OR NHPSA.Surname <> STAFFS.Surname
OR NHPSA.DOB <>Staffs.DOB
OR NHPDA.FirstName <> Staffs.First_Name
UNION ALL
SELECT *, "sdNumber" as MatchType
FROM NHPSA INNER JOIN STAFFS
ON NHPSA.SdNumber= STAFFS.sd_Number
WHERE NHPSA.ComDate <> Staffs.Com_date
OR NHPSA.niNumber <> Staffs.ni_Number
OR NHPSA.Surname <> STAFFS.Surname
OR NHPSA.DOB <>Staffs.DOB
OR NHPDA.FirstName <> Staffs.First_Name

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Hello All

I have two large tables {each with around 4500 records}:
NHSPA with the following fields
[firstname]
[surname]
[dob]
[comdate]
[sdnumber]
[ninumber]

and STAFFS with the following fields
[first_name]
[surname]
[dob]
[com_date]
[sd_number]
[ni_number]

I need a query that will check for matched records in the tables, but
the
trouble is that it is unknown which - if any - of the fields match
exactly.
The rule for determining a match would be that one or more of the
following
3 conditions must be met:

condition 1
[firstname] = [first_name] and
[surname] = [surname] and
[dob] = [dob]

or
condition 2
[sdnumber] = [sd_number]

or
condition 3
[ninumber] = [ni_number]

The query needs to identify all matched records, and for these records it
needs to return any mis-matches in the other fields.
e.g.
For all matches identified under condition 1 the query must return any
records where
[comdate] <> [com_date]
or
[sdnumber] <> [sd_number]
or
[ninumber] <> [ni_number]

I think I can do this with 3 seperate queries {one corresponding to each
condition}, but it would be better if it could be done with one query -
partly because I need to display all the results in one report. I cannot
work out how to do this though.

Hope someone can help.

Many thanks
Les