I need several records based on same cell values

[Regina Baker]

I need a query that will randomly pull 2 records from my microsoft access database for any repeating cell value.

I have to pull a random sample of audits based on employee id (2 audits per rep). So the employee Id feild will have repeating values and i need something to tell ms access to keep 2 of the records for each id and delete the rest.

Please help, i have to have this ready by the end of the month.

EggHeadCafe - Software Developer Portal of Choice
ASP.NET Process Killer Revisited
http://www.eggheadcafe.com/tutorial...23-4a372d7bc912/aspnet-process-killer-re.aspx

 

[John W. Vinson]

I need a query that will randomly pull 2 records from my microsoft access database for any repeating cell value.

Well... Access doesn't have "cells" but...

Try adapting this:

You can use the Top Values property of a query, with help
from a little VBA. Put this little function into a Module:

Public Function RndNum(vIgnore As Variant) As Double
Static bRnd As Boolean
If Not bRnd Then
'Initialize the random number generator once only
bRnd = True
Randomize
End If
RndNum = Rnd()
End Function

Then add a calculated field to your Query by typing

Shuffle: RndNum([fieldname])

in a vacant Field cell, where [fieldname] is any field in
your table - this forces Access to give a different random
number for each record.

Sort the query by Shuffle, and set its Top Values property
to the number of records you want to see.