Query, No Duplicate Accounts find the correct Date (1 Viewer)

JahJr

Andy
Local time
Today, 10:58
Joined
Dec 3, 2008
Messages
93
I have 3 tables
tblAccountNumbers
tblAccountValues
tblAccountFees

I need to build a query that pulls the account value for each account number and pull the correct % Fee. The account fee that is getting pulled in from tblAccountFees should be the fee that has an effective date that is <= to the Date selected in the form that runs the query, If there are multiple Fees that fit this criteria then it should pick the one with the largest value in the DateEntered Column.

The query should only have 1 row for each account. If you run the query for 3/31/20 you should get the following results
AccountNumberAccountValueValueDateFeePercentFeeEffectiveDateDateEntered
Acct1$1003/31/20201.10%2/28/20204/19/2021
Acct2$2003/31/20202.00%12/31/201912/31/19
Acct3$3003/31/20203.00%12/31/201912/31/19
Acct4$4003/31/20204.00%12/31/201912/31/19

I've tried multiple scenarios and cant get anything to work. I think I'm going to need a sub query but cant figure out where to start. I've attached a sample db.

Andy
 

Attachments

  • FeeQuery.accdb
    736 KB · Views: 227

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:58
Joined
May 7, 2009
Messages
19,175
i made 2 little queries (Query1 and Query2).
i combine them to make the "final" query (queryFinal).
 

Attachments

  • FeeQuery.accdb
    480 KB · Views: 219

JahJr

Andy
Local time
Today, 10:58
Joined
Dec 3, 2008
Messages
93
That is really close. It works for 3/31/20 but all other dates return the following error

"At most one record can be returned by this subquery."

It is Query1 that is throwing this error.

I changed Query 1 to the following
SQL:
SELECT tblAccountValues.AccountNumber, tblAccountValues.AccountValue, tblAccountValues.ValueDate
FROM tblAccountValues
WHERE (((tblAccountValues.ValueDate)=[Forms]![frmRunReport]![cboDate]));

That eliminated the earlier error. With this I was able to see that it doesn't return the Acct3 Fee of 3.30% for any of the dates.
 
Last edited:

JahJr

Andy
Local time
Today, 10:58
Joined
Dec 3, 2008
Messages
93
I got it working. The db had some files that were not stored as dates in the tblFees. I corrected those along with the Query1 edit and everything is working as it should . Thank you
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:58
Joined
May 7, 2009
Messages
19,175
on Table tblFees dont use Date/Time Extended, only use Date/Time.
 

Attachments

  • FeeQuery.accdb
    480 KB · Views: 340

Users who are viewing this thread

Top Bottom