Comparing two tables

[jclaibor]

Hi
I need to compare two tables that have some overlapping data (from
different sources). Example
Tbl 1 Tbl 2
Date, Color, Count Date, Color, Count, Shade

I'm comparing the color feilds and calculating the difference in Date and
Count returned by the two different sources. The problem is some records in
Tbl 1 won't be in Tbl 2 and vice versa while some records will be in both.
How do I write a query to get a single data set that lists the compared
results without picking up duplicates or omitting data not in both tables.
I've tried Unions but must be missing something. Thanks for any help.

 

[Douglas J. Steele]

You'll need to use LEFT JOIN between tbl1 and tbl2 and UNION that with a
LEFT JOIN between tbl2 and tbl2:

SELECT T1.Color, T1.[Date], T1.Count, T2.[Date], T2.Count, T2.Shade
FROM [Tbl 1] AS T1 LEFT JOIN [Tbl 2] AS T2
ON T1.Color = T2.Color
UNION
SELECT T2.Color, T1.[Date], T1.Count, T2.[Date], T2.Count, T2.Shade
FROM [Tbl 2] AS T2 LEFT JOIN [Tbl 1] AS T1
ON T1.Color = T2.Color

Note that you really should rename the field from Date, since Date is a
reserved word. For a comprehensive list of names to avoid (as well as a link
to a free utility to check your application for compliance), check what
Allen Browne has at http://www.allenbrowne.com/AppIssueBadWord.html

 

[jclaibor]

Thanks Doug. I translated the mock fields to my table fields but it was a no
go. The problem is most likely in the question and not the answer. I'll try
again without the dummy names.

Table1 has fields for Date, Bus, Cash
Table2 has fields for Date, Bus, Cash, and Badge (1-4)

The data comes from different sources and each field name reflects its
source i.e. table1_date and table2_date. I am comparing the cash collected
for each bus by date with the respective badge numbers if they are available.
Each source will have some data that matches the other source and some that
is not present in the other source. I know that some buses will only be
represented in Table1 and some only represented in Table2. So I need data
unique to Table1, data reflected in both sources, and data unique to table2.
The queries will be date driven.

Thanks

You'll need to use LEFT JOIN between tbl1 and tbl2 and UNION that with a
LEFT JOIN between tbl2 and tbl2:

SELECT T1.Color, T1.[Date], T1.Count, T2.[Date], T2.Count, T2.Shade
FROM [Tbl 1] AS T1 LEFT JOIN [Tbl 2] AS T2
ON T1.Color = T2.Color
UNION
SELECT T2.Color, T1.[Date], T1.Count, T2.[Date], T2.Count, T2.Shade
FROM [Tbl 2] AS T2 LEFT JOIN [Tbl 1] AS T1
ON T1.Color = T2.Color

Note that you really should rename the field from Date, since Date is a
reserved word. For a comprehensive list of names to avoid (as well as a link
to a free utility to check your application for compliance), check what
Allen Browne has at http://www.allenbrowne.com/AppIssueBadWord.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi
I need to compare two tables that have some overlapping data (from
different sources). Example
Tbl 1 Tbl 2
Date, Color, Count Date, Color, Count, Shade

I'm comparing the color feilds and calculating the difference in Date and
Count returned by the two different sources. The problem is some records
in
Tbl 1 won't be in Tbl 2 and vice versa while some records will be in both.
How do I write a query to get a single data set that lists the compared
results without picking up duplicates or omitting data not in both tables.
I've tried Unions but must be missing something. Thanks for any help.

.

 

[Douglas J. Steele]

Show me the SQL that failed.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Doug. I translated the mock fields to my table fields but it was a
no
go. The problem is most likely in the question and not the answer. I'll
try
again without the dummy names.

Table1 has fields for Date, Bus, Cash
Table2 has fields for Date, Bus, Cash, and Badge (1-4)

The data comes from different sources and each field name reflects its
source i.e. table1_date and table2_date. I am comparing the cash
collected
for each bus by date with the respective badge numbers if they are
available.
Each source will have some data that matches the other source and some
that
is not present in the other source. I know that some buses will only be
represented in Table1 and some only represented in Table2. So I need data
unique to Table1, data reflected in both sources, and data unique to
table2.
The queries will be date driven.

Thanks

You'll need to use LEFT JOIN between tbl1 and tbl2 and UNION that with a
LEFT JOIN between tbl2 and tbl2:

SELECT T1.Color, T1.[Date], T1.Count, T2.[Date], T2.Count, T2.Shade
FROM [Tbl 1] AS T1 LEFT JOIN [Tbl 2] AS T2
ON T1.Color = T2.Color
UNION
SELECT T2.Color, T1.[Date], T1.Count, T2.[Date], T2.Count, T2.Shade
FROM [Tbl 2] AS T2 LEFT JOIN [Tbl 1] AS T1
ON T1.Color = T2.Color

Note that you really should rename the field from Date, since Date is a
reserved word. For a comprehensive list of names to avoid (as well as a
link
to a free utility to check your application for compliance), check what
Allen Browne has at http://www.allenbrowne.com/AppIssueBadWord.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi
I need to compare two tables that have some overlapping data (from
different sources). Example
Tbl 1 Tbl 2
Date, Color, Count Date, Color, Count, Shade

I'm comparing the color feilds and calculating the difference in Date
and
Count returned by the two different sources. The problem is some
records
in
Tbl 1 won't be in Tbl 2 and vice versa while some records will be in
both.
How do I write a query to get a single data set that lists the compared
results without picking up duplicates or omitting data not in both
tables.
I've tried Unions but must be missing something. Thanks for any help.

.

 

[jclaibor]

Hi Doug

I was able to get what I needed by using 3 queries in a Union query.
Records that only existed in Table1, records that only existed in Table 2,
and records that were present in both tables. The query works great now.
Thanks again for your help.

Show me the SQL that failed.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Doug. I translated the mock fields to my table fields but it was a
no
go. The problem is most likely in the question and not the answer. I'll
try
again without the dummy names.

Table1 has fields for Date, Bus, Cash
Table2 has fields for Date, Bus, Cash, and Badge (1-4)

The data comes from different sources and each field name reflects its
source i.e. table1_date and table2_date. I am comparing the cash
collected
for each bus by date with the respective badge numbers if they are
available.
Each source will have some data that matches the other source and some
that
is not present in the other source. I know that some buses will only be
represented in Table1 and some only represented in Table2. So I need data
unique to Table1, data reflected in both sources, and data unique to
table2.
The queries will be date driven.

Thanks

You'll need to use LEFT JOIN between tbl1 and tbl2 and UNION that with a
LEFT JOIN between tbl2 and tbl2:

SELECT T1.Color, T1.[Date], T1.Count, T2.[Date], T2.Count, T2.Shade
FROM [Tbl 1] AS T1 LEFT JOIN [Tbl 2] AS T2
ON T1.Color = T2.Color
UNION
SELECT T2.Color, T1.[Date], T1.Count, T2.[Date], T2.Count, T2.Shade
FROM [Tbl 2] AS T2 LEFT JOIN [Tbl 1] AS T1
ON T1.Color = T2.Color

Note that you really should rename the field from Date, since Date is a
reserved word. For a comprehensive list of names to avoid (as well as a
link
to a free utility to check your application for compliance), check what
Allen Browne has at http://www.allenbrowne.com/AppIssueBadWord.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi
I need to compare two tables that have some overlapping data (from
different sources). Example
Tbl 1 Tbl 2
Date, Color, Count Date, Color, Count, Shade

I'm comparing the color feilds and calculating the difference in Date
and
Count returned by the two different sources. The problem is some
records
in
Tbl 1 won't be in Tbl 2 and vice versa while some records will be in
both.
How do I write a query to get a single data set that lists the compared
results without picking up duplicates or omitting data not in both
tables.
I've tried Unions but must be missing something. Thanks for any help.


.

.

 

[Itamar]

Hi
   I need tocomparetwotablesthat have some overlapping data (fromdifferentsources).  Example
Tbl 1                                    Tbl 2
Date, Color, Count             Date, Color, Count, Shade

I'm comparing the color feilds and calculating the difference in Date and
Count returned by the twodifferentsources.  The problem is some recordsin
Tbl 1 won't be in Tbl 2 and vice versa while some records will be in both..  
How do I write a query to get a single data set that lists the compared
results without picking up duplicates or omitting data not in bothtables. 
I've tried Unions but must be missing something.  Thanks for any help.

Hi
if you're willing to use an external tool to do this, with a nice GUI,
Columbo, by Nob Hill can do it, and its free.( http://www.nobhillsoft.com/Columbo.aspx)

 

[Douglas J. Steele]

How do you figure that an application that says it's $49.95 is free?

Oh, wait: you work for the company!

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi
I need tocomparetwotablesthat have some overlapping data
(fromdifferentsources). Example
Tbl 1 Tbl 2
Date, Color, Count Date, Color, Count, Shade

I'm comparing the color feilds and calculating the difference in Date and
Count returned by the twodifferentsources. The problem is some records in
Tbl 1 won't be in Tbl 2 and vice versa while some records will be in both.
How do I write a query to get a single data set that lists the compared
results without picking up duplicates or omitting data not in bothtables.
I've tried Unions but must be missing something. Thanks for any help.

Hi
if you're willing to use an external tool to do this, with a nice GUI,
Columbo, by Nob Hill can do it, and its free.(
http://www.nobhillsoft.com/Columbo.aspx)