Web Development

Ask something
Discussion Locked

Access97 question

0 helpful
8 replies

by chainsawz

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I hope someone has an easy solution to my problem. I have an Access97 database set up to pull a random 5% of records from a table that is refreshed daily. I have a table called Random_claims_audit with 3 fields: claim number, examiner initials (different initials each day), and last date updated. Currently, I have this query set up to pull the random 5% sample:

SELECT TOP 5 PERCENT Random_claims_audit.[Claim Number], Random_claims_audit.[Examiner Initials], Random_claims_audit.[Last DateUpdated], Rnd([claim number]) AS RandomEx
FROM Random_claims_audit
ORDER BY Rnd([claim number]);

This works great, but only pulls 5% from the whole table. I need to pull a random 5% for each distinct examiner [examiner initials], and I'm just not sure how to go about this.

Thanks!

8 replies

by stillatit

add

WHERE Random_claims_audit.[Examiner Initials] =

and run the query for each set of initials. The initials must be in quotes.

Good luck

by sl-campbell

Build a DISTINCT OR GROUP BY recordset to get each exaiminer's initials and then loop thru each examiner, and with-in this loop poll the examiner by creating a 2nd recordset using a your Top 5% SELECT Statement, adding a WHERE clause which resticts the examiner's initials to the initials of the current record in the first recordset.

Create a recordset, rsExaminers, using the following:

"SELECT DISTINCT Random_claims_audit.[Examiner Initials] FROM Random_claims_audit"

Loop thru the records:
rsExaminers.MoveFirst
Do Until rsExaminers.EOF

'Create the 2nd recordset, 'rsExaminerRecords
"SELECT TOP 5 PERCENT Random_claims_audit.[Claim Number], Random_claims_audit.[Examiner Initials], Random_claims_audit.[Last Date Updated], Rnd([claim number]) AS RandomEx
FROM Random_claims_audit WHERE Random_claims_audit.[Examiner Initials] = '" & rsExaminers("Examiner Initials") & "' ORDER BY Rnd([claim number]);

'Do something here with the data, (add it to an array, collect it all in a temporary table, or just show the results for the current examiner on a form)

Loop

by sl-campbell

By the way, it may be better to change the ORDER BY from:

ORDER BY Rnd([claim number]);

to:

ORDER BY Random_claims_audit.[claim number];

removing the Rnd here.

by sl-campbell

It really depends on how and where the data is to be used. Is it going to be used in more than one form or report? Is showing it in one form all you need? Do you only want to show the records for one examiner at a time but be able to loop through each or do you want to show all records, that are a 5% random pick for each examiner, at the same time in, say, a grid?
Do you want to use bound or unbound controls?

For a fast test I would create 2 form level recordset variables, one for each recordset I mentioned above, 3 subs, the first one initiates rsExaminers and the second one initiates the rsExaminerRecords, the third one gets the data from the recordset and updates the textboxes and labels on the form.
When the form is opened, the first sub gets called.
And when you move to a new record, the second sub gets called

Of course, best would be to have another table which lists the examiners names(each having a unique key), and their initials and making sure that each examiner, (initials) that is in the claims table has a corresponding examiner in the examiner table.

You could also do this with bound controls. Check out this: Create two forms using the form wizard/assistant. After creating, open the query builder form each form, go to SQL view and use the above sql statements with-out the where criteria.

Then, in the main/first form place a sub-form container control on the form and select the second form as the source, and link both on the initials field.

by chainsawz

Thanks for your quick responses! The first answer is correct, but I would like to automate using the second answer by sl-campbell@gmx.de -- I'm a FoxPro user learning Access so I'm not exactly sure how your process works. Can I write the sql and loop in a module? Any specifics would be greatly appreciated, as I'm a little lost. Thanks for any help you can offer...

This discussion is closed to new replies.