Compare mulitple fields in two tables

[Guest]

I have two identical tables. Information is input into each table by two
seperate sources. I would like to have a query that compares the fields in
each table and list those records with any field that does not match. The
code below does not work, but I am trying to get something along these lines
that does. I want it to compare 3 fields in two idential tables and list the
CLNum if any of the fields do not match.

SELECT tblCalAudit.ClNum
FROM tblCalAudit INNER JOIN tblCalField ON (tblCalAudit.[LeakY/N] <>
tblCalField.[LeakY/N]) OR (tblCalAudit.LossLeak <> tblCalField.LossLeak) OR
(tblCalAudit.LAELeak <> tblCalField.LAELeak)
WITH OWNERACCESS OPTION;

 

[John Spencer]

How do you know that a record in tblCalAudit is supposed to match a record
in tblCalField? If you can't do that then I don't see a way to do what you
want.

After all if you have five records in table one and five records in table
two that are exact duplicates of the five in table one, then you are going
to end up with four records in table two that don't match each of the
records in table one.

TableOne - one field with values 1,2,3,4,5
TableTwo- one field with values 1,2,3,4,5

TableOne record with value 1 will not match TableTwo records with the values
2, 3, 4, or 5.

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

 

[Guest]

I am using the two different tables to test responses. Ideally, both sets of
responses ie both tables should be the same. If they are not exact matches,
then I need to know this so we can do some additional evaluation.

In field one, if the reponse is Yes in table one, but No in table two, then
I want the query to identify this, and list the ClNum.

Essentially what I am looking for is a way to have the unmatched query
analyze multiple fields.

How do you know that a record in tblCalAudit is supposed to match a record
in tblCalField? If you can't do that then I don't see a way to do what you
want.

After all if you have five records in table one and five records in table
two that are exact duplicates of the five in table one, then you are going
to end up with four records in table two that don't match each of the
records in table one.

TableOne - one field with values 1,2,3,4,5
TableTwo- one field with values 1,2,3,4,5

TableOne record with value 1 will not match TableTwo records with the values
2, 3, 4, or 5.

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

I have two identical tables. Information is input into each table by two
seperate sources. I would like to have a query that compares the fields
in
each table and list those records with any field that does not match. The
code below does not work, but I am trying to get something along these
lines
that does. I want it to compare 3 fields in two idential tables and list
the
CLNum if any of the fields do not match.

SELECT tblCalAudit.ClNum
FROM tblCalAudit INNER JOIN tblCalField ON (tblCalAudit.[LeakY/N] <>
tblCalField.[LeakY/N]) OR (tblCalAudit.LossLeak <> tblCalField.LossLeak)
OR
(tblCalAudit.LAELeak <> tblCalField.LAELeak)
WITH OWNERACCESS OPTION;

 

[Guest]

Rbb101,

Is the ClNum a common field between the two that must match?

How about:

Select Audit.ClNum
, tblCalAudit.[LeakY/N] as AuditLeakYN
, tblCalField.[LeakY/N] as FieldLeakYN
, tblCalAudit.[LossLeak] as AuditLossLeak
, tblCalField.[LossLeak] as FieldLossLeak
, tblCalAudit.[LAELeak] as AuditLEALeak
, tblCalField.[LAELeak] as FieldLEALeak
FROM tblCalAudit INNER JOIN tblCalField
ON tblCalAudit = tblCalField
WHERE tblCalAudit.[LeakY/N] <> tblCalField.[LeakY/N]
OR tblCalAudit.LossLeak <> tblCalField.LossLeak
OR tblCalAud.LEALeak <> tblCalField.LEALeak

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.

I am using the two different tables to test responses. Ideally, both sets of
responses ie both tables should be the same. If they are not exact matches,
then I need to know this so we can do some additional evaluation.

In field one, if the reponse is Yes in table one, but No in table two, then
I want the query to identify this, and list the ClNum.

Essentially what I am looking for is a way to have the unmatched query
analyze multiple fields.

How do you know that a record in tblCalAudit is supposed to match a record
in tblCalField? If you can't do that then I don't see a way to do what you
want.

After all if you have five records in table one and five records in table
two that are exact duplicates of the five in table one, then you are going
to end up with four records in table two that don't match each of the
records in table one.

TableOne - one field with values 1,2,3,4,5
TableTwo- one field with values 1,2,3,4,5

TableOne record with value 1 will not match TableTwo records with the values
2, 3, 4, or 5.

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

I have two identical tables. Information is input into each table by two
seperate sources. I would like to have a query that compares the fields
in
each table and list those records with any field that does not match. The
code below does not work, but I am trying to get something along these
lines
that does. I want it to compare 3 fields in two idential tables and list
the
CLNum if any of the fields do not match.

