Comparing total between tables with one to many relationship

[jrg24]

I am sure this is an easy thing but I cannot seem to wrap my head around it. I have two tables, Table 1 is a list of invoices that contains the total cost related to the invoice (This is the one side). Table 2 lists each individual cost line broken out (each cost is a separate record). Invoice number is the related field. What I would like to do is compare the sum of the individual cost lines in table 2 to the total cost in table 1 and return all records from table 2 that do not add up to the value on table 1.



Thanks for any help you can provide.

[aytee111]

What is the purpose for doing this? One of the rules of database design is that calculated fields are not stored on tables. This kind of check should be done when the invoice is entered to ensure that the totals match, but not kept anywhere.

Otherwise create a totals query on table 2 and sum the amount per invoice, then in query 2 compare that value to the table.

[jrg24]

Thanks. none of the mentioned fields are calculated fields. All are data from the source. My issue is that on some records, the total cost on the invoice on table 1 is not always fully accounted for in the detail in table 2 (in particular if that detail exceeds a certain number of records, those records are truncated in the source). Obviously, the best solution is to get the source correct but try as I might to get this done it isn't going to happen as I do not have direct control over that process. The purpose of the query is to show me which records do not match so that I can do manual research. I will then use another query to append the "missing" cost detail records to table 2. I assumed there was a way to do it in one query but I will try using 2 queries. Thanks for your help.

[aytee111]

You can do it in one query by using a sub-query. See here for how they work.