Solved Join queries-reference group by date (1 Viewer)

[foshizzle]

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

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;



 

[theDBguy]

Hi. Are you able to share a sample copy of your db. Complex queries like these may be easier to work with if there's an actual file we can try. Just a thought...

 

[foshizzle]

attached - thank you

 

[theDBguy]

attached - thank you

Hi. You're only getting one date because you're using TOP 1 in your query. Try to set the query to Return All and see what happens. You will get all the dates, but It's probably not the result you're after. To get the result you want, you'll have to explain more what you're trying to achieve, starting with the table data and explain each query and their purpose.

 

[foshizzle]

Its kind of confusing to setup because this is a newly built DB, based off an old one in which every field was hard-coded, including the calculations. The new report needs to mimic the original report in the previous DB.

Explanation of Data
tblAirlineTransactions is a table of fuel gallons received or issued for each airline, each day which the airline had a flight and fuel transaction.
qryAirlineTransactionsTop1 was created to get the current record SUM of GallonsIssued/GallonsReceived for each airline.
qryAirlineTransactionsTop2 was created to get the previous record SUMs of GallonsIssued/GallonsReceived for each airline.
qryAirlineTransactionsTop3 was created to get the previous two record SUMs of GallonsIssued/GallonsReceived for each airline.

Each of the TOP queries work properly. I'm just having trouble pulling the dates in together in order to use it on one single (sub)report as follows:
PreviousBalance: =OldCurrentTotal
CurrentReceived: =[GallonsReceived]
CurrentSubtotal: =OldCurrentTotal+[GallonsReceived]
CurrentIssued: =[GallonsIssued]
CurrentTotal: =CurrentSubtotal-[GallonsIssued]

So the PreviousBalance, CurrentSubtotal and CurrentTotal all depend on previous (cumulative) records. For further explanation:
If today was 10/7, this would mean the PreviousBalance value is comprised of records from 10/6 and the previous day's "PreviousBalance" on 10/5, assuming records were entered daily. I've attached an example of the previous report from 10/7.

 

[theDBguy]

Each of the TOP queries work properly.

Let me see if I get this straight. If I open tblAirlineTransactions, I see there are five (5) unique airlines with transaction records. But when I run the TOP 1 query, it only lists three (3) airlines. If this is correct, can you explain why it is correct? Thanks.

PS. When you say "current," do you mean today's date?

PPS. If by "current" you meant the latest transaction date, would this be a better result for the TOP 1 query? Just curious...

 

[foshizzle]

Let me see if I get this straight. If I open tblAirlineTransactions, I see there are five (5) unique airlines with transaction records. But when I run the TOP 1 query, it only lists three (3) airlines. If this is correct, can you explain why it is correct? Thanks.

PS. When you say "current," do you mean today's date?

PPS. If by "current" you meant the latest transaction date, would this be a better result for the TOP 1 query? Just curious...
View attachment 85844

By "current", I mean the most recent record's date.

Regarding the Top1 query, I believe your logic is correct. Only three airlines had flights on the most recent date. But, in reference to your PSS., the report will need to show ALL airlines regardless of whether or not they've had a transaction on the most recent "current" date. Meaning, it would just show their last transaction, independently of other airlines.

In the end, I just need to have a calculation for the actual date (which is the report date input from a form with begin/end dates). So if an airline hasn't had any transaction, the PreviousBalance, CurrentSubtotal and CurrentTotal would remain the same, as seen on my picture of the original report for Allegiant.

 

[theDBguy]

By "current", I mean the most recent record's date.

Regarding the Top1 query, I believe your logic is correct. Only three airlines had flights on the most recent date. But, in reference to your PSS., the report will need to show ALL airlines regardless of whether or not they've had a transaction on the most recent "current" date. Meaning, it would just show their last transaction, independently of other airlines.

In the end, I just need to have a calculation for the actual date (which is the report date input from a form with begin/end dates). So if an airline hasn't had any transaction, the PreviousBalance, CurrentSubtotal and CurrentTotal would remain the same, as seen on my picture of the original report for Allegiant.

Hi. Maybe to help us better understand what you're trying to get from the table, could you please create an Excel spreadsheet of the result you want to get from the three TOP queries and label/highlight (with different colors) which columns came from which TOP query? Thanks.

 

[foshizzle]

Thanks for your help thus far; i didnt realize it would be this difficult. i've attached a spreadsheet which I had to compress in order to post here. Really hope that helps. please let me know if you need further clarification.

 

[theDBguy]

i didnt realize it would be this difficult.

What you're asking to do may not be difficult (I don't know yet). What is difficult for us is we are not familiar with your business process and your database model. Therefore, we need to understand it first before we can understand your requirements.