SELECT tblCalAudit.ClNum
FROM tblCalAudit INNER JOIN tblCalField ON (tblCalAudit.[LeakY/N] <>
tblCalField.[LeakY/N]) OR (tblCalAudit.LossLeak <> tblCalField.LossLeak)
OR
(tblCalAudit.LAELeak <> tblCalField.LAELeak)
WITH OWNERACCESS OPTION;

 

[John Spencer]

Don't forget to test for nulls

Easiest way to do that is to use a calculated field and the NZ function.
You don't need to do that if your field is a Yes/No field (Boolean
field).

WHERE NZ(tblCalAudit.[LossLeak],"") <> NZ(tblCalField.[LossLeak],"")

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

 

[Guest]

Yes, the ClNum field is a common field between the two tables and must match.

What you provided is what I was looking for. Thanks.

To complicate things, is there a way to incorporate acceptable limits,
rather than just identifying something that does not match exactly. For
instance, is there a way to change tblCalAudit.LossLeak
<>tblCalField.LossLeak to "tblCalAudit.LossLeak <90% or >100% of
tblCalAudit.LossLeak".

Brian

Rbb101,

Is the ClNum a common field between the two that must match?

How about:

Select Audit.ClNum
, tblCalAudit.[LeakY/N] as AuditLeakYN
, tblCalField.[LeakY/N] as FieldLeakYN
, tblCalAudit.[LossLeak] as AuditLossLeak
, tblCalField.[LossLeak] as FieldLossLeak
, tblCalAudit.[LAELeak] as AuditLEALeak
, tblCalField.[LAELeak] as FieldLEALeak
FROM tblCalAudit INNER JOIN tblCalField
ON tblCalAudit = tblCalField
WHERE tblCalAudit.[LeakY/N] <> tblCalField.[LeakY/N]
OR tblCalAudit.LossLeak <> tblCalField.LossLeak
OR tblCalAud.LEALeak <> tblCalField.LEALeak

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.

I am using the two different tables to test responses. Ideally, both sets of
responses ie both tables should be the same. If they are not exact matches,
then I need to know this so we can do some additional evaluation.

In field one, if the reponse is Yes in table one, but No in table two, then
I want the query to identify this, and list the ClNum.

Essentially what I am looking for is a way to have the unmatched query
analyze multiple fields.

How do you know that a record in tblCalAudit is supposed to match a record
in tblCalField? If you can't do that then I don't see a way to do what you
want.

After all if you have five records in table one and five records in table
two that are exact duplicates of the five in table one, then you are going
to end up with four records in table two that don't match each of the
records in table one.

TableOne - one field with values 1,2,3,4,5
TableTwo- one field with values 1,2,3,4,5

TableOne record with value 1 will not match TableTwo records with the values
2, 3, 4, or 5.

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

I have two identical tables. Information is input into each table by two
seperate sources. I would like to have a query that compares the fields
in
each table and list those records with any field that does not match. The
code below does not work, but I am trying to get something along these
lines
that does. I want it to compare 3 fields in two idential tables and list
the
CLNum if any of the fields do not match.

SELECT tblCalAudit.ClNum
FROM tblCalAudit INNER JOIN tblCalField ON (tblCalAudit.[LeakY/N] <>
tblCalField.[LeakY/N]) OR (tblCalAudit.LossLeak <> tblCalField.LossLeak)
OR
(tblCalAudit.LAELeak <> tblCalField.LAELeak)
WITH OWNERACCESS OPTION;

 

[John Spencer]

Change the where clause to something like the following

Where tblCallField.LossLeak Not Between tblCalAudit.LossLeak * .9 and
tblCallAudit.LossLeak * 1.1


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

Yes, the ClNum field is a common field between the two tables and must
match.

What you provided is what I was looking for. Thanks.

To complicate things, is there a way to incorporate acceptable limits,
rather than just identifying something that does not match exactly. For
instance, is there a way to change tblCalAudit.LossLeak
<>tblCalField.LossLeak to "tblCalAudit.LossLeak <90% or >100% of
tblCalAudit.LossLeak".

Brian

Rbb101,

Is the ClNum a common field between the two that must match?

How about:

Select Audit.ClNum
, tblCalAudit.[LeakY/N] as AuditLeakYN
, tblCalField.[LeakY/N] as FieldLeakYN
, tblCalAudit.[LossLeak] as AuditLossLeak
, tblCalField.[LossLeak] as FieldLossLeak
, tblCalAudit.[LAELeak] as AuditLEALeak
, tblCalField.[LAELeak] as FieldLEALeak
FROM tblCalAudit INNER JOIN tblCalField
ON tblCalAudit = tblCalField
WHERE tblCalAudit.[LeakY/N] <> tblCalField.[LeakY/N]
OR tblCalAudit.LossLeak <> tblCalField.LossLeak
OR tblCalAud.LEALeak <> tblCalField.LEALeak

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.

