Random sampling in Queries

[AccessEd87]

Dear community,

If you could help me out with writing a very specific query I would be immensely grateful.

Problem:

My database has a client table with basic information (name, DOB, home town, current place of work, occupation etc.)

I need to run a query that will return the percentage of clients with a particular occupation (lets say, nurse, for example purposes). Here's the tricky bit. I need this broken down by month, with a random sample of 50 clients per month, and then collated by annual quarter.

The final output would ideally look like this:

Month Total Sample Size 'Nurses" within sample % Nurses
Jan 50 35 70%
Feb 50 47 94%
March 50 40 80%

I've got part of the way there. To get the random sample, I have used the Rnd() function in the query, sorting ascending by this function and then limiting the number of returns to 50. By using a parameter query for date ranges, I can then run the query 3 times, once for each month to get the overall data for the quarter.

I think I then need to tie this into a totals query to finish the job, but can't quite work out how to get it into 1 query output at the end. In an ideal world, I'm looking for something thats as close to a 1 click solution as possible.

Many thanks for your help in advance!

Ed

 

[AccessEd87]

Thinking about it, could I possible use a union query of my random sample month queries to produce the quarter summary?

 

[AccessEd87]

I have written a union query that will combine my sample of 50 for each month into a quarterly query. But, I can no longer use ORDER By Rnd() to produce a random sample. Any thoughts on where I'm going wrong?

SELECT TOP 50 Main.FirstName, Main.Surname, Main.Occupation, Main.Age, Main.ArrivalDate, Rnd([ID])
FROM Main
WHERE (((Main.ArrivalDate) Between [Provide the month 1 start date as DD/MM/YYYY] And [Provide the month 1 end date as DD/MM/YYYY]))
ORDER BY Rnd([ID]);
UNION ALL
SELECT TOP 50 Main.FirstName, Main.Surname, Main.Occupation, Main.Age, Main.ArrivalDate, Rnd([ID])
FROM Main
WHERE (((Main.ArrivalDate) Between [Provide the month 2 start date as DD/MM/YYYY] And [Provide the month 2 end date as DD/MM/YYYY]));