Find the proper record problem

[Dawn]

Table x has two field: customerid, customer_account_type,
customer_account_type’s values = 0,1,2,3,4, one customerid may
Different customer_account_type, for example:
Customeridx 3
Customeridx 2
Customeridy 1
Customeridy 0
How to use one query to find out the records for the customer who only has
the specific customer_account_type ,for example 4 and with on other
customer_account_type.
Thanks.
Dawn

 

[jschmuck9]

Dawn, use the query builder. Add both fields from the table to the query.
Under criteria for Customer_account_type, put the number of accounts you want
returned.

 

[Michel Walsh]

SELECT customerID
FROM tableName
GROUP BY customerID
HAVING COUNT(*)=1 AND MAX(type)=wantedType

if you want the customers having ONLY the wantedType,

SELECT customerID
FROM tableName
GROUP BY customerID
HAVING COUNT(*)=1

if you want the customers having ONLY ONE type, any one.


I assume you have one record per {customer, type} and no such pair is
duplicated (which may be enforced with an index on these two fields, no dup,
at the table design level).



Vanderghast, Access MVP

 

[John Spencer]

With fairly small recordsets, you can use the following and get
acceptable performance.

SELECT customerID
FROM YourTable
WHERE CustomerId NOT IN
(SELECT CustomerID
FROM YourTable
WHERE Customer_Account_Type <> 4)


If performance for that is too slow, there are a couple of options. But
the above is probably the simplest method.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================