Compare 2 totals queries

[blueboy1894]

Hi

I have a database with several total queries which eventually need to be
compared, and the differences between the summed Quantities displayed on a
report. The structure of all the data tables (tblprep1, tblprep2 and so on)
is the same and the SQL for each total query looks, for example like this:

SELECT tblprep1.Field4 AS Product, Sum(tblprep1.Field6) AS Quantity,
tblprep1.Field10 AS Prod_Grp, First(tblprep1.Field5) AS Description
FROM tblprep1
GROUP BY tblprep1.Field4, tblprep1.Field10
HAVING (((tblprep1.Field10)="PREPSALADS" Or (tblprep1.Field10)="PREPFRUIT"
Or (tblprep1.Field10)="PREPVEG" Or (tblprep1.Field10)="JUICES"));

How do I go about writing a subquery which allows me to determine the
difference between, for example, the summed quantity in this query, and the
summed quantity in a query for tblprep2?

Any help much appreciated.

 

[Dale Fye]

BlueBoy,

It appears to me that your data is not normallized very well. If you have
multiple tables with the same table structure this is a pretty good
indication that your structure is flawed. This causes a number of
difficulties, of which your example is just one. You can accomplish the same
thing by having a single table, and adding an additional field to identify
what would currently determine which table the data is stored in.

Enough of my soap-box; on to your question:

When you say you want to compare the tables, I take that to mean you want to
identify each product/quantity that appears in each query and compare their
quantities. This usually includes identifying those records that are in one
of the queries (or tables) and not in the other. I would start out by
creating a union query that would look something like (save this as Query3):

SELECT "tblPrep1" as Source, Prod_Grp, Product, Description, Quantity
FROM query1
UNION ALL
SELECT "tblPrep2" as Source, Prod_Grp, Product, Description, Quantity
FROM query2

Then, you can use Query3 as the source for a Crosstab query that includes
the Prod_Group, Product, Description as Row headings, and Source as the
Column Heading, and Quantity as the value (set this to Sum). Once you run
this query, you can easily identify the differences by observation. If you
want to do this in a query, save the crosstab query as Quer4 and write a new
query that identifies the differences:

SELECT Prod_Group, Product, Description, tblPrep1, tblPrep2
FROM Query4
Where (isnull(tblPrep1) = False AND isnull(tblPrep2) = True)
OR (isnull(tblPrep1) = True AND isnull(tblPrep2) = False)
OR (tblPrep1 <> tblPrep2)

You need the first two parts of this where clause to identify where a record
is
missing from one of the queries. The last part will identify where the
values in your two basic queries are not the same, but will not return those
records where one of the values is NULL.

HTH
Dale

 

[blueboy1894]

Hi Dale, thanks for answering

Just to answer the first point - which is a good one - the reason behind
this is the data actually comes from a different system and is being imported
into the access database.

The 3 tables represent snapshots of the data at different times of the day.
It is a bunch of order lines for different products from different customers
(1 row = 1 order from 1 customer for a certain quantity of 1 product). Whilst
the difference in quantities can be done manually in Excel, by using a bunch
of pivot tables, the user wants the process automated in Access.

As a result, the data can't be appended toegther in one table, which is a
shame. We need basically to understand what the difference in totals for each
product (hence the total queries that sum up the quantities from the raw
order lines). I then need to determine which products have total quantities
which differ from the snapshot that preceded it and determine the difference.

Hope that makes it clearer.

That said, I'll have a look at using the solution you provided, and post the
results here.

 

[blueboy1894]

Spot on this Dale, many many thanks...

BlueBoy,

It appears to me that your data is not normallized very well. If you have
multiple tables with the same table structure this is a pretty good
indication that your structure is flawed. This causes a number of
difficulties, of which your example is just one. You can accomplish the same
thing by having a single table, and adding an additional field to identify
what would currently determine which table the data is stored in.

Enough of my soap-box; on to your question:

When you say you want to compare the tables, I take that to mean you want to
identify each product/quantity that appears in each query and compare their
quantities. This usually includes identifying those records that are in one
of the queries (or tables) and not in the other. I would start out by
creating a union query that would look something like (save this as Query3):

SELECT "tblPrep1" as Source, Prod_Grp, Product, Description, Quantity
FROM query1
UNION ALL
SELECT "tblPrep2" as Source, Prod_Grp, Product, Description, Quantity
FROM query2

Then, you can use Query3 as the source for a Crosstab query that includes
the Prod_Group, Product, Description as Row headings, and Source as the
Column Heading, and Quantity as the value (set this to Sum). Once you run
this query, you can easily identify the differences by observation. If you
want to do this in a query, save the crosstab query as Quer4 and write a new
query that identifies the differences:

SELECT Prod_Group, Product, Description, tblPrep1, tblPrep2
FROM Query4
Where (isnull(tblPrep1) = False AND isnull(tblPrep2) = True)
OR (isnull(tblPrep1) = True AND isnull(tblPrep2) = False)
OR (tblPrep1 <> tblPrep2)

You need the first two parts of this where clause to identify where a record
is
missing from one of the queries. The last part will identify where the
values in your two basic queries are not the same, but will not return those
records where one of the values is NULL.

HTH
Dale