Issues with a Query (1 Viewer)

woodsy74

Registered User.
Local time
Today, 03:59
Joined
Jul 25, 2012
Messages
26
I am struggling to get this query to work. I can get the answers I need if I dump everything into excel but I am trying to get it done via Access so other users can get what they need with one click. I've attached a Test db with samples of the two tables being used. tbl_CustData contains all of the account numbers for each customer along with the balance and payment date. tbl_Accounts just groups the various account numbers into the names I want to use (there are other account numbers on tbl_CustData but I only care about the ones listed in tbl_Accounts).

I want to be able to input a Date and pull back the balances for each customer as of that date. So far I haven't been able to get what I wanted because if I say >= payment date then I also get older balances that I don't want.

The other issue I was having was to have the output use the Account Groups as the headers and for each customer sum all of the balances that go under that group. Below are samples of two outputs, one running it using 4/30/2020 as the date and the other using 5/31/2020.

Let me know if you have any thoughts on how I can get this data or if you need any other information from me. Thank you for your time.

As of4/30/2020
CUST_NOEXPENSEPRINCIPALINTEREST
111-38345.86-211653.23-46858.29
555-440-163432.2-30112.94
As of5/31/2020
CUST_NOEXPENSEPRINCIPALINTEREST
111-39111.63-211653.23-46858.29
555-455-163432.2-30112.94
 

Attachments

  • Database1.zip
    17.2 KB · Views: 137

Gasman

Enthusiastic Amateur
Local time
Today, 07:59
Joined
Sep 21, 2011
Messages
14,050
I want to be able to input a Date and pull back the balances for each customer as of that date. So far I haven't been able to get what I wanted because if I say >= payment date then I also get older balances that I don't want.
You have lost me there. If you want balance up to a date, sure the criteria would be <= DateEntered
 

woodsy74

Registered User.
Local time
Today, 03:59
Joined
Jul 25, 2012
Messages
26
You have lost me there. If you want balance up to a date, sure the criteria would be <= DateEntered
Sorry, I meant to write <=. When I do that I get all of the balances for the Account Number that is <= the DateEntered and not just the one balance per Account Number that is closest to the DateEntered.
 

Isaac

Lifelong Learner
Local time
Today, 00:59
Joined
Mar 14, 2017
Messages
8,738
I have attached your database with a new query in it. I don't do subqueries very often in MS Access, so I could have made a mistake, but checking a few it seems correct.
PS .. this addresses the first part of your two part question.
 

Attachments

  • Database1.accdb
    424 KB · Views: 138
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 07:59
Joined
Sep 21, 2011
Messages
14,050
Sorry, I meant to write <=. When I do that I get all of the balances for the Account Number that is <= the DateEntered and not just the one balance per Account Number that is closest to the DateEntered.
Well I think that is part of your problem? You should not be storing Balances, just calculating when needed.? So you would sum all the transactions for an account and get the balance that way.

