Access Query Problem!!!

[Swathi37]

Hi,
I have a question in Access.

CustID A# Type
------------------------------
0023 20 AB
0023 21 WI
0021 09 WI
0034 10 AB
0044 12 AB
0044 29 WI

My question is
If CustID has both AB and WI as Type I need to extract AB type custIDs, but if the custID has no AB but only WI I need to extract even this customer.

Is this possible?

The output should be
---------------------


CustID A# Type
------------------------------
0023 20 AB
0021 09 WI
0034 10 AB
0044 12 AB

How do I write the query?

Please help me out.

Thanks in advance.

 

[rac2]

A query like the following will give you one row for every CustID with a WI type. Those customers with both types will have values in both Type columns. Those with only WI will have a value in the wiTbl.Type column and NULL in abTbl.Type. Those with only AB will not be retrieved.

SELECT wiTbl.CustID,
       wiTbl.A# AS "Number_WI",
       abTbl.A# AS "Number_AB",
       wiTbl.Type AS "Type_WI",
       abTbl.Type AS "Type_AB"
       
FROM myTable wiTbl
LEFT JOIN myTbale abTbl
     ON wiTbl.CustID = abTbl.CustID
        AND abTbl.Type = 'AB'
WHERE wiTbl.Type = 'WI'

Suppose you save that query and call it HalfWayHome.

SELECT CustID,
       Number_AB AS "A#",
       Type_AB AS "Type"
FROM HalfWayHome
WHERE Type_AB = 'AB'

UNION

SELECT CustID,
       Number_WI AS "A#",
       Type_WI AS "Type"
FROM HalfWayHome
WHERE Type_AB IS NULL

So this should retrieve first those with both types, and second those with only WI type.

Or something like this should.

 

[Swathi37]

Thank you so much for your response.