Limit Number of Records to 5...

[ddbrook]

I have a customer number that can have multiple account numbers. The probably is that I only want 5 of them on each customer number.

Below is a query I ran to get customer numbers > 5.
Cust_no, CustCount
16188,112
205612,63
12417,49

Now I need to delete anything >= 5 on each customer number.

So it would end up like:
Cust_no,CustCount
16188,5
205612,5
12417,5

I just want to read the first 5 records and delete the rest!!!

Any suggestions would be greatly appreciated.

Thanks,
DDBROOK

 

[Jerry Stoner]

If I understand you correctly all you have to do is go to properties for the query and in Top Values put 5.

 

[Jon K]

> Now I need to delete anything >= 5 on each customer number.....
I just want to read the first 5 records and delete the rest!!! <


I think you can delete the 6th, 7th, etc. records of each Cust_no with two queries. But the following info is needed:

What is the data type of the field Cust_no? Is it a numeric field or a text field?

What is the table name?

Is there an autonumber field in the table? If yes, what is its field name?

 

[ddbrook]

It is numeric.
Table name is ActiveAccounts.
No Autonumber field.

This is a make-table query.

Thanks for your help.
ddbrook

 

[Jon K]

Make a backup copy of your table.

In table Design, add an autonumber field RecNum in the table.


Create the following query (type/paste in the SQL View of a new query). The query will rank the records of each Cust_no.

qryRankRecords:-
SELECT DCount("*", "ActiveAccounts", "RecNum <= " & a.RecNum & " and Cust_No=" & a.Cust_No) AS Rank, *
FROM ActiveAccounts AS a
ORDER BY Cust_No, RecNum;


Create a query to view the first five records of each Cust_no.

qryViewFirstFiveRecords:-
SELECT *
FROM qryRankRecords
WHERE Rank <=5;

(Run the query to view the records.)


Create a query to delete the 6th, 7th, etc. records of each Cust_no.

qryDelete6th7th_etc_records:-
DELETE *
FROM qryRankRecords
WHERE Rank >5;

(Run the query to delete the records... but why do you want to delete accounting records?)


The view and delete queries are based on the first query, which uses DCount to rank the records. DCount is an inefficient function and may take time to run, particularly if there are many records in the table or the queries are run on a slow system.

---------------------------------------------------------
If you don't need to delete records, you can use an optimized version of query qryRankRecords:-

SELECT (Select count(*) from ActiveAccounts where RecNum<=a.RecNum and Cust_No=a.Cust_No) AS Rank, *
FROM ActiveAccounts AS a
ORDER BY Cust_No, RecNum;


Hope this helps.

 

[ddbrook]

Great, I will try and let you know...
Thanks,
ddbrook

 

[ddbrook]

Worked great!!
The reason I have to delete is for the report, manager does not want to see all the records because it made the report to long!!
We do what they want...

Thanks so very much,
Ddbrook