Report help needed

[Steve]

i have a simple DB with 3 tables: Original Contract, Change Orders, and
Invoices. Each entry requires a Job #. All jobs have only 1 original amount,
but may have many CO's and Invoices. i would like to run a report that would
take the original contract amount, apply all CO's and Invoices, and display
the current contract balance. Any help would be appreciated.

 

[Marshall Barton]

i have a simple DB with 3 tables: Original Contract, Change Orders, and
Invoices. Each entry requires a Job #. All jobs have only 1 original amount,
but may have many CO's and Invoices. i would like to run a report that would
take the original contract amount, apply all CO's and Invoices, and display
the current contract balance. Any help would be appreciated.

Try this sort of thing:

SELECT OC.[Job #],
OC.amount + Sum(CO.amount) - Sum(INV.amount) As Bal
FROM (Original Contract As OC LEFT JOIN Change Orders As CO
ON OC.[Job #] = CO.[Job #])
LEFT JOIN Invoices As INV
ON OC.[Job #] = INV.[Job #]