Query returning INCORRECT SUM when grouped

[jmac123]

I Have done a bit of database administration in other environments but am fairly new to access. I have a peculiar problem that I have been trying to solve for almost 10 hours now :banghead:

Any help on this would be really appreciated. I have searched existing threads to no avail.

When I run query 1 (Below) it returns one row with value 9,147,353. This is correct.

When I add a 'group by' (shown in query 2) it returns one row with value 9,143,612. INCORRECT!

I have tried grouping by different fields. All statements with a 'Group By' return a number of rows with 'cases' field always totaling 9,143,612. There are certainly 9,147,353 total cases in the base table.
Why should the total cases change when grouped??

QUERY 1
SELECT SUM(S2.Cases) as Cases
FROM
(
SELECT
S.ViewDescription,
S.CustomersID,
S.ProductsID,
S.Year,
S.Week,
S.MonthA as Month,
S.Cases * S.MonthASplit as Cases
FROM SCPViews S WHERE S.Current = TRUE
UNION ALL
SELECT
S.ViewDescription,
S.CustomersID,
S.ProductsID,
S.Year,
S.Week,
S.MonthB as Month,
S.Cases * S.MonthBSplit as Cases
FROM SCPViews S WHERE S.Current = TRUE
) S2


Query 2
SELECT SUM(S2.Cases) as Cases
FROM
(
SELECT
S.ViewDescription,
S.CustomersID,
S.ProductsID,
S.Year,
S.Week,
S.MonthA as Month,
S.Cases * S.MonthASplit as Cases
FROM SCPViews S WHERE S.Current = TRUE
UNION ALL
SELECT
S.ViewDescription,
S.CustomersID,
S.ProductsID,
S.Year,
S.Week,
S.MonthB as Month,
S.Cases * S.MonthBSplit as Cases
FROM SCPViews S WHERE S.Current = TRUE
) S2
Group By S2.Year

Using MS Access 2010 btw

 

[Guus2005]

Are you sure that the Year column is always filled, No Null values?
Are you sure that Cases and MonthBSplit are allways filled, No Null values?

You might want to use the Nz function to convert the possible Null fields into 0.

What is also detect is a normalisation issue. MonthA and MonthB in the same table points in that direction.

HTH

 

[jmac123]

Thanks for your reply

I can confirm there are no NULLs in Year column.

The returned dataset is perfect until a group by is added

 

[Guus2005]

Looking closer at you query it is a bit strange that
S.Cases * S.MonthASplit as Cases
and in the other query
S.Cases * S.MonthBSplit as Cases
The result of both you sum in the end.

You get the same result when:
S.Cases * S.MonthASplit + S.Cases * S.MonthBSplit as Cases
or
S.Cases * (S.MonthASplit + S.MonthBSplit) as Cases

The same S.Cases is used in both multiplications.
So why do you need the rest?

Having said that, you can break up this problem into smaller parts.
First create a query which unions both queries and view the result if the are any anomalies.

SELECT
S.ViewDescription,
S.CustomersID,
S.ProductsID,
S.Year,
S.Week,
S.MonthA as Month,
S.Cases * S.MonthASplit as Cases
FROM SCPViews S WHERE S.Current = TRUE
UNION ALL
SELECT
S.ViewDescription,
S.CustomersID,
S.ProductsID,
S.Year,
S.Week,
S.MonthB as Month,
S.Cases * S.MonthBSplit as Cases
FROM SCPViews S WHERE S.Current = TRUE

Then user this query to produce the end result:

SELECT SUM(Query1.Cases) as Cases, Query1.Year
FROM Query1
Group By Query1.Year

HTH

 

[jmac123]

To explain,

The data file I am working from has Forecast units by Week.
In order to get data by month I have a few calculated columns:
Month A - Calculates the month number of day 1 for this particular week
Month B - Calculates the month number of day 7 for this particular week
MonthASplit - Calculates the % of the week that falls into Month A
MonthBSplit - Calculates the % of the week that falls into Month B


The union works great and I have already tried making this into a query as suggested.

SELECT
S.ViewDescription,
S.CustomersID,
S.ProductsID,
S.Year,
S.Week,
S.MonthA as Month,
S.Cases * S.MonthASplit as Cases
FROM SCPViews S WHERE S.Current = TRUE
UNION ALL
SELECT
S.ViewDescription,
S.CustomersID,
S.ProductsID,
S.Year,
S.Week,
S.MonthB as Month,
S.Cases * S.MonthBSplit as Cases
FROM SCPViews S WHERE S.Current = TRUE

The query work fine but as soon as I try and perform any 'Group By' (like suggested)

SELECT SUM(Query1.Cases) as Cases, Query1.Year
FROM Query1
Group By Query1.Year

it returns the faulty case totals again.

I did another test. Instead of making a query, I made a new table from the union shown above. When performing the 'Group By' suggested above with the table it works fine with the desired grouping and correct totals. Running the identical SELECT on the query returns incorrect values!

 

[Guus2005]

Can you post a sample database?

 

[jmac123]

Hi whilst making the sample database for you I discovered the solution!!

The SCPViews.Cases field had a data type of long integer which is normally ok. However, the subselect output should have cases with several decimal places and it is apparent that it was forcing 'cases' to the nearest integer before grouping.

I was presuming that the temporary table from the subselect wouldn't care about the source number type.

Important lesson learnt

Thanks for you help Guss2005

 

[Guus2005]

glad you figured it out and that i was part of the solution.

grtz, Guus.