Compare billing statements 2

[djgolds]

Is there a way to do an Unmatched Query and use multiple fields for the comparison? What I'm trying to accomplish is to compare a line-by-line billing from a vendor with my own billing spreadsheet and I need to match on address, service date, equipment, and dollar amount to determine any discrepancies in their billing. Any help or guidance is appreciated!

 

[Remou]

You can set a relationship between each of the fields in the query window, choosing "Include all records from TableA and only those from TableB where the joined field is equal" (I think that's the wording), where TableA is the table which will show unmatched records. It is necessary to set the relationship for each of the fields, as far as I recall.

 

[Golom]

In general

Select A.*

From myBillingTable A LEFT JOIN VendorBillings B
     ON  A.address = V.address
     AND A.[Service Date] = V.[Service Date]
     AND A.Equipment = V.Equipment
     AND A.Amount = V.Amount

Where V.Address IS NULL 
   OR V.[Service Date] IS NULL
   OR V.Equipment IS NULL
   OR V.Amount IS NULL

Will give you the records in "myBillingTable" that do not match any record (for any of those four fields) in "VendorBillings".