Sum column for last date of transaction within specified period (1 Viewer)

foshizzle

Registered User.
Local time
Today, 06:55
Joined
Nov 27, 2013
Messages
277
I have an existing query of transactions where fuel has been received by any particular airline for any given date.
An airline can receive multiple fuel transactions per day.
I need to find the SUM of fuel received for each airline; only for the very last day they received it, within the given time period.

I can pull the last transaction date for each airline using the Last function. (see table below for example; though I need to make sure this only happens for values in the received column.)
The problem I'm having is trying to get the correct GallonsReceived for each airline (group) on only the last date they received fuel for that particular timeframe.

Example in the attached photos:
For testing, I'm running the query for dates between 12/01/20 through 12/31/20.
On 12/31/20, there happens to be only one received transaction per airline.
The other photo shows the last date of each airline had a transaction. (though I need to make sure this only happens for values in the received column).

Example db is attached. Thanks for your help!

example1.jpg
lastReceived.jpg
 

Attachments

  • Database1.accdb
    1 MB · Views: 262

Gasman

Enthusiastic Amateur
Local time
Today, 10:55
Joined
Sep 21, 2011
Messages
14,038
I think you would need Max() not Last for the transaction date ?
 

Ranman256

Well-known member
Local time
Today, 06:55
Joined
Apr 9, 2015
Messages
4,337
your Q1: qryLastTransaction , I assume:
select LastOfTransaction() from table where date between forms!fMyForm!txtStartDate and forms!fMyForm!txtEndDate

Now Q2, join the t.Transactions table with Q2 on Date and Airline.
now sum your fuel.
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:55
Joined
Sep 21, 2011
Messages
14,038
I managed it as
Code:
SELECT T1.CMS, Sum(T1.Amount) AS SumOfAmount
FROM Emails T1
WHERE T1.TransactionDate = (SELECT Max(TransactionDate) FROM Emails T2 WHERE T2.CMS = T1.CMS)
GROUP BY T1.CMS;
 

plog

Banishment Pending
Local time
Today, 05:55
Joined
May 11, 2011
Messages
11,611
First, as I said in the prior post, GallonsReceived and GallonsIssued should not be seperate fields, they should be in just a Quantity field with negative numbers denoting Issued and positive numbers denoting Received. You're building on a poor structure that will bite you.

As Gasman said--you should use MAX not LAST. LAST is a horrible function in Access and doesn't operate in the manner people think it does. qryLastTransaction should be this SQL


Code:
SELECT Max(tblAirlineTransactions.TransactionDate) AS LastTransactionDate, tblAirlineTransactions.AirlineCompanyLookup
FROM tblAirlineTransactions
WHERE (((tblAirlineTransactions.GallonsReceived)>0))
GROUP BY tblAirlineTransactions.AirlineCompanyLookup;

This is where you would put your transaction date criteria as well. Then to get the data you want, you take the above query and link it to tblAirlineTransactions where AirlineCompanyLookup = AirlineCompanyLookup and LastTransactionDate = TransactionDate.

Your current qryTransactions is a bad query because you GROUP BY in and and could be omiting records that should be included in the final total.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:55
Joined
May 7, 2009
Messages
19,169
see qryFinal
 

Attachments

  • Database1.accdb
    480 KB · Views: 504

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 10:55
Joined
Sep 12, 2006
Messages
15,613
Another alternative

1. I removed the lookup combo box from AirlineLookup
2. I did two new queries qryGTHLastDate, qryGTHResult
3. The first query finds the highest date. The second gives the results.

Out of interest, I see all the system tables are unhidden. Do you use them? I have never once used a system table.
 

Attachments

  • TestTable.accdb
    736 KB · Views: 364

foshizzle

Registered User.
Local time
Today, 06:55
Joined
Nov 27, 2013
Messages
277
thanks for everyone's thoughts and suggestions. Not sure why i was having such an issue with this. In the end, i just started over with a bunch of smaller queries; i was trying to make it harder than it was for some reason
 

Users who are viewing this thread

Top Bottom