difficult SQL logic for a comparison issue

[Randy]

I do a lot of simple query items in MS Access. But I am taking my first SQL course in jan. Up until now all my SQL has been self taught by looking at examples, and reviewing code of other users. I have a need that I can not figure out.

I have a table, basically two columns
CR 123456yz
CD 123456yz
CR 123567AB
CR 123567AB
CD 123678DE
CD 123678DE
CR 234678BCD
CR 234678BCD
CD 234678BCD

so there are two columns, column 1 has a code for the type of record, and column two is the customer reference id.

in some cases (i.e. record 1 & 2) there is one CR record and one CD record. These two records need to be in the answer set

In other cases (i.e. records7, 8& 9) there are multiple CR records and one CD record. These need to be in the answer set.

for records 3 - 6 we do not need these records on in the answer set.

Also there will be examples of multiple CD records and one matching CR record, and of course instances were there will be multiple CR records, and muiltple CD records

basically we only need records to show on the report if there is at least one CD and one CR record. If there are multiple CD records with no CR record or multiple CR records with no CD record, then we do not need them in the answer set.

Any help in coding this is greatly appreciated. thanks.

 

[ajetrumpet]

something like this may be a start:

SELECT field1, field2

FROM table

WHERE dcount(

"field1", "table", 

"[field1] = '" & [field1] & "'") > 1

 

[WayneRyan]

Randy,

You can do this with a set of querys.

Query1: Combine two lists of the record types

Select Distinct CustomerReferenceID
From   YourTable
Where  RecordType = 'CR'

UNION

Select Distinct CustomerReferenceID
From   YourTable
Where  RecordType = 'CD'

Query2: Get a list of Customers with BOTH 'CR' and 'CD' recordtypes

Select CustomerReferenceID, Count(*)
From   Query1
Group By CustomerReferenceID
Having Count(*) > 1

Query3: From your original table use the list from the last step to retrieve ALL records.

Select YourTable.RecordType, YourTable.CustomerReferenceID
From   YourTable Inner Join Query2 On
         YourTable.CustomerReferenceID = Query2.CustomerReferenceID
Order by YourTable.CustomerReferenceID

hth,
Wayne