OK. Well I'm going to start by reminding you of the requirement for an ID field for your [tblLoanAmounts] table. I'm going to assume you've added one called [TranID] which is defined as
AutoNumber.
Table = [tblLoanAmounts] - FieldName Type
-
TranID AutoNumber
-
TranDate Date
-
LoanAmount Number
-
...
Table = [tblDates] - FieldName Type
-
TranDate Date
I still don't know if there is any other of the fields you want to GROUP BY, so I've ignored that part and assumed no other groupings. If that's the case then [TranDate] would probably work as a unique reference, but in case it's not I've added [TranID] for the extra flexibility.
The SQL for the query should be of the form :
- SELECT [TranDate]
-
, (SELECT [LoanAmount]
-
FROM [tblLoanAmounts] AS [tLA]
-
WHERE ([TranID]=(SELECT [TranID]
-
FROM [tblLoanAmounts]
-
WHERE ([TranDate]=(SELECT Max([TranDate]) AS [MaxTD]
-
FROM [tblLoanAmounts]
-
WHERE ([TranDate]<=[tD].[TranDate])))))) AS [LoanAmount]
-
FROM [tblDates] AS [tD]
-
ORDER BY [TranDate]
Starting from the innermost SELECT query :
- Lines #6- #8 Return the furthest forward [TranDate] in [tblLoanAmounts] that is either on or before the [tblDates].[TranDate] from the outermost query.
- Lines #4- #8 Return the [TranID] for the [tblLoanAmounts] record that matches A above.
- Lines #2- #8 Return the [LoanAmount] from the record identified by B above.
- Lines #1-#10 Return the [tranDate] from [tblDates] along with the value from C above and sort all records by [tblDates].[TranDate]
With records in [tblDates] running from 1st March 2016 to 10th March 2016 and the following data in [tblLoanAmounts] I got the results shown at the bottom.
- TranID TranDate LoanAmount
-
1 03/03/2016 20
-
2 05/03/2016 50
-
3 08/03/2016 100
- TranDate LoanAmount
-
01/03/2016
-
02/03/2016
-
03/03/2016 £20.00
-
04/03/2016 £20.00
-
05/03/2016 £50.00
-
06/03/2016 £50.00
-
07/03/2016 £50.00
-
08/03/2016 £100.00
-
09/03/2016 £100.00
-
10/03/2016 £100.00