Random selection - multiple criteria

C

Craig

I never would have made it as far as I have without answers that I've found
in this discussion group, so I will thank you in advance for helping me with
my sticking point.

I have a query that I am using to generate a random selection of customer
records for use in an internal audit. The query that it is drawing from is
designed to select the records within the audit period and includes a
variable for the supervisor that is responsible for the associated customer
record. The query that I have so far pulls a 10% random sample of the total
records from the audit period. Where I am stuck is that I need a 10% random
sample PER SUPERVISOR for the period. The sql that I have so far is as
follows:

SELECT TOP 10 PERCENT [qryAnnual_Audit-SUP_CUSTOMERS-UNIQUE].[Num],
[qryAnnual_Audit-SUP_CUSTOMERS-UNIQUE].[Full Name],
[qryAnnual_Audit-SUP_CUSTOMERS-UNIQUE].[SumOfAmount],
[qryAnnual_Audit-SUP_CUSTOMERS-UNIQUE].[Assigned Rep]
FROM [qryAnnual_Audit-SUP_CUSTOMERS-UNIQUE]
ORDER BY Rnd([Num]) DESC;

Any suggestions for getting to the last step will be tremendously appreciated.
 
K

KARL DEWEY

I do not know if this is random enough for you.
First create a query to count audits -- qryAnnual_Audit_Count
SELECT [qryAnnual_Audit-SUP_CUSTOMERS-UNIQUE].[Assigned Rep],
Count([qryAnnual_Audit-SUP_CUSTOMERS-UNIQUE].[Assigned Rep]) AS
[CountOfAssigned Rep]
FROM [qryAnnual_Audit-SUP_CUSTOMERS-UNIQUE]
GROUP BY [qryAnnual_Audit-SUP_CUSTOMERS-UNIQUE].[Assigned Rep];

Then the next query --
SELECT Q.[Full Name], Q.SumOfAmount, Q.[Assigned Rep], Q.Num, (SELECT
COUNT(*) FROM [qryAnnual_Audit-SUP_CUSTOMERS-UNIQUE] Q1
WHERE Q1.[Assigned Rep] = Q.[Assigned Rep]
AND Q1.SumOfAmount <= Q.SumOfAmount) AS Rank
FROM [qryAnnual_Audit-SUP_CUSTOMERS-UNIQUE] AS Q INNER JOIN
qryAnnual_Audit_Count ON Q.[Assigned Rep] = qryAnnual_Audit_Count.[Assigned
Rep]
WHERE ((((SELECT COUNT(*) FROM [qryAnnual_Audit-SUP_CUSTOMERS-UNIQUE] Q1
WHERE Q1.[Assigned Rep] = Q.[Assigned Rep]
AND Q1.SumOfAmount <= Q.SumOfAmount))<=Round([CountOfAssigned
Rep]*0.9)))
ORDER BY Q.[Assigned Rep];
 
C

Craig

Karl,

Thanks for the response. I'm not sure that I understand how to use this
ranking though. Some numbers in the ranking appear multiple times. Also,
how would I use this to select the records for the audit? Ultimately, I need
a 10% selection of records for each Assigned Rep, and the results will be
used in a report that I generate.

Thanks for your help.

Craig

KARL DEWEY said:
I do not know if this is random enough for you.
First create a query to count audits -- qryAnnual_Audit_Count
SELECT [qryAnnual_Audit-SUP_CUSTOMERS-UNIQUE].[Assigned Rep],
Count([qryAnnual_Audit-SUP_CUSTOMERS-UNIQUE].[Assigned Rep]) AS
[CountOfAssigned Rep]
FROM [qryAnnual_Audit-SUP_CUSTOMERS-UNIQUE]
GROUP BY [qryAnnual_Audit-SUP_CUSTOMERS-UNIQUE].[Assigned Rep];

Then the next query --
SELECT Q.[Full Name], Q.SumOfAmount, Q.[Assigned Rep], Q.Num, (SELECT
COUNT(*) FROM [qryAnnual_Audit-SUP_CUSTOMERS-UNIQUE] Q1
WHERE Q1.[Assigned Rep] = Q.[Assigned Rep]
AND Q1.SumOfAmount <= Q.SumOfAmount) AS Rank
FROM [qryAnnual_Audit-SUP_CUSTOMERS-UNIQUE] AS Q INNER JOIN
qryAnnual_Audit_Count ON Q.[Assigned Rep] = qryAnnual_Audit_Count.[Assigned
Rep]
WHERE ((((SELECT COUNT(*) FROM [qryAnnual_Audit-SUP_CUSTOMERS-UNIQUE] Q1
WHERE Q1.[Assigned Rep] = Q.[Assigned Rep]
AND Q1.SumOfAmount <= Q.SumOfAmount))<=Round([CountOfAssigned
Rep]*0.9)))
ORDER BY Q.[Assigned Rep];

