account statement

[timbits35 via AccessMonster.com]

I have the following tables :

tblsales - member ID, invnum, invdate etc
tblsalesdetails - memberID, salesID, serviceID, salesprice, qty
tblpayments - invnum, paymentID, paymentdate, paymentamount

I already have a report that shows me the outstanding balance per invoice
that has the TOTAL sales, TOTAL payments and then a calculated field for
balance due. I did this by making a query for the total sales, a query for
the total payments and then a 3rd query for the payments due. Now I want to
create a report but not with the totals but all the details. So for each
invoice number (which is in tblsales) I want to show BOTH the sales details
and all the payment details on the same report. Something like this :

Invnum AB1234
Sales Details
Yearly Plan $250.00
Open file $ 50.00
Payment Details
March 5 $70.00
April 10 $100.00
May 1 $100.00
May 25 $ 30.00

I cannot figure out how to do this. I have an invoice which shows the sales
details. But how do I get the payments details as well on the same report?

Thanks,
Liane

 

[Marshall Barton]

I have the following tables :

tblsales - member ID, invnum, invdate etc
tblsalesdetails - memberID, salesID, serviceID, salesprice, qty
tblpayments - invnum, paymentID, paymentdate, paymentamount

I already have a report that shows me the outstanding balance per invoice
that has the TOTAL sales, TOTAL payments and then a calculated field for
balance due. I did this by making a query for the total sales, a query for
the total payments and then a 3rd query for the payments due. Now I want to
create a report but not with the totals but all the details. So for each
invoice number (which is in tblsales) I want to show BOTH the sales details
and all the payment details on the same report. Something like this :

Invnum AB1234
Sales Details
Yearly Plan $250.00
Open file $ 50.00
Payment Details
March 5 $70.00
April 10 $100.00
May 1 $100.00
May 25 $ 30.00

I cannot figure out how to do this. I have an invoice which shows the sales
details. But how do I get the payments details as well on the same report?

I think you can do that by creating a subreport for the
payments query and then using it as a subreport in the
invoice footer section (linked on the InvNum field.