Tricky query?

[Les Isaacs]

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

[CJ_London]

rather than describing the situation, provide some examples. It is not an uncommon requirement but the description is incomplete. For example does the client ID in the accounts table match the client ID in the fees table?

Best I can suggest at the moment is to use a union query to combine the tables

[ranman256]

you most likely want an outer join

in a query, join your 2 tables,
dbl-click the join line,
chose SHOW ALL records in tAccts, SOME in tFeesDue
OK
bring down the joined field in both tables.
run query
the items with NULL field are the missing ones.

this will show all accounts, even if they have nothing due.

but if you only want to see the Accts that DO have FeesDue, then the normal join will do this.

[Les Isaacs]

rather than describing the situation, provide some examples. It is not an uncommon requirement but the description is incomplete. For example does the client ID in the accounts table match the client ID in the fees table?

Best I can suggest at the moment is to use a union query to combine the tables

Ajax - yes, the client IDs are related, with referential integrity.
By way of an example, let's say that, with a 'pay month' of "June 2022", client1 has a duedate of 15/6/22 and client2 has a duedate of 21/6/22. With a 'from' value of "June 2022" selected, the query must output all records, from either table, for client1 with a date of 15/6/22 or later and for client2 with a date of 21/6/22 or later.
Does that help?

Thanks
Les

[Les Isaacs]

Hi ranman256
Thanks for your reply.
I already have the query joining the 2 tables, the problem I'm having is that the 'date' criteria (the from date) varies from client to client - it is their 'due date' for the relevant 'pay month'.
Hope that helps?

Thanks again
Les

[CJ_London]

Does that help?

not really, it is just another description.

[Les Isaacs]

not really, it is just another description.

No it isn't - it's an example of the data, which is what you asked for, I believe.
Not to worry - I'll figure it out, even if you can't!
Thanks anyway.

[CJ_London]

example data would be presented as a table or tables and would include all variations that that data might take. And then include the required outcome. I can't work out from your description why a 'from' date of 'june 2022' includes client2 and not prepared to spend time on the next x number of posts trying to clarify.

Good luck, but I'll drop out now, perhaps someone else can work out what you mean