CODEWITHSUNDEEP
ms-accesssumdistinct
like2think
like2think

Reputation: 162

MS Access Totals Query Sum Field Incorrect Result

I've built what I believe to be a simple query in MS Access.

Two Tables are involved:

The History table includes multiple rows of data for each property, and for various dates.
I'm trying to show the sum of net_value for a specific date for properties that share a common area in the property table.

Here's my query:

SELECT      Properties.Area
            , History.HIST_DATE
            , History.ID
            , Sum(History.NET_VALUE) AS SumOfNET_VALUE
FROM        Properties INNER JOIN History ON Properties.ID = History.ID
WHERE       (((History.Account_ID)=45))
GROUP BY    Properties.Area, History.HIST_DATE, History.ID
HAVING      (((Properties.Area)="MY AREA") AND 
             ((History.HIST_DATE)=#2/1/2017#));

The problem is, the sum field is wildly incorrect.

Debugging

The root cause of the issue is that there are multiple entries of Properties.ID. So I suppose the select is not distinct? Is there a way around this?
The Properties.ID is effectively an account identifier and multiple properties can be associated with it; so I can't really limit Properties.ID to one record per ID... thoughts?

Upvotes: 1

Views: 1559

Answers (2)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19722

It looks like History.ID is a unique field, or at least unique to each property.
By including it in your query the sum will group on that ID as well as the area, so you'll end up with a total per property per area.

No idea where Account_ID comes into it, but have included it in the WHERE clause anyway.

SELECT        Properties.Area
            , History.Hist_Date
            , SUM(History.Net_Value) AS Total_Net_Value
FROM        Properties LEFT JOIN History ON Properties.ID = History.ID
WHERE       History.Account_ID = 45 AND 
            Properties.Area = "My Area"
            History.Hist_Date=#04/27/2018#
GROUP BY      Properties.Area 
            , History.Hist_Date

Upvotes: 1

asdev
asdev

Reputation: 943

Try this one:

SELECT      Properties.Area
            , History.HIST_DATE
            , History.ID
            , Sum(History.NET_VALUE) AS SumOfNET_VALUE
FROM        Properties INNER JOIN History ON Properties.ID = History.ID
WHERE       ((History.Account_ID)=45) 
              AND (Properties.Area)="MY AREA" 
              AND (History.HIST_DATE)=#2/1/2017#))
GROUP BY    Properties.Area, History.HIST_DATE, History.ID;

Do not use HAVING clause as Filter except for aggregate function, see here. Use WHERE Part instead.

HAVING would be reasonable for ...HAVING SumOfNET_VALUE > 200, for example.

Upvotes: 0

Related Questions