Incorrect Sum Totals on Single Field for Certain Records

[accessapprentice]

Hello All,



I am writing in regards to a question I cannot seem to solve.

I have joined two tables (the names of which have been changed for privacy reasons), well call "Sales" and "Purchases". It is a Many-to-One join on the field 'SKU' with a Left Join of "Sales" table to "Purchases" table.
***FYI 'GROUP' and 'GROUP DESC' fields have also been changed from real field names for privacy reasons.

The problem I am having is that the field "Sales Units" is summing incorrect, overinflated totals for certain 'SKUs'. This isn't the case for all fields, but this one in particular.





I am not sure if it has anything to do with the datatypes of the fields. Datatypes are as follows:

SALES TABLE
SKU = Number, Long Integer (GROUP BY)
SKU DESC= Text (GROUP BY)
"GROUP"= Number, Long Integer (GROUP BY)
CHANNEL= Text (GROUP BY) Criteria: "Stocked"
SALES UNITS = Number, Long IntegerRETAIL PRICE = Number, Double (SUM)
RETAIL PRICE = Number, Double (AVG)

PURCHASES TABLE
"GROUP DESC"= Text, @ (GROUP BY)
COST(EA) = Currency, $* #,##0.00;$* (#,##0.00);$* -00 (AVG)





Any thoughts?

Thank you for your help

[pbaldy]

Is it possible those SKU's are duplicated in the "one" table?

[accessapprentice]

Yes, this actually seems to be the issue.

For example, we have a SKU #12965.
In the query I performed, the total 'SumOfSALES UNITS' was = 311,682
When I filtered and pulled all the records from the "Sales" table and did a sum in excel, I got a sum total of 14,842 for field 'SALES UNITS'.

When I searched the 'Purchases Table' I got 21 records. 14842 x 21 = 311682.

I am still trying to figure out how to remedy this, but now I understand. Thank you!

[pbaldy]

What is the purpose of the purchases table? If it records purchases, that would imply that there have been 21 purchases. I guess the question is why that table is included in your query if you don't expect a record for each. You called it a "one" table, but apparently it's not. Generally the "one" table would be a products table that had one record for each SKU.