Create a query to filter out records

[jakeman]

Hey everyone - I'm a beginner level Access user and I'm having trouble writing a query. I have a very simple table called CustomerActvity with 5 attributes: CustID, CustName, MembershipType, DatePurchased, and MembershipAmt.

If a customer has purchased more than one type of membership, I'm trying to show those customers in my query for targeted emails. So basically when Count(CustID) > 1 in my table, show those Customers in my results, with all attributes returned.

Is this best handled using a sub-query, maybe?

[ranman256]

make Q1 to count the customers in the date range.

then Q2 to pull the count>1
Q2:
select CustID from Q1 where [CountOfPurchases]>1

[jakeman]

Thanks, ranman. If I were to do this all in the same query, how would it look?

[JoeM]

You would probably need to write the SQL code directly (instead of using the Query Builder), and it would look something like this:

SELECT *
FROM CustomerActivity
WHERE CustID In
(SELECT CustomerActivity.CustID
FROM CustomerActivity
GROUP BY CustomerActivity.CustID
HAVING Count(CustomerActivity.CustID)>1);

[June7]

Build another query that JOINS the aggregate query to the table. Then switch to SQLView and copy/paste the SQL of aggregate query into the second query within parens (be sure to delete the semi-colon from the inner SQL statement).

SELECT CustomerActvity.*, Q1.* FROM CustomerActvity INNER JOIN (SELECT …) AS Q1 ON Q1.CustID = CustomerActvity.CustID WHERE CountOfPurchases >1;

Filtering on the count parameter can be done in the aggregate query or the outer query.

EDIT: Just saw JoeM reply - yes, IN operator is a good alternative. Can still use copy/paste to build the final query SQL, then delete the query object no longer needed.


Do you have Customers table? Really should not have both CustID and CustName in CustomerActivity - just the CustID. Then retrieve CustName from Customers in queries that join tables.