If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Compare mulitple fields in two tables
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; |
#2
|
|||
|
|||
Compare mulitple fields in two tables
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 .. "rbb101" wrote in message ... 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; |
#3
|
|||
|
|||
Compare mulitple fields in two tables
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. "John Spencer" wrote: 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 .. "rbb101" wrote in message ... 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; |
#4
|
|||
|
|||
Compare mulitple fields in two tables
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. "rbb101" wrote: 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. "John Spencer" wrote: 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 .. "rbb101" wrote in message ... 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; |
#5
|
|||
|
|||
Compare mulitple fields in two tables
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 '================================================= === Dale Fye wrote: 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 |
#6
|
|||
|
|||
Compare mulitple fields in two tables
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 "Dale Fye" wrote: 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. "rbb101" wrote: 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. "John Spencer" wrote: 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 .. "rbb101" wrote in message ... 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; |
#7
|
|||
|
|||
Compare mulitple fields in two tables
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 .. "rbb101" wrote in message ... 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 "Dale Fye" wrote: 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. "rbb101" wrote: 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. "John Spencer" wrote: 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 .. "rbb101" wrote in message ... 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; |
#8
|
|||
|
|||
Compare mulitple fields in two tables
Thanks. I appreciate the assistance.
"John Spencer" wrote: 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 .. "rbb101" wrote in message ... 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 "Dale Fye" wrote: 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. "rbb101" wrote: 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. "John Spencer" wrote: 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 .. "rbb101" wrote in message ... 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; |
Thread Tools | |
Display Modes | |
|
|