Trying to make an invoice with info from many tables

G

Guest

Help! I have an access database for a school that I run. In one table, I
have the parent names. In another table with a one to many relationship to
the parent table, I have the children's names. Each child has a field
called tuition. In addition I have a table called payments, which is also a
one to many relationship to the parent table.

For my invoices, I created a report based on a query of the parents and
children. The report lists all the children of a parent with their tuitions
and totals them up. What I need to do on this invoice is to also total up
all the payments that a parent has made on the account.

If I add the payments table to my underlying query, I get errors because the
joins are ambiguous. I tried making a query based on 2 queries, but the
payments are getting double counted for each child. I am able to make a
separate report of the payments, but if I add it as a subreport, I can't seem
to match the payment to the parent. I can't use DLookup because the total
tuition is not stored anywhere, just each individual payment.

I'm just not familiar enough with Access to figure out how to do this. Any
help would be greatly appreciated!
 
D

Duane Hookom

If you want to show individual payments made by parents, you can create a
subreport based on the payments table and place it in the Parents Group
Header or Footer in the main report. You can total the payments in the
subreport Report Footer and reference it in the main report.
 
G

Guest

Yes - it worked. Thanks so much!

Duane Hookom said:
If you want to show individual payments made by parents, you can create a
subreport based on the payments table and place it in the Parents Group
Header or Footer in the main report. You can total the payments in the
subreport Report Footer and reference it in the main report.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top