Define record return count 1

[dbero]

I have a table with 500 records in it. One column has a system indicator, A, B, or C. I would like to return 10 A's, 5 B's and 55 C's. I want a random sample from this 500 records distribut ed across the 3 systems. Is it possible to do this? If so, can you help?

Thank you!

 

[dhookom]

I think you could use the Rnd() function in a SELECT TOP/Union query. This is a sample using tables from the Northwind database

SELECT TOP 20 Rnd([Order Details].[OrderID]+[ProductID]) AS Expr1, Orders.OrderDate, [Order Details].OrderID, [Order Details].ProductID
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
WHERE ((([Order Details].ProductID)=1))
ORDER BY 1
UNION ALL
SELECT TOP 5 Rnd([Order Details].[OrderID]+[ProductID]) AS Expr1, Orders.OrderDate, [Order Details].OrderID, [Order Details].ProductID
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
WHERE ((([Order Details].ProductID)=2))
ORDER BY 1
UNION ALL
SELECT TOP 10 Rnd([Order Details].[OrderID]+[ProductID]) AS Expr1, Orders.OrderDate, [Order Details].OrderID, [Order Details].ProductID
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
WHERE ((([Order Details].ProductID)=3))
ORDER BY 1;

Duane
Hook'D on Access
MS Access MVP

 

[dbero]

Thank you much. Never heard of top 20 rnd before. I was afraid that i would lose my ability to edit with a union query. Guess I will give it a try!

 

[dhookom]

Yes, you lose your ability to edit. I didn't see anything in your original post regarding edits ;-).

Duane
Hook'D on Access
MS Access MVP