CODEWITHSUNDEEP
ms-access
Michael
Michael

Reputation: 3219

SUM in MS Access report is using all rows despite grouping

I have an MS Access report that has these columns:

Here is the query:

SELECT TOP 13 i.Code, i.Weight * i.Average_CWT / 100 as Amount, il.Location
FROM Inventory as i
LEFT JOIN Item_Location il ON i.Code = il.Item_Code
ORDER BY i.Code

The report is grouped by the Inventory Code, because there is a 1 to many join to get the locations (1 item can have many locations), so I just want to show the inventory item, the amount, and then under that group all of the locations, which I have here:

enter image description here

This grouping is working well, as you can see the last record shows 2 locations for 1 inventory code.

The issue arises when I try to add a SUM of the Amounts to the report header. The sum at the top of the image is actually slightly off. It should be 3,795.8175, but is showing as 3854.2405.

This is because the row at the bottom has 2 locations. So the SUM does not add up the amounts shown on the report; instead it calculates the sum based off the query, which returns 2 rows for that inventory item with 2 locations, instead of the 1 shown in the report. That's why the SUM is adding both the records values, each worth 58.423.

How can I get around this? Yes the join will return 2 rows since the location is different, but the amount should not be doubled. I've tried calculating the SUM as a subquery, and adding the field to the top of my report but got this error:

Multi-level group by not allowed

I already ran through this and it won't fix my problem: Multi-level GROUP BY clause not allowed in subquery.

Any ideas on how to get the true sum of records when using grouping in an Access report?

Upvotes: 0

Views: 469

Answers (1)

June7
June7

Reputation: 21370

Possible options:

  1. Instead of grouping and summing in report, join an aggregate query to detail data

  2. A main report with Inventory data as RecordSource and a subreport for related location details

  3. DSum() domain aggregate function, calculations can occur within domain aggregate:
    DSum("Weight * Average_CWT / 100", "tableORqueryName")

Upvotes: 1

Related Questions