Query, No Duplicate Accounts find the correct Date

[JahJr]

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

AccountNumber	AccountValue	ValueDate	FeePercent	FeeEffectiveDate	DateEntered
Acct1	$100	3/31/2020	1.10%	2/28/2020	4/19/2021
Acct2	$200	3/31/2020	2.00%	12/31/2019	12/31/19
Acct3	$300	3/31/2020	3.00%	12/31/2019	12/31/19
Acct4	$400	3/31/2020	4.00%	12/31/2019	12/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

 

[arnelgp]

i made 2 little queries (Query1 and Query2).
i combine them to make the "final" query (queryFinal).

 

[JahJr]

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

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.

 

[JahJr]

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]

on Table tblFees dont use Date/Time Extended, only use Date/Time.