--
KARL DEWEY
Build a little - Test a little


Craig said:
I never would have made it as far as I have without answers that I've found
in this discussion group, so I will thank you in advance for helping me with
my sticking point.

I have a query that I am using to generate a random selection of customer
records for use in an internal audit. The query that it is drawing from is
designed to select the records within the audit period and includes a
variable for the supervisor that is responsible for the associated customer
record. The query that I have so far pulls a 10% random sample of the total
records from the audit period. Where I am stuck is that I need a 10% random
sample PER SUPERVISOR for the period. The sql that I have so far is as
follows:

SELECT TOP 10 PERCENT [qryAnnual_Audit-SUP_CUSTOMERS-UNIQUE].[Num],
[qryAnnual_Audit-SUP_CUSTOMERS-UNIQUE].[Full Name],
[qryAnnual_Audit-SUP_CUSTOMERS-UNIQUE].[SumOfAmount],
[qryAnnual_Audit-SUP_CUSTOMERS-UNIQUE].[Assigned Rep]
FROM [qryAnnual_Audit-SUP_CUSTOMERS-UNIQUE]
ORDER BY Rnd([Num]) DESC;

Any suggestions for getting to the last step will be tremendously appreciated.
Click to expand...
Click to expand...
 
K

KARL DEWEY

Some numbers in the ranking appear multiple times.
The query is ranking in a group. The group being Assigned Rep.
I made a mistake in that the multiplier needs to be 0.1 instead of 0.9 for
the 10 percent.
To select your quanity of records Rank mest be <=Round([CountOfAssigned
Rep]*0.1)

--
KARL DEWEY
Build a little - Test a little


Craig said:
Karl,

Thanks for the response. I'm not sure that I understand how to use this
ranking though. Some numbers in the ranking appear multiple times. Also,
how would I use this to select the records for the audit? Ultimately, I need
a 10% selection of records for each Assigned Rep, and the results will be
used in a report that I generate.

Thanks for your help.

Craig

KARL DEWEY said:
I do not know if this is random enough for you.
First create a query to count audits -- qryAnnual_Audit_Count
SELECT [qryAnnual_Audit-SUP_CUSTOMERS-UNIQUE].[Assigned Rep],
Count([qryAnnual_Audit-SUP_CUSTOMERS-UNIQUE].[Assigned Rep]) AS
[CountOfAssigned Rep]
FROM [qryAnnual_Audit-SUP_CUSTOMERS-UNIQUE]
GROUP BY [qryAnnual_Audit-SUP_CUSTOMERS-UNIQUE].[Assigned Rep];

Then the next query --
SELECT Q.[Full Name], Q.SumOfAmount, Q.[Assigned Rep], Q.Num, (SELECT
COUNT(*) FROM [qryAnnual_Audit-SUP_CUSTOMERS-UNIQUE] Q1
WHERE Q1.[Assigned Rep] = Q.[Assigned Rep]
AND Q1.SumOfAmount <= Q.SumOfAmount) AS Rank
FROM [qryAnnual_Audit-SUP_CUSTOMERS-UNIQUE] AS Q INNER JOIN
qryAnnual_Audit_Count ON Q.[Assigned Rep] = qryAnnual_Audit_Count.[Assigned
Rep]
WHERE ((((SELECT COUNT(*) FROM [qryAnnual_Audit-SUP_CUSTOMERS-UNIQUE] Q1
WHERE Q1.[Assigned Rep] = Q.[Assigned Rep]
AND Q1.SumOfAmount <= Q.SumOfAmount))<=Round([CountOfAssigned
Rep]*0.9)))
ORDER BY Q.[Assigned Rep];

--
KARL DEWEY
Build a little - Test a little


Craig said:
I never would have made it as far as I have without answers that I've found
in this discussion group, so I will thank you in advance for helping me with
my sticking point.

I have a query that I am using to generate a random selection of customer
records for use in an internal audit. The query that it is drawing from is
designed to select the records within the audit period and includes a
variable for the supervisor that is responsible for the associated customer
record. The query that I have so far pulls a 10% random sample of the total
records from the audit period. Where I am stuck is that I need a 10% random
sample PER SUPERVISOR for the period. The sql that I have so far is as
follows:

SELECT TOP 10 PERCENT [qryAnnual_Audit-SUP_CUSTOMERS-UNIQUE].[Num],
[qryAnnual_Audit-SUP_CUSTOMERS-UNIQUE].[Full Name],
[qryAnnual_Audit-SUP_CUSTOMERS-UNIQUE].[SumOfAmount],
[qryAnnual_Audit-SUP_CUSTOMERS-UNIQUE].[Assigned Rep]
FROM [qryAnnual_Audit-SUP_CUSTOMERS-UNIQUE]
ORDER BY Rnd([Num]) DESC;

