Hi all
I have a table tbl_accounts, which includes fields 'acc_date', 'client', 'debit' and 'credit' (amongst others). This table is populated by downloading the bank statement, and so contains all movements on the bank account, many of which are fee receipts from clients.
I have another table tbl_feesDue, which includes fields 'due_date', 'pay_month', 'client' and 'fee_due'(amongst others). This table contains a record for each fee due for each client (generally one per per client per month), and is populated by adding the appropriate record each time a fee is incurred (generally once/month/client).
I need a query that will output each client's account - ie one list, in client then date order, of the client's 'fee_due' records from tbl_feesDue with any records from tbl_accounts for that client.
The tricky bit is that I need to run the query from a selected 'pay_month' so that the output includes all records from either table with a 'acc_date'/'due_date' that is on or after the 'due_date' for that client/'pay_month'. The trouble is that the 'due_date' values for a given 'pay_month' vary from 'client' to 'client'!!
I know I could do this singly, 'client' by 'client', but I treally could do with being able to generate a report that will summarise the position across all clients.
I have thought of combining the tables, and adding a yes/no field to tbl_accounts to designate whether the record is a bank statement record (as opposed to being a notional record of an amount due): this would obviously make tbl_accounts much bigger - but would mean one fewer table overall, and for a simpler query?
Hope someone has followed this, and can help?
Many thanks in advance.
Les