Bank statement-style report from two tables
I am trying to create a bank statement-style report of a supplier statement.
Basically, the statement will contain four fields from three related tables. The fields are: Date, Credit, Debit and Balance.
One table is "Supplier" which contains a unique supplier ID, the next table is "Invoices", and the final is "Payments", all linked using the unique supplier ID.
So, the data is stored like this: Date(Both Invoices and Payments table), Credit(Payments table), Debit(Invoices table) and Balance(summary field).
But, I can't figure out how to get the information from the Invoices and Payments tables to show on my report. I have tried creating a new table called "Supplier Statement" with the correct calculation fields, but this table contains 0 records, so I don't know if I need to perform some sort of scripted import from the other tables?
Or maybe I am just being really stupid and missing something simple?
Thanks all in advance,