Get only account numbers listed between 3 to 10 times

  • Thread starter Thread starter scott04
  • Start date Start date
S

scott04

I have a database that lists account numbers(field name acc_num) in the
records.I would like to have a query run that gives me the details only when
the same account number is listed between 3-9 times. So if the account
number is listed 2 times i dont want it included in my results as well as if
its listed 10 times i wouldnt want it in my results. Does anyone know how i
can design my query to get the desired results? Thanks
 
Try this:

SELECT *
FROM Accounts As A1
WHERE
(SELECT COUNT(*)
FROM Accounts AS A2
WHERE A2.acc_num = A1.acc_num)
BETWEEN 3 AND 9
ORDER BY acc_num;

Ken Sheridan
Stafford, England
 
Create a totals query like this --
SELECT [acc_num], Count([acc_num]) AS CountOfacc_num
FROM YourTable
WHERE Count([acc_num]) Between 3 AND 9
GROUP BY [acc_num];

Then inner join in anothe query like this --
SELECT [YourTable].*
FROM [YourTable] INNER JOIN [Totals Query] ON [YourTable].[acc_num] =
[Totals Query].[acc_num];
 
Probably the most efficient

SELECT *
FROM [AccountsTable]
WHERE Acc_Num in
(SELECT Acc_Num
FROM [AccountsTable]
HAVING Count(*) Between 3 and 9)
ORDER BY Acc_Num

Note that this is almost the same query as the find duplicates query wizard
produces. The significant difference being that the subquery in the where
clause sets a range for the count instead of Having Count(*) > 1.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top