Thanks for posting the new file, I'll take a look...

 

[foshizzle]

What you're asking to do may not be difficult (I don't know yet). What is difficult for us is we are not familiar with your business process and your database model. Therefore, we need to understand it first before we can understand your requirements.

Thanks for posting the new file, I'll take a look...

Can I ask how you got the results from your Query1 screenshot?

 

[theDBguy]

Can I ask how you got the results from your Query1 screenshot?

I used the following SQL statement.

SELECT tblAirlineTransactions.TransactionDate, tblAirlineTransactions.AirlineCompanyLookup, Sum(tblAirlineTransactions.GallonsIssued) AS SumOfGallonsIssued, Sum(tblAirlineTransactions.GallonsReceived) AS SumOfGallonsReceived
FROM tblAirlineTransactions
WHERE (((tblAirlineTransactions.TransactionDate)=(SELECT Max(TransactionDate) FROM tblAirlineTransactions S1 WHERE AirlineCompanyLookup=tblAirlineTransactions.[AirlineCompanyLookup])))
GROUP BY tblAirlineTransactions.TransactionDate, tblAirlineTransactions.AirlineCompanyLookup;

 

[foshizzle]

Ok, great!, I found this link, and was trying to make it work with your MAX query.

How then, would I show the MAX date from this?

SELECT qryReportSumAirlineTransactionsAll.TransactionDate, qryReportSumAirlineTransactionsAll.AirlineCompanyLookup, qryReportSumAirlineTransactionsAll.SumIssued, qryReportSumAirlineTransactionsAll.SumReceived
FROM qryReportSumAirlineTransactionsAll LEFT JOIN qryReportSumAirlineTransactionsMax ON (qryReportSumAirlineTransactionsAll.AirlineCompanyLookup = qryReportSumAirlineTransactionsMax.AirlineCompanyLookup) AND (qryReportSumAirlineTransactionsAll.TransactionDate = qryReportSumAirlineTransactionsMax.TransactionDate)
GROUP BY qryReportSumAirlineTransactionsAll.TransactionDate, qryReportSumAirlineTransactionsAll.AirlineCompanyLookup, qryReportSumAirlineTransactionsAll.SumIssued, qryReportSumAirlineTransactionsAll.SumReceived, qryReportSumAirlineTransactionsMax.TransactionDate, qryReportSumAirlineTransactionsMax.AirlineCompanyLookup
HAVING (((qryReportSumAirlineTransactionsMax.TransactionDate) Is Null) AND ((qryReportSumAirlineTransactionsMax.AirlineCompanyLookup) Is Null))
ORDER BY qryReportSumAirlineTransactionsAll.TransactionDate DESC;

 

[theDBguy]

Ok, great!, I found this link, and was trying to make it work with your MAX query.

How then, would I show the MAX date from this?

SELECT qryReportSumAirlineTransactionsAll.TransactionDate, qryReportSumAirlineTransactionsAll.AirlineCompanyLookup, qryReportSumAirlineTransactionsAll.SumIssued, qryReportSumAirlineTransactionsAll.SumReceived
FROM qryReportSumAirlineTransactionsAll LEFT JOIN qryReportSumAirlineTransactionsMax ON (qryReportSumAirlineTransactionsAll.AirlineCompanyLookup = qryReportSumAirlineTransactionsMax.AirlineCompanyLookup) AND (qryReportSumAirlineTransactionsAll.TransactionDate = qryReportSumAirlineTransactionsMax.TransactionDate)
GROUP BY qryReportSumAirlineTransactionsAll.TransactionDate, qryReportSumAirlineTransactionsAll.AirlineCompanyLookup, qryReportSumAirlineTransactionsAll.SumIssued, qryReportSumAirlineTransactionsAll.SumReceived, qryReportSumAirlineTransactionsMax.TransactionDate, qryReportSumAirlineTransactionsMax.AirlineCompanyLookup
HAVING (((qryReportSumAirlineTransactionsMax.TransactionDate) Is Null) AND ((qryReportSumAirlineTransactionsMax.AirlineCompanyLookup) Is Null))
ORDER BY qryReportSumAirlineTransactionsAll.TransactionDate DESC;

Hi. I'm sorry, I can't read your mind, so I don't know what you mean by "show the MAX date." I tried to run your query on my copy, but I'm missing qryReportSumAirlineTransactionsAll, so it won't run at all.

Like I said earlier, it would be easier for us to understand what you want if you run that query, copy the result in Excel, and then add the MAX date you're talking about. I could then try to compare it with the data you gave us and see if I can figure out what you mean.

