3 table report problem

[spinningwoman]

I am using Access 2000 to design a relational database. Although I have used dbase and foxbase professionally in the rather distant past, I have no previous experience with Access. If it works out, I may well upgrade to a more recent version, but this is what I currently have.

I am trying to generate a report from a database which has tables for events, set fees, and actual payments. Both fees and payments have a many to one relationship with the events file, rather than being directly related to each other. I am reluctant to change this as this is the way it works in reality - I may receive one single payment for an event to cover several different fee items, or I may receive more than one payment which covers the total of the fees but where the individual cheques do not relate to a particular fee line.

I want a report which will show, for each event, a list of fees and a list of payments with totals so that I can see where payment is not complete. However, setting it up with the report wizard, I can only seem to get payments subordinate to fees, so that the payments made are duplicated on every fee line of the report and so can't be totalled without being multiplied by the number of fee lines.

Can I do this with the report wizard? If not, can I do it with a manual design?

I have the Access 2000 for Dummies and Access 2000 Bible if anyone wants to point me to a suitable reference there.

Thanks

 

[pbaldy]

That type of relationship would often (usually?) be displayed with subreports. Your main report would be based on the event table, and the other 2 tables would be on subreports. Master/child links would keep the subreports in sync with the main report. You can't do the whole thing with a single wizard, to my knowledge. You can make a single report for each table, then in design view of the event report, use the subreport wizard to drop the others into it.