Solved Complex query: GROUP BY EACH Latest account balance (1 Viewer)

ironfelix717

Registered User.
Local time
Today, 12:49
Joined
Sep 20, 2019
Messages
193
HI,

I need to write a query that finds the "closest" account balance to a specified date for each account in the system. I've tried this for hours. I'm done messing around and getting nowhere.

There are 2 tables: Accounts & Transactions.

I need to group the Transactions by each Account in 'ACCOUNTS' (or really doesn't even need to be linked to Accounts - see below).

Then find the 'BALANCE' closest to a specific date. - FOR EACH account

This query needs to return each unique account name. Note: Record in 'Transactions' will never have an account that doesn't exist in Accounts table. (see bold above)


Example question: List the balance for each account in transactions on 10/18/19
 

Attachments

  • GroupBy Query.zip
    25.1 KB · Views: 183

plog

Banishment Pending
Local time
Today, 11:49
Joined
May 11, 2011
Messages
11,611
This has a whiff of homework to it. Which is fine, but in these instances you will need to tell us what you have tried and how its not working.

So, write/describe the way you think it should be tackled adn we will guide you there.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:49
Joined
May 7, 2009
Messages
19,169
if the transaction are well inputted, meaning it is inputted as the transaction occurs and not by bulk entry (where you enter a transaction in no particular order). then you can create a Total query first, grouped by Account, order by Date descending, ID descending (see Query1).
create another Parameter query and plug in Query1 (see FinalQuery).
 

Attachments

  • GroupBy Query.zip
    28.9 KB · Views: 132

ironfelix717

Registered User.
Local time
Today, 12:49
Joined
Sep 20, 2019
Messages
193
Hi,

Returning with more information. Thanks for your replies.

I left out a key point: the data is not well inputted, meaning the IDs do not necessarily scale with the time order of the data. Therefore, arnelgp's query will not work. This is also a problem I ran into.

However,

On that note, it should be noted that: Although the IDs may be essentially random, the HIGHEST ID for a given date takes precedence over any other IDs of the same date which are less.

For example:

#1 12/1/19 500
#2 12/2/19 600
#3 12/1/19 450

450 (#3) is the value considered to have occurred LAST on 12/1/19.


Its fair to say, you need a minimum of 2 queries with this request. All I need is the ID of the closest value and i can join the rest of the required fields. This is a completely simple query when just querying for a single account, but when trying to return every account is when things get complicated.
Thanks for any help!


Learn SQL they said, its powerful they said (n)
 

plog

Banishment Pending
Local time
Today, 11:49
Joined
May 11, 2011
Messages
11,611
First, [Date] is a terrible field name. It's a reserved word and makes coding more difficult. I suggest you rename it for what date it represents (TransactionDate). Onto the SQL. Since you can have multiple balances per data, you need a query to find the right ID per date:

Code:
SELECT Account, [Date] AS BalanceDate, Max(ID) AS BalanceID
FROM TRANSACTIONS
GROUP BY Account, [Date];

Name that SQL 'sub1'. Now you need a query where you can set the data criteria and get the highest date in Transactions that meets that criteria:

Code:
SELECT Account, Max(BalanceDate) AS LastDate
FROM sub1
WHERE (BalanceDate<#11/1/2019#)
GROUP BY Account;

Name that 'sub2' and change 11/1/2019 to whatever you want. Then for the final query this will produce the results you want:

Code:
SELECT sub2.Account, sub2.LastDate, TRANSACTIONS.Balance
FROM (sub2 INNER JOIN sub1 ON (sub2.LastDate = sub1.BalanceDate) AND (sub2.Account = sub1.Account)) INNER JOIN TRANSACTIONS ON (sub1.BalanceDate = TRANSACTIONS.Date) AND (sub1.Account = TRANSACTIONS.Account);
 

Users who are viewing this thread

Top Bottom