I have two queries which are used to get the SUM of previous record groups and the group prior to that using TOP 3 and TOP 2. (I need the 3 most current groups of records).
Now I need to reference those SUMs (for each group/airline) for additional calculations; I'm guessing through another query. I'm just not sure how to do that because when I try joining the two tables in a new query, the data doesn't present properly. i.e., the totals for each date are correct but all dates are the same.
TOP3
TOP2
Final
Now I need to reference those SUMs (for each group/airline) for additional calculations; I'm guessing through another query. I'm just not sure how to do that because when I try joining the two tables in a new query, the data doesn't present properly. i.e., the totals for each date are correct but all dates are the same.
TOP3
SELECT tblAirlineTransactions.TransactionDate, tblAirlineTransactions.AirlineCompanyLookup, Sum(tblAirlineTransactions.GallonsIssued) AS SumOfGallonsIssued, Sum(tblAirlineTransactions.GallonsReceived) AS SumOfGallonsReceived
FROM tblAirlineTransactions
GROUP BY tblAirlineTransactions.TransactionDate, tblAirlineTransactions.AirlineCompanyLookup, tblAirlineTransactions.TransactionDate
HAVING (((tblAirlineTransactions.TransactionDate) In (SELECT TOP 3 TransactionDate
FROM
(SELECT DISTINCT TransactionDate FROM tblAirlineTransactions)
ORDER BY TransactionDate DESC;
)));
TOP2
SELECT tblAirlineTransactions.TransactionDate, tblAirlineTransactions.AirlineCompanyLookup, Sum(tblAirlineTransactions.GallonsIssued) AS SumOfGallonsIssued, Sum(tblAirlineTransactions.GallonsReceived) AS SumOfGallonsReceived
FROM tblAirlineTransactions
WHERE (((tblAirlineTransactions.TransactionDate) In (SELECT TOP 2 TransactionDate
FROM
(SELECT DISTINCT TransactionDate FROM tblAirlineTransactions)
ORDER BY TransactionDate DESC;)))
GROUP BY tblAirlineTransactions.TransactionDate, tblAirlineTransactions.AirlineCompanyLookup;
Final
SELECT TOP 1 Top3.TransactionDate, Top3.AirlineCompanyLookup, Top3.SumOfGallonsIssued, Top2.SumOfGallonsIssued, Top3.SumOfGallonsReceived, Top2.SumOfGallonsReceived
FROM qryAirlineTransactionsTop3 AS Top3 LEFT JOIN qryAirlineTransactionsTop2 AS Top2 ON Top3.AirlineCompanyLookup = Top2.AirlineCompanyLookup
GROUP BY Top3.TransactionDate, Top3.AirlineCompanyLookup, Top3.SumOfGallonsIssued, Top2.SumOfGallonsIssued, Top3.SumOfGallonsReceived, Top2.SumOfGallonsReceived
ORDER BY Top3.TransactionDate DESC;