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

[theDBguy]

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

Okay, I fixed Query2, it now shows like this.

So, if I am beginning to understand your requirements, I don't think you need to use the TOP queries (I'm not using them here). We could make this query (Query2) to prompt the user for the Report Date, and it will only show the data for that date.

 

[theDBguy]

Okay, I fixed Query2, it now shows like this.

View attachment 85905

So, if I am beginning to understand your requirements, I don't think you need to use the TOP queries (I'm not using them here). We could make this query (Query2) to prompt the user for the Report Date, and it will only show the data for that date.

PS. I could also fix Query2 to display a 0 when there's no transaction for that date. Which would you prefer (blank or zero)?

 

[foshizzle]

PS. I could also fix Query2 to display a 0 when there's no transaction for that date. Which would you prefer (blank or zero)?

View attachment 85908

Lol - I've been checking all day and I didnt realize there was a second page for this post..

For your comment"So, if I am beginning to understand your requirements, I don't think you need to use the TOP queries (I'm not using them here). We could make this query (Query2) to prompt the user for the Report Date, and it will only show the data for that date."

Correct, it doest not appear the TOP queries are relevant anymore. Also, I was using the below TransactionDate criteria for capturing the report date from the user. (Needs to be changed to accept the single date instead of between).
Between [Forms]![frmReportBuildSum]![txtStartDate] And [Forms]![frmReportBuildSum]![txtEndDate]

And yes, a 0 is preferred. Should be 100% after this!!!

 

[foshizzle]

Lol - I've been checking all day and I didnt realize there was a second page for this post..

For your comment"So, if I am beginning to understand your requirements, I don't think you need to use the TOP queries (I'm not using them here). We could make this query (Query2) to prompt the user for the Report Date, and it will only show the data for that date."

Correct, it doest not appear the TOP queries are relevant anymore. Also, I was using the below TransactionDate criteria for capturing the report date from the user. (Needs to be changed to accept the single date instead of between).
Between [Forms]![frmReportBuildSum]![txtStartDate] And [Forms]![frmReportBuildSum]![txtEndDate]

And yes, a 0 is preferred. Should be 100% after this!!!

Hi DBGuy, checking in for the remaining code when available. Thanks so much for all your efforts and patience here

 

[theDBguy]

Hi DBGuy, checking in for the remaining code when available. Thanks so much for all your efforts and patience here

Sorry, got busy with work. Let me try again when I get a chance.

 

[theDBguy]

Hi DBGuy, checking in for the remaining code when available. Thanks so much for all your efforts and patience here

Okay, for Query2, here's the SQL.

SELECT Query3.TransactionDate, Query3.AirlineCompanyLookup, (SELECT Nz(Sum(T1.GallonsReceived))-Nz(Sum(T1.GallonsIssued)) FROM tblAirlineTransactions T1 WHERE T1.TransactionDate<Query3.[TransactionDate] AND T1.AirlineCompanyLookup=Query3.AirlineCompanyLookup) AS BegBalance, Sum(Nz([GallonsReceived])) AS GallonsRcvd, Sum(Nz([GallonsIssued])) AS GallonsIssd, Nz([BegBalance])+Nz(Sum([GallonsReceived]))-Nz(Sum([GallonsIssued])) AS Total
FROM Query3 LEFT JOIN tblAirlineTransactions ON (Query3.AirlineCompanyLookup = tblAirlineTransactions.AirlineCompanyLookup) AND (Query3.TransactionDate = tblAirlineTransactions.TransactionDate)
WHERE (((Query3.TransactionDate) Between [start date] And [end date]))
GROUP BY Query3.TransactionDate, Query3.AirlineCompanyLookup
ORDER BY Query3.TransactionDate DESC , Query3.AirlineCompanyLookup;

Query2 uses/refers to Query3. Here's the SQL for Query3.

SELECT DISTINCT tblAirlineTransactions_1.TransactionDate, tblAirlineTransactions.AirlineCompanyLookup
FROM tblAirlineTransactions, tblAirlineTransactions AS tblAirlineTransactions_1;

Hope that helps...

 

[foshizzle]

Hey DBGuy, I finally had a chance to get some data from the user today and the reports match after entering in the beginning balance! Thanks for your help and perseverance!

 

[theDBguy]

Hey DBGuy, I finally had a chance to get some data from the user today and the reports match after entering in the beginning balance! Thanks for your help and perseverance!

Okay. Glad to hear that. Good luck with your project.