find non-matching field in group

[ptalindstrom]

I have what is likely a pretty simple question. I am trying to find records which have a common value in one field but mismatched values in a 2nd field.

A snapshot of the table i am looking at is:

ODInvoiceID EmployerID EmployerInvoiceID
4468 55 596
4464 55 596
4448 54 596
4446 55 596
4442 55 596
4440 59 595
4438 59 595
4436 56 594
4434 56 594

All the records with a common EmployerInvoiceID should have the same EmployerID; but you can see that record #3 does not. The query should find only that record.

I figured something like a grouping with a min and max of the EmployerID not being equal - but can't get the grouping correct.

thanks for any help,
peter...

 

[dRahme]

Hi, I don't think you can do that given your example. How does one know that for example record 3 is the correct one and the others are not? There would have to be some other criteria for finding the correct EmpID.

What you could do is build a query with the empID and InvoiceID only. Select View Totals which will give you a grouping and you could add one more field for a count on EmpID.

SELECT tbl.EmpID, tbl.InvoiceID, Count(tbl.EmpID) AS CountOfEmpID
FROM tbl
GROUP BY tbl.EmpID, tbl.InvoiceID;

Hope that helps

 

[ptalindstrom]

ooops, good point.. i left out the part that i am basically just trying to find the records which have an issue - not necessary that i know exactly what the issue is.

For the example i gave - query would return ALL the records with EmplInvID = 596 ; because it has the set of records which do not have a common EmployerID.

thanks,