Find the proper record problem

  • Thread starter Thread starter Dawn
  • Start date Start date
D

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
 
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.
 
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
 
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
'====================================================
 
Back
Top