MSaccess search inconsistent records

Question

I have a table INVOICES with columns InvoiceNAME, InvoiceSUM and an other INVOICEitems with column InvoiceNAME, ItemNAME, ItemPRICE and ItemQTY. Tables joined via InvoiceNAME fields.

I'd like to select all the rows from INVOICES table where InvoiceSUM not equals the SUM of ItemPRICE*ItemQTY related to InvoiceNAME. In other words I would like to list the inconsistens records, where the InvoiceSUM not equal to the items total.

Answer 1

One method uses a correlated subquery:

select i.*
from invoices as i
where i.invoicesum <> (select sum(ii.price * ii.quantity)
                       from INVOICEitems as ii
                       where ii.InvoiceNAME = i.InvoiceNAME
                      );