Hi everyone,
I have come to the end of myself trying to figure this query out, I hope someone can kindly help. I have two tables (simplified for this example).
The first table, TDays, contains one field only (FDate). This is simply a list of dates covering an entire year:
FDate
01/01/2012
01/02/2012
01/03/2012
01/04/2012
etc.
The second table, TLimits, contains three fields (FAccountID, FLimit, FEffectiveDate). In this table you specify an account, credit limit, and effective date:
FAccountID, FLimit, FEffectiveDate
AccountA, -2000.00, 01/01/2012
AccountB, -1000.00, 01/01/2012
AccountA, -5000.00, 01/03/2012
etc.
The above example shows how the credit limit is changed for AccountA to -5000.00, effective from 01/03/2012.
I'm trying to write a query that outputs the credit limit for each account, for each day in table TDays - like this:
FDate, FAccountID, FLimit
01/01/2012, AccountA, -2000.00
01/01/2012, AccountB, -1000.00
01/02/2012, AccountA, -2000.00
01/02/2012, AccountB, -1000.00
01/03/2012, AccountA, -5000.00
01/03/2012, AccountB, -1000.00
01/04/2012, AccountA, -5000.00
01/04/2012, AccountB, -1000.00
etc.
Thank you in advance for any help you can give, much appreciated :-)
Michal