assigning auto numbers to groups of records in a table

[DayStar]

I have a table that contains dealer information and
account numbers. I need to be able to pull 10% of the
records for each of the dealers on the table, by dealer.
For instance, Dealer 1 has 100 records, dealer 2 has 40
recrods. I need to be able to pull every tenth record for
dealer 1 (total of 10 records) and every tenth record for
dealer 2 (total of 4 records), etc. I have tried queries
that group and count and can get sums, but cannot get a
clean pull for each dealer.

Any ideas?

 

[Tom Ellison]

Dear Day:

In order to obtain "every tenth record" you must first put the rows
into a unique order. Until you order them, they are not in any order
at all, so the concept of "every tenth record" is meaningless.

To then obtain every tenth row, you must first rank the rows according
to the unique order mentioned above. If you then filter to where the
rank of a row modulo 10 is 9 (based on a 0 based ranking) you will
have every tenth row.

I do not expect you to know just how to do this, but it is a real
description of the process I propose.

Please write a query for your table that contains all the rows you
want to see in the result, and which is ordered by Dealer and whatever
else makes for the necessary unique ordering. Post that back here and
I will then construct the query you need based on that.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Rebecca Riordan]

I'm not sure how your data is set up, but I tried this with Northwind:

SELECT TOP 5 PERCENT Customers.CustomerID, Orders.OrderID
FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.CustomerID, Orders.OrderID;

And got exactly what you're looking for. (Surprisingly!)

HTH

--
Rebecca Riordan, MVP

Designing Relational Database Systems
Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step

http://www.microsoft.com/mspress

Blessed are they who can laugh at themselves,
for they shall never cease to be amused...