Display a record once if data in two fields have matching record

[crisenr]

Hello eveyone . . .


i am attempting to create a VERY complicated query . . . it's sort of confusing:

I recieve data from an outside source and it is up to us to format it for our own analysis . . .
i have fields:

MethodCode..... AccNo .....PatientName.....MR..... TestCode ..... etc, etc

The fields we want to focus on is "AccNo" and "TestCode"

Now i want to filter based on this condition:

If "AccNo" AND "TestCode" BOTH have duplicate data in a record. Show that record only once. Remember. . . "AccNo and TestCode, both these fields have to have duplicate data in a record.

Example:
MethodCode..... AccNo .....PatientName.....MR..... TestCode ..... etc, etc
CAPT.....M566679.....John Blue.....123456.....CBCA
CAPT.....M566679.....John Blue.....123456.....CBCA
the example above should only display once because of the repeated AccNo and TestCode

Example:
MethodCode..... AccNo .....PatientName.....MR..... TestCode ..... etc, etc
CAPT.....M566679.....John Blue.....123456..... CBCA
CAPT.....M566679.....John Blue.....123456..... LIPID
the example above is just fine because the TestCode is not matching

How do i create a query to accomplish this? someone please help, i've had troube with this for the passed couple weeks . .. .

 

[FoFa]

SELECT MethodCode, AccNo, MAX([PatientName]) as Patient, MAX([MR]) as MRno, MAX([TestCode]) as TstCode, MAX(etc....
From MyTable
Group By MethodCode, AccNo

 

[reclusivemonkey]

Use the "Find Duplicates Query Wizard" ?