Reporting from multiple tables with duplication

[0bsidian]

Hi,

I, like most people can manage to report effectively from one table, however I am having some difficulty when it comes to reporting from two.

I have a pupil transaction table and a "buyback" table (for purchasing items back from students). I want to create an invoice report for each pupil which lists both their purchases and any items they have sold back in a tidy manner. This will allow me to put totals at the bottom of the report, taking into consideration the money they have spent, and the money due back for items sold back.

My concern is that I want to limit the amount of duplicate info (same fields in both tables) whilst showing a list of incoming (transactions) and outgoing (buy back) transactions.

PupilTransactions fields look like this:

Transaction Date
PupilID
EAN
Billing Status
Quantity
ValueExcVAT
ValueIncVAT

Buyback looks lke this:

Transaction Date
PupilID
EAN
Billing Status
Quantity
Buy Back Value
BuyBack Item? (tick/untick)

Can anyone suggest how best to do this, as I am a little fraught.

Thanks in advance for the help.

0bs