Please help us help you...

 

[theDBguy]

Thanks for your help thus far; i didnt realize it would be this difficult. i've attached a spreadsheet which I had to compress in order to post here. Really hope that helps. please let me know if you need further clarification.

Hi. Sorry for the delay. I finally had a chance to look at your spreadsheet, and here's what I came up with (given the limited data available to me).

To explain, I have no data before 10/5/2020, so the beginning balance rows for 10/5/2020 are empty, and some of the rows for 10/6/2020 are in the negative.

What it's doing is totaling all previous days records and comparing the result to the current transaction date. Takes the numbers for the same date and produce the new total.

Hope this is what you meant. Here's the SQL statement for the above query.

SELECT tblAirlineTransactions.TransactionDate, tblAirlineTransactions.AirlineCompanyLookup, (SELECT Sum(T1.GallonsReceived)-Sum(T1.GallonsIssued) FROM tblAirlineTransactions T1 WHERE T1.TransactionDate<tblAirlineTransactions.[TransactionDate] AND T1.AirlineCompanyLookup=tblAirlineTransactions.AirlineCompanyLookup) AS BegBalance, Sum(tblAirlineTransactions.GallonsReceived) AS SumOfGallonsReceived, Sum(tblAirlineTransactions.GallonsIssued) AS SumOfGallonsIssued, Nz([BegBalance])+Sum([GallonsReceived])-Sum([GallonsIssued]) AS Total
FROM tblAirlineTransactions
GROUP BY tblAirlineTransactions.TransactionDate, tblAirlineTransactions.AirlineCompanyLookup
ORDER BY tblAirlineTransactions.TransactionDate DESC;

Hope that helps...

 

[theDBguy]

PS. When I looked at the above result, I think it may be close but not exactly what you showed in your Excel file. I think this one may be more like it shows the same thing as your Excel sample.

Please let me know if that's correct, and I'll post the updated SQL statement for Query2 and the new Query3.

 

[foshizzle]

Hm, the Totals are a little off. Can we try something else right quick? Sorry for the difficulty and misunderstandings. This one thing has been beating me up for a week now. After all the research and seeing your examples, I think I've come up with an alternative using a combination of your MAX query and this link. Forget the totals; I believe I can accomplish this by making a new subreport and hiding it.

I've uploaded a new sample DB.
qryReportSumAirlineTransactionsAll - queries all dates from tblAirlineTransactions (and does some SUMing)
qryReportSumAirlineTransactionsMax - obtains the MAX transaction record for each airline (your query)
qryReportSumAirlineTransactionsNext - uses the method from the link to show all records from qryReportSumAirlineTransactionsAll that are not listed in your MAX query.

If you could use the same technique you used in your original MAX query to get the MAX transaction record for each airline in the qryReportSumAirlineTransactionsNext query, I think we'd have it licked.. I think I could use this, plus one additional "Next" query to get the three dates and list them all in the subreport. I can then reference each for calculation.

 

[theDBguy]

Hm, the Totals are a little off.

Would that still be true if you take the data you provided earlier and manually do your calculations in Excel? If so, can you please post the result showing what was off with the image I posted? That would help me understand more about the process you're using.

 

[theDBguy]

If you could use the same technique you used in your original MAX query to get the MAX transaction record for each airline in the qryReportSumAirlineTransactionsNext query, I think we'd have it licked.

I'm not sure I understand this. Could you please create a mockup in Excel, what is the result you're expecting I'll be able to create with this new query? In other words, here's the result of qryReportSumAirlineTransactionsNext, what would you want it to look like instead after I apply the MAX method I used earlier?

I think I could use this, plus one additional "Next" query to get the three dates and list them all in the subreport.

I would like to see this. If you're only concerned about the last three (3) transaction dates, this may be doable. But if you want more than three dates, this may get a bit complex and slow. So, are you really only concerned about the last three dates?

 

[foshizzle]

PS. When I looked at the above result, I think it may be close but not exactly what you showed in your Excel file. I think this one may be more like it shows the same thing as your Excel sample.

View attachment 85877
Please let me know if that's correct, and I'll post the updated SQL statement for Query2 and the new Query3.

Sorry, I compared my Excel results to your query and i found the problem; i had entered an extra beginning balance on my sheet. So your numbers are absolutely correct!! I just need two minor adjustments:
- I need to show the subtotal column as another column in the query (for report purposes), as shown in the attached 'report_ex.jpg'.
- For the query results with no GallonsIssued or GallonsReceived, the Total number still needs to show (which would equal the BegBalance), as shown on the updatedQueryExample.jpg