Solved DSum Query does not start at 0 (1 Viewer)

[foshizzle]

For troubleshooting purposes, I have made two queries looking at the same table.
One query 'RunGalIss' accurately uses DSum to total gallons issued for a particular group within a specified timeframe.
However 'RunGalRcv' is setup the exact same way but starts with a negative value instead of 0.
Both data types to evaluate are integers. A sample database is attached. Additionally, here is the code from each query:

RunGalIss
SELECT tblAirlineTransactions.TransactionDate, tblAirlineTransactions.TransactionID, tblAirlineTransactions.AirlineCompanyLookup, tblAirlineTransactions.GallonsIssued, (DSum("GallonsIssued","tblAirlineTransactions","AirlineCompanyLookup = " & [AirlineCompanyLookup] & " And TransactionID <=" & [TransactionID])) AS RunIss
FROM tblAirlineTransactions
WHERE (((tblAirlineTransactions.TransactionDate) Between #12/1/2020# And #12/31/2020#) AND ((tblAirlineTransactions.AirlineCompanyLookup)=5));

RunGalRcv
SELECT tblAirlineTransactions.TransactionDate, tblAirlineTransactions.TransactionID, tblAirlineTransactions.AirlineCompanyLookup, tblAirlineTransactions.GallonsReceived, (DSum("GallonsReceived","tblAirlineTransactions","AirlineCompanyLookup = " & [AirlineCompanyLookup] & " And TransactionID <=" & [TransactionID])) AS RunRcv
FROM tblAirlineTransactions
WHERE (((tblAirlineTransactions.TransactionDate) Between #12/1/2020# And #12/31/2020#) AND ((tblAirlineTransactions.AirlineCompanyLookup)=5));

Thank you

 

[Minty]

Have you checked your data for any negative entries?

 

[foshizzle]

Good catch. the day before the query criteria on 11/30/20, there was a negative entry of the same amount I'm off. This is because the value was used as a starting balance; we are using this database to transition from an old one which had alot of hard-coded values.

Still, shouldn't the DSum query be only counting data specified in the given timeframe? i.e., For this instance, I am looking strictly for data between 12/01/20 and 12/31/20. If not, how do I make it start at 0?

 

[plog]

Still, shouldn't the DSum query be only counting data specified in the given timeframe?

Think of the DSUM as its own query. It knows nothing of any paramaters of the query it exists in.

RunRcv: (DSum("GallonsReceived","tblAirlineTransactions","AirlineCompanyLookup = " & [AirlineCompanyLookup] & " And TransactionID <=" & [TransactionID]))

If you want its criteria to match the criteria of the query you must put the query criteria in the DSUM as well. Additionally, I wouldn't use TransactionID in the DSUM but a date criteria. There's no guarantee your data entry is going to be chronologically done--someone is going to miss an entry one day and then insert it a few days later and its TransactionID will be out of place but its date value will be correct.

Additionally, you shouldn't store GallonsIssued and GallonsReceived in seperate fields. Putting them in one field allows you to just SUM the field to find the balance of them. Negative number=Issued, Positive Number = Received.

 

[foshizzle]

Thank you for that information. I've now tried including the date criteria inside the DSum. I'm getting a different number but still, an unexpected result.

The correct DSum result for gallons received should be 135154. I am getting 480213 which doesn't match anything. Here is my updated DSum expression. The dates are hardcoded for testing only.


(DSum("GallonsReceived","tblAirlineTransactions","AirlineCompanyLookup = " & [AirlineCompanyLookup] & " And TransactionID <=" & [TransactionID] AND [TransactionDate] between #12/01/20# and #12/31/20#))

 

[foshizzle]

Wait up. Think i may have figured it out; its adding all airlines but even if i specify the airline in the criteria its the same. i need to now get the results by each airline name

 

[plog]

You really shouldn't be using a DSUM either. Instead the most efficient way to get the running total is with a correlated subquery:

Correlated subquery - Wikipedia

en.wikipedia.org

This is what you should use for the RunRcv total:

RunRcv: (SELECT SUM([GallonsReceived]) FROM tblAirlineTransactions subQ WHERE subQ.AirlineCompanyLookup=tblAirlineTransactions.AirlineCompanyLookup AND subQ.TransactionDate>=#12/1/2020# AND subQ.TransactionDate<=tblAirlineTransactions.TransactionDate)

Again though, GallonsIssued and GallonsReceived should not be seperate fields. All the gallons should just be in 1 field.

 

[foshizzle]

Thank you; this makes sense and worked perfectly.
I believe there is a reason for keeping the fuel received and issued separately but will follow up w that dept to verify.

Thanks for everything!