I am using the two different tables to test responses. Ideally, both
sets of
responses ie both tables should be the same. If they are not exact
matches,
then I need to know this so we can do some additional evaluation.

In field one, if the reponse is Yes in table one, but No in table two,
then
I want the query to identify this, and list the ClNum.

Essentially what I am looking for is a way to have the unmatched query
analyze multiple fields.

:

How do you know that a record in tblCalAudit is supposed to match a
record
in tblCalField? If you can't do that then I don't see a way to do
what you
want.

After all if you have five records in table one and five records in
table
two that are exact duplicates of the five in table one, then you are
going
to end up with four records in table two that don't match each of the
records in table one.

TableOne - one field with values 1,2,3,4,5
TableTwo- one field with values 1,2,3,4,5

TableOne record with value 1 will not match TableTwo records with the
values
2, 3, 4, or 5.

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

I have two identical tables. Information is input into each table
by two
seperate sources. I would like to have a query that compares the
fields
in
each table and list those records with any field that does not
match. The
code below does not work, but I am trying to get something along
these
lines
that does. I want it to compare 3 fields in two idential tables
and list
the
CLNum if any of the fields do not match.

SELECT tblCalAudit.ClNum
FROM tblCalAudit INNER JOIN tblCalField ON (tblCalAudit.[LeakY/N]
<>
tblCalField.[LeakY/N]) OR (tblCalAudit.LossLeak <>
tblCalField.LossLeak)
OR
(tblCalAudit.LAELeak <> tblCalField.LAELeak)
WITH OWNERACCESS OPTION;

 

[Guest]

Thanks. I appreciate the assistance.

Change the where clause to something like the following

Where tblCallField.LossLeak Not Between tblCalAudit.LossLeak * .9 and
tblCallAudit.LossLeak * 1.1


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

Yes, the ClNum field is a common field between the two tables and must
match.

What you provided is what I was looking for. Thanks.

To complicate things, is there a way to incorporate acceptable limits,
rather than just identifying something that does not match exactly. For
instance, is there a way to change tblCalAudit.LossLeak
<>tblCalField.LossLeak to "tblCalAudit.LossLeak <90% or >100% of
tblCalAudit.LossLeak".

Brian

Rbb101,

Is the ClNum a common field between the two that must match?

How about:

Select Audit.ClNum
, tblCalAudit.[LeakY/N] as AuditLeakYN
, tblCalField.[LeakY/N] as FieldLeakYN
, tblCalAudit.[LossLeak] as AuditLossLeak
, tblCalField.[LossLeak] as FieldLossLeak
, tblCalAudit.[LAELeak] as AuditLEALeak
, tblCalField.[LAELeak] as FieldLEALeak
FROM tblCalAudit INNER JOIN tblCalField
ON tblCalAudit = tblCalField
WHERE tblCalAudit.[LeakY/N] <> tblCalField.[LeakY/N]
OR tblCalAudit.LossLeak <> tblCalField.LossLeak
OR tblCalAud.LEALeak <> tblCalField.LEALeak

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.


:

I am using the two different tables to test responses. Ideally, both
sets of
responses ie both tables should be the same. If they are not exact
matches,
then I need to know this so we can do some additional evaluation.

In field one, if the reponse is Yes in table one, but No in table two,
then
I want the query to identify this, and list the ClNum.

Essentially what I am looking for is a way to have the unmatched query
analyze multiple fields.

:

How do you know that a record in tblCalAudit is supposed to match a
record
in tblCalField? If you can't do that then I don't see a way to do
what you
want.

After all if you have five records in table one and five records in
table
two that are exact duplicates of the five in table one, then you are
going
to end up with four records in table two that don't match each of the
records in table one.

TableOne - one field with values 1,2,3,4,5
TableTwo- one field with values 1,2,3,4,5

TableOne record with value 1 will not match TableTwo records with the
values
2, 3, 4, or 5.

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

I have two identical tables. Information is input into each table
by two
seperate sources. I would like to have a query that compares the
fields
in
each table and list those records with any field that does not
match. The
code below does not work, but I am trying to get something along
these
lines
that does. I want it to compare 3 fields in two idential tables
and list
the
CLNum if any of the fields do not match.

SELECT tblCalAudit.ClNum
FROM tblCalAudit INNER JOIN tblCalField ON (tblCalAudit.[LeakY/N]
<>
tblCalField.[LeakY/N]) OR (tblCalAudit.LossLeak <>
tblCalField.LossLeak)
OR
(tblCalAudit.LAELeak <> tblCalField.LAELeak)
WITH OWNERACCESS OPTION;