SQL - summing invoice items and taxes

I have three tables - Invoice, InvoiceItem and InvoiceItemTax, all related by InvoiceNumber. InvoiceItemTax may no exist for a given InvoiceNumber.

On Invoice is a denormalized TotalAmount column.

On InvoiceItem is a TotalPrice
On InvoiceTax is a TotalTax

I want to do an integrity check to ensure the data is accurate for all invoices.

I want to ensure the Invoice.TotalAmount equals the sum of all the related InvoiceItem.TotalPrice PLUS the sum of all the related InvoiceTax.TotalTax

I can't figure out how to do this!

all help appreciated.

I hope I didn't mis-understand what you ment.
but if you want a query that would report improper values of total amount then:

In ACCESS this should work

a query like this should give you the results you want

SELECT ID
FROM [IN]
WHERE TAmount <> (
(Select sum( Price )
FROM [Item]
Where [Item].ID = [in].ID)
+
(Select sum (TAx)
FROM [Tax]
Where [Tax].ID = [in].ID)
)


For
Table IN -- Fields ID, TAmout
Table TAx -- Fields ID, TaxAmount
Table Item -- Fields ID, Price

where x is the ID of the invoice you want to validate.
The IDs returned are for invoices that do not pass the validation.

The syntax might be a little different in other DB platforms.

Note: I have only tested this using very small data sets .. so it might not be 100% effecient, please make sure you test more.

Thanks for the info. In fact, I am doing this in Oracle and i have tried the following SQL:

SELECT *
FROM inv a
WHERE total_amt <>
((Select sum( qty_num * unit_price ) FROM inv_item b Where a.inv_no = b.inv_no)
+
(Select sum (tax_amt) FROM inv_item_tax c Where a.inv_no = c.inv_no=));

It still does not work - I get a 'missing right parenthesis' error on at the + sign.

I've tried lots of combinations, but can't get around the error.

IN
SELECT *
FROM inv a
WHERE total_amt <>
((Select sum( qty_num * unit_price ) FROM inv_item b Where a.inv_no = b.inv_no)
+
(Select sum (tax_amt) FROM inv_item_tax c Where a.inv_no = c.inv_no=));

Is the very last = sign a typo?!

Yes - thats a typo.