Your way, you could try Max(PaymentDate) in your query
Something along the lines of
Code:
SELECT Transactions.Description, Sum(Transactions.Amount) AS SumOfAmount, Max(Transactions.TransactionDate) AS MaxOfTransactionDate
FROM Transactions
GROUP BY Transactions.Description
HAVING (((Max(Transactions.TransactionDate))<=#6/1/2016#));
or something like
Code:
SELECT Transactions.Description, Max(Transactions.TransactionDate) AS MaxOfTransactionDate, Last(Transactions.Amount) AS LastOfAmount
FROM Transactions
WHERE (((Transactions.Amount)<>0))
GROUP BY Transactions.Description
HAVING (((Max(Transactions.TransactionDate))<=#6/1/2016#));

I do not have balances so had to use my Amount field.
 
Last edited:

woodsy74

Registered User.
Local time
Today, 03:59
Joined
Jul 25, 2012
Messages
26
I have attached your database with a new query in it. I don't do subqueries very often in MS Access, so I could have made a mistake, but checking a few it seems correct.
PS .. this addresses the first part of your two part question.
This seemed to work in my sample the db for Part I. Another question though. Is there a way to account for times when the same Account_No is using the same Date? I tried running this against a larger population of Customer data and I got a message stating "At most one record can returned by this subquery". When I looked through the data I found instances of the same Account No using the same Date. Is there a way for it to return either one of them or both? I need to talk with the users of the data to see if this is correct or not. Since I see more than a couple instances I'm thinking it might not be a mistake. There may also be another column on the table to help me determine which entry I want to use.
 

Isaac

Lifelong Learner
Local time
Today, 00:59
Joined
Mar 14, 2017
Messages
8,738
Is there a way to account for times when the same Account_No is using the same Date? I tried running this against a larger population of Customer data and I got a message stating "At most one record can returned by this subquery". When I looked through the data I found instances of the same Account No using the same Date. Is there a way for it to return either one of them or both?
I believe if you add a column to the ORDER BY (of the subquery), on a column that is unique (such as your primary key ID which you hopefully have), it will solve that.
I did test that and it works in your sample db - I added [ID] (autonumber) to your table, then added , [ID] to the order by portion of the subquery and it worked.
It does seem a bit silly that Access Top 1 will not always return a single value. Then again, if it did, the randomness would be unapparent to many, and it might not have fleshed out this issue for you, so maybe that's just as well.
 
Last edited:

Isaac

Lifelong Learner
Local time
Today, 00:59
Joined
Mar 14, 2017
Messages
8,738
Then again, how will that let you know the precise balance as of the latest time in the day? Since the one it choses will be a bit random.
Probably adding a Time portion to the datestamp will help.

I feel I must add, though, that in principle, I agree with Gasman. Generally, it's discouraged to store 'calculate-able' information in tables. There are a few reasons. One is what happens when the data factors (previously used in the calculation), change? What happens to the calculated value then? Another is simply efficiency and storage. Why store any calculation that you can calculate when needed at runtime.
Having said that, this principle is sometimes overstated. At least in larger RDBMS there are any number of good reasons for sometimes materializing aggregations. Ranging from the complexity of the calculation, to the time it takes to calculate, to snapshotting, to the simple desire of a business unit for a pre-aggregated datamart.
However, probably none of those exemptions apply in your case.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:59
Joined
May 7, 2009
Messages
19,169
you may use Top 1 on your query.
 

woodsy74

Registered User.
Local time
Today, 03:59
Joined
Jul 25, 2012
Messages
26
Then again, how will that let you know the precise balance as of the latest time in the day? Since the one it choses will be a bit random.
Probably adding a Time portion to the datestamp will help.

I feel I must add, though, that in principle, I agree with Gasman. Generally, it's discouraged to store 'calculate-able' information in tables. There are a few reasons. One is what happens when the data factors (previously used in the calculation), change? What happens to the calculated value then? Another is simply efficiency and storage. Why store any calculation that you can calculate when needed at runtime.
Having said that, this principle is sometimes overstated. At least in larger RDBMS there are any number of good reasons for sometimes materializing aggregations. Ranging from the complexity of the calculation, to the time it takes to calculate, to snapshotting, to the simple desire of a business unit for a pre-aggregated datamart.
However, probably none of those exemptions apply in your case.
I added the second Order By and it seemed to work for me. I just need to discuss with users to make sure what I am using for the second Order By makes sense. As far as the storing of the data I have no control over that. This is how the db is set up already. I was just trying to extract what was needed. Thanks.
 

Isaac

Lifelong Learner
Local time
Today, 00:59
Joined
Mar 14, 2017
Messages
8,738
I added the second Order By and it seemed to work for me. I just need to discuss with users to make sure what I am using for the second Order By makes sense
Glad it worked out. That seems like a wise thing to do.
 

Users who are viewing this thread

Top Bottom