Any suggestions for getting to the last step will be tremendously appreciated.
Click to expand...
Click to expand...
Click to expand...
 
C

Craig

That has just about got me there. It is returning an interesting result
though. In qryAnnual_Audit_Count, there are records returned for 2 Assigned
Reps. The first one has 14 records, and the second has 6 records. The final
random selection query is returning 1 hit for the Assigned Rep with 6
records, and 0 hits for the Assigned Rep with 14 records. When I increase
the % from .1 to .11, it still returns 1 hit for the Assigned Rep with 6
records, but there are 2 hits for the Assigned Rep with 14 records (which
makes sense b/c 11% of 14 is 1.54, so it rounds up). To leave it at 11% is
fine for now, because the numbers are small enough that it won't add a
tremendous amount of work to the audit. As the db gets more and more
populated though, I'd prefer to stick to the 10% threshold if there is a way
to make it display correctly.

Again, I greatly appreciate your time and expertise.

KARL DEWEY said:
The query is ranking in a group. The group being Assigned Rep.
I made a mistake in that the multiplier needs to be 0.1 instead of 0.9 for
the 10 percent.
To select your quanity of records Rank mest be <=Round([CountOfAssigned
Rep]*0.1)

--
KARL DEWEY
Build a little - Test a little


Craig said:
Karl,

Thanks for the response. I'm not sure that I understand how to use this
ranking though. Some numbers in the ranking appear multiple times. Also,
how would I use this to select the records for the audit? Ultimately, I need
a 10% selection of records for each Assigned Rep, and the results will be
used in a report that I generate.

Thanks for your help.

Craig

KARL DEWEY said:
I do not know if this is random enough for you.
First create a query to count audits -- qryAnnual_Audit_Count
SELECT [qryAnnual_Audit-SUP_CUSTOMERS-UNIQUE].[Assigned Rep],
Count([qryAnnual_Audit-SUP_CUSTOMERS-UNIQUE].[Assigned Rep]) AS
[CountOfAssigned Rep]
FROM [qryAnnual_Audit-SUP_CUSTOMERS-UNIQUE]
GROUP BY [qryAnnual_Audit-SUP_CUSTOMERS-UNIQUE].[Assigned Rep];

Then the next query --
SELECT Q.[Full Name], Q.SumOfAmount, Q.[Assigned Rep], Q.Num, (SELECT
COUNT(*) FROM [qryAnnual_Audit-SUP_CUSTOMERS-UNIQUE] Q1
WHERE Q1.[Assigned Rep] = Q.[Assigned Rep]
AND Q1.SumOfAmount <= Q.SumOfAmount) AS Rank
FROM [qryAnnual_Audit-SUP_CUSTOMERS-UNIQUE] AS Q INNER JOIN
qryAnnual_Audit_Count ON Q.[Assigned Rep] = qryAnnual_Audit_Count.[Assigned
Rep]
WHERE ((((SELECT COUNT(*) FROM [qryAnnual_Audit-SUP_CUSTOMERS-UNIQUE] Q1
WHERE Q1.[Assigned Rep] = Q.[Assigned Rep]
AND Q1.SumOfAmount <= Q.SumOfAmount))<=Round([CountOfAssigned
Rep]*0.9)))
ORDER BY Q.[Assigned Rep];

--
KARL DEWEY
Build a little - Test a little


:

I never would have made it as far as I have without answers that I've found
in this discussion group, so I will thank you in advance for helping me with
my sticking point.

I have a query that I am using to generate a random selection of customer
records for use in an internal audit. The query that it is drawing from is
designed to select the records within the audit period and includes a
variable for the supervisor that is responsible for the associated customer
record. The query that I have so far pulls a 10% random sample of the total
records from the audit period. Where I am stuck is that I need a 10% random
sample PER SUPERVISOR for the period. The sql that I have so far is as
follows:

SELECT TOP 10 PERCENT [qryAnnual_Audit-SUP_CUSTOMERS-UNIQUE].[Num],
[qryAnnual_Audit-SUP_CUSTOMERS-UNIQUE].[Full Name],
[qryAnnual_Audit-SUP_CUSTOMERS-UNIQUE].[SumOfAmount],
[qryAnnual_Audit-SUP_CUSTOMERS-UNIQUE].[Assigned Rep]
FROM [qryAnnual_Audit-SUP_CUSTOMERS-UNIQUE]
ORDER BY Rnd([Num]) DESC;

Any suggestions for getting to the last step will be tremendously appreciated.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Real 10 random records 7
Random Selection by User 4
Returning a random selection 2
Random Selection of Records 4
Random Selection 1
What do I need? 5
Random operator 2
Random selection of records based on specifc value 5

Top