Comparing data in two tables

I am trying to print out a report that compares two tables. The tables have
the same headings. One table is from my department and the other table is
from another department. I need to make sure that we balance (which we NEVER
do). So I need to be able to show which records are not balancing.

Simple enough. Now my dilema.
The information could have duplicate records in either table, thus
duplicating the amount.

SAMPLE:
Table 1:
File# Veh $ Misc $
07-145678 $3,639

Table 2:
File# Veh $ Misc $
07-145678 $3,639 $300
07-145678 $500


REPORT shows:
File# Veh $ Misc $ Veh $ Misc $
07-145678 $3,639 $3,639 $300
07-145678 $3,639 $500

I even tried doing it through a querry and I get the same thing. If the file
has two or more records associated with it then I get it duplicated on the
report. I even tried to do a sum of the records with the same file #......
that did not work.

HELP

I used four queries. If you know subqueries you could do it with less.
ivysgarden_0 ---
SELECT [Table 1].[File#]
FROM [Table 1]
GROUP BY [Table 1].[File#]
UNION ALL SELECT [Table 2].[File#]
FROM [Table 2]
GROUP BY [Table 2].[File#];

ivysgarden_1 ---
SELECT [Table 1].[File#], Sum([Table 1].[Veh $]) AS [SumOfVeh $], Sum([Table
1].[Misc $]) AS [SumOfMisc $]
FROM [Table 1]
GROUP BY [Table 1].[File#];

ivysgarden_2 ---
SELECT [Table 2].[File#], Sum([Table 2].[Veh $]) AS [SumOfVeh $], Sum([Table
2].[Misc $]) AS [SumOfMisc $]
FROM [Table 2]
GROUP BY [Table 2].[File#];

SELECT ivysgarden_0.[File#], ivysgarden_1.[SumOfVeh $],
ivysgarden_1.[SumOfMisc $], ivysgarden_2.[SumOfVeh $],
ivysgarden_2.[SumOfMisc $]
FROM (ivysgarden_0 LEFT JOIN ivysgarden_1 ON ivysgarden_0.[File#] =
ivysgarden_1.[File#]) LEFT JOIN ivysgarden_2 ON ivysgarden_0.[File#] =
ivysgarden_2.[File#]
GROUP BY ivysgarden_0.[File#], ivysgarden_1.[SumOfVeh $],
ivysgarden_1.[SumOfMisc $], ivysgarden_2.[SumOfVeh $],
ivysgarden_2.[SumOfMisc $]
HAVING (((ivysgarden_2.[SumOfVeh $])=[ivysgarden_1].[SumOfVeh $])) OR
(((ivysgarden_2.[SumOfMisc $])=[ivysgarden_1].[SumOfMisc $]));

--
KARL DEWEY
Build a little - Test a little


"ivysgarden" wrote:

I am trying to print out a report that compares two tables. The tables have
the same headings. One table is from my department and the other table is
from another department. I need to make sure that we balance (which we NEVER
do). So I need to be able to show which records are not balancing.

Simple enough. Now my dilema.
The information could have duplicate records in either table, thus
duplicating the amount.

SAMPLE:
Table 1:
File# Veh $ Misc $
07-145678 $3,639

Table 2:
File# Veh $ Misc $
07-145678 $3,639 $300
07-145678 $500


REPORT shows:
File# Veh $ Misc $ Veh $ Misc $
07-145678 $3,639 $3,639 $300
07-145678 $3,639 $500

I even tried doing it through a querry and I get the same thing. If the file
has two or more records associated with it then I get it duplicated on the
report. I even tried to do a sum of the records with the same file #......
that did not work.

HELP