Random Top 10 percent of groups

M

Mushroom

I have reached the end of my VBA knowledge and desperately seek assistance.
I need to randomly select 10% of the work issued to each of multiple
contractors for QC purposes. I can’t determine how to select from multiple
groups like this.

I do have a query that can tell me how many jobs each contractor completed
during the timeframe. I also have a query which returns a random 10% of the
total jobs in the timeframe. I just can’t get 10% of each contractor all in
one set! I pasted the SQL for these below.

Here is my thought process on how to do this:
I have the timeframe set by simply adding a field to my workorder table that
is initially blank. Then I can query all of the completed workorders for the
blanks and know which jobs are newly completed. My plan was to run this
about once a month, but it shouldn’t matter using the added field. Once
this is initiated, I’d like to change the blanks according to certain
criteria.
0 = the work was completed and the record was used to calculate 10% but was
not selected for QC
1 = the job is selected for QC
2 = the QC is completed.

Updating the 0 and 2 isn’t a problem. Designating the 1 is a problem I
can’t grasp.

SQL to count jobs:
SELECT Count([Workorders].[Contractor ID]) AS [CountOfContractor ID],
[Workorders].QCcheck, [Workorders].[Contractor ID], [Contractors].[Contractor
Name]
FROM [Contractors] INNER JOIN [Workorders] ON [Contractors].[Contractor ID]
= [Workorders].[Contractor ID] GROUP BY [Workorders].[Contractor ID] HAVING
((([Workorders].QCcheck) Is Null))

SQL to select 10% of all completed jobs with QCcheck blank:
SELECT TOP 10 PERCENT [Workorders].ID, [Workorders].[Contractor ID]
FROM [Contractors] INNER JOIN [Workorders] ON [Contractors].[Contractor ID]
= [Workorders].[Contractor ID] GROUP BY [Workorders].ID,
[Workorders].[Contractor ID], [Contractors].GeneralCont,
Rnd(IsNull([WO#])*0+1), randomizer(), [Workorders].QCcheck HAVING
((([Contractors].GeneralCont)=-1) AND ((randomizer())=0) AND
(([Workorders].QCcheck) Is Null)) ORDER BY Rnd(IsNull([WONum])*0+1)

(Randomizer copied from this group. Thanks!)

My latest VBA attempt was to create a recordset to see which contractors had
work, then use the ID for that contractor as a variable for the randomizing
query. Once that is created, the workorder ID could be added to a temp table
or maybe an array and then the process loops to the next contractor in the
recordset. Once the last contractor is reached, all the id's selected would
be used to update the QCcheck field with a 1. The remaining empty QCcheck
fields would become 0’s. If I can accomplish that much, the rest of it is
easy.

I have read dozens of posts that are related but none tackle this specific
issue in detail. I probably rambled on but I wanted to give as much info as
possible. I’m not terrible with VBA but once you add recordsets and SQL and
something with this many variables, my head explodes.

Any help is appreciated.
 
K

KARL DEWEY

UNTESTED UNTESTED UNTESTED UNTESTED

SELECT [Workorders].ID, [Workorders].[Contractor ID],
Count([Workorders].[Contractor ID]) AS NumWOs, (SELECT Count(*) FROM
[Workorders] AS [XX] WHERE [XX].[Contractor ID] = [Workorders].[Contractor
ID] AND [XX].ID <= [Workorders].ID ORDER BY [XX].ID) AS Rank
FROM [Contractors] INNER JOIN [Workorders] ON [Contractors].[Contractor ID]
= [Workorders].[Contractor ID]
GROUP BY [Workorders].ID, [Workorders].[Contractor ID],
Rnd(IsNull([WO#])*0+1), randomizer(), [Workorders].QCcheck
HAVING ((([Contractors].GeneralCont)=-1) AND ((randomizer())=0) AND
(([Workorders].QCcheck) Is Null)) AND (SELECT Count(*) FROM [Workorders] AS
[XX] WHERE [XX].[Contractor ID] = [Workorders].[Contractor ID] AND [XX].ID <=
[Workorders].ID ORDER BY [XX].ID) <= Count([Workorders].[Contractor ID])\10;

--
Build a little, test a little.


Mushroom said:
I have reached the end of my VBA knowledge and desperately seek assistance.
I need to randomly select 10% of the work issued to each of multiple
contractors for QC purposes. I can’t determine how to select from multiple
groups like this.

I do have a query that can tell me how many jobs each contractor completed
during the timeframe. I also have a query which returns a random 10% of the
total jobs in the timeframe. I just can’t get 10% of each contractor all in
one set! I pasted the SQL for these below.

Here is my thought process on how to do this:
I have the timeframe set by simply adding a field to my workorder table that
is initially blank. Then I can query all of the completed workorders for the
blanks and know which jobs are newly completed. My plan was to run this
about once a month, but it shouldn’t matter using the added field. Once
this is initiated, I’d like to change the blanks according to certain
criteria.
0 = the work was completed and the record was used to calculate 10% but was
not selected for QC
1 = the job is selected for QC
2 = the QC is completed.

Updating the 0 and 2 isn’t a problem. Designating the 1 is a problem I
can’t grasp.

SQL to count jobs:
SELECT Count([Workorders].[Contractor ID]) AS [CountOfContractor ID],
[Workorders].QCcheck, [Workorders].[Contractor ID], [Contractors].[Contractor
Name]
FROM [Contractors] INNER JOIN [Workorders] ON [Contractors].[Contractor ID]
= [Workorders].[Contractor ID] GROUP BY [Workorders].[Contractor ID] HAVING
((([Workorders].QCcheck) Is Null))

SQL to select 10% of all completed jobs with QCcheck blank:
SELECT TOP 10 PERCENT [Workorders].ID, [Workorders].[Contractor ID]
FROM [Contractors] INNER JOIN [Workorders] ON [Contractors].[Contractor ID]
= [Workorders].[Contractor ID] GROUP BY [Workorders].ID,
[Workorders].[Contractor ID], [Contractors].GeneralCont,
Rnd(IsNull([WO#])*0+1), randomizer(), [Workorders].QCcheck HAVING
((([Contractors].GeneralCont)=-1) AND ((randomizer())=0) AND
(([Workorders].QCcheck) Is Null)) ORDER BY Rnd(IsNull([WONum])*0+1)

(Randomizer copied from this group. Thanks!)

My latest VBA attempt was to create a recordset to see which contractors had
work, then use the ID for that contractor as a variable for the randomizing
query. Once that is created, the workorder ID could be added to a temp table
or maybe an array and then the process loops to the next contractor in the
recordset. Once the last contractor is reached, all the id's selected would
be used to update the QCcheck field with a 1. The remaining empty QCcheck
fields would become 0’s. If I can accomplish that much, the rest of it is
easy.

I have read dozens of posts that are related but none tackle this specific
issue in detail. I probably rambled on but I wanted to give as much info as
possible. I’m not terrible with VBA but once you add recordsets and SQL and
something with this many variables, my head explodes.

Any help is appreciated.
 
A

a a r o n . k e m p f

Dude stop using a crippled language and move to SQL Server

with SQL Server you can use real correlated subqueries, you can test
and see the subqueries in design view






I have reached the end of my VBA knowledge and desperately seek assistance.  
I need to randomly select 10% of the work issued to each of multiple
contractors for QC purposes.  I can’t determine how to select from multiple
groups like this.  

I do have a query that can tell me how many jobs each contractor completed
during the timeframe.  I also have a query which returns a random 10% of the
total jobs in the timeframe.  I just can’t get 10% of each contractorall in
one set!  I pasted the SQL for these below.

Here is my thought process on how to do this:
I have the timeframe set by simply adding a field to my workorder table that
is initially blank.  Then I can query all of the completed workorders for the
blanks and know which jobs are newly completed.  My plan was to run this
about once a month, but it shouldn’t matter using the added field.   Once
this is initiated, I’d like to change the blanks according to certain
criteria.
0 = the work was completed and the record was used to calculate 10% butwas
not selected for QC
1 = the job is selected for QC
2 = the QC is completed.

Updating the 0 and 2 isn’t a problem.  Designating the 1 is a problemI
can’t grasp.

SQL to count jobs:
SELECT Count([Workorders].[Contractor ID]) AS [CountOfContractor ID],
[Workorders].QCcheck, [Workorders].[Contractor ID], [Contractors].[Contractor
Name]
FROM [Contractors] INNER JOIN [Workorders] ON [Contractors].[Contractor ID]
= [Workorders].[Contractor ID] GROUP BY  [Workorders].[Contractor ID]HAVING
((([Workorders].QCcheck) Is Null))

SQL to select 10% of all completed jobs with QCcheck blank:
SELECT TOP 10 PERCENT [Workorders].ID, [Workorders].[Contractor ID]
FROM [Contractors] INNER JOIN [Workorders] ON [Contractors].[Contractor ID]
= [Workorders].[Contractor ID] GROUP BY [Workorders].ID,
[Workorders].[Contractor ID], [Contractors].GeneralCont,
Rnd(IsNull([WO#])*0+1), randomizer(), [Workorders].QCcheck HAVING
((([Contractors].GeneralCont)=-1) AND ((randomizer())=0) AND
(([Workorders].QCcheck) Is Null)) ORDER BY Rnd(IsNull([WONum])*0+1)

(Randomizer copied from this group.  Thanks!)

My latest VBA attempt was to create a recordset to see which contractors had
work, then use the ID for that contractor as a variable for the randomizing
query.  Once that is created, the workorder ID could be added to a temptable
or maybe an array and then the process loops to the next contractor in the
recordset.  Once the last contractor is reached, all the id's selected would
be used to update the QCcheck field with a 1.  The remaining empty QCcheck
fields would become 0’s.  If I can accomplish that much, the rest of it is
easy.

I have read dozens of posts that are related but none tackle this specific
issue in detail.  I probably rambled on but I wanted to give as much info as
possible.  I’m not terrible with VBA but once you add recordsets and SQL and
something with this many variables, my head explodes.

Any help is appreciated.
 
T

Tony Toews [MVP]

N

nena

Mushroom said:
I have reached the end of my VBA knowledge and desperately seek
assistance.
I need to randomly select 10% of the work issued to each of multiple
contractors for QC purposes. I can’t determine how to select from
multiple
groups like this.

I do have a query that can tell me how many jobs each contractor completed
during the timeframe. I also have a query which returns a random 10% of
the
total jobs in the timeframe. I just can’t get 10% of each contractor all
in
one set! I pasted the SQL for these below.

Here is my thought process on how to do this:
I have the timeframe set by simply adding a field to my workorder table
that
is initially blank. Then I can query all of the completed workorders for
the
blanks and know which jobs are newly completed. My plan was to run this
about once a month, but it shouldn’t matter using the added field. Once
this is initiated, I’d like to change the blanks according to certain
criteria.
0 = the work was completed and the record was used to calculate 10% but
was
not selected for QC
1 = the job is selected for QC
2 = the QC is completed.

Updating the 0 and 2 isn’t a problem. Designating the 1 is a problem I
can’t grasp.

SQL to count jobs:
SELECT Count([Workorders].[Contractor ID]) AS [CountOfContractor ID],
[Workorders].QCcheck, [Workorders].[Contractor ID],
[Contractors].[Contractor
Name]
FROM [Contractors] INNER JOIN [Workorders] ON [Contractors].[Contractor
ID]
= [Workorders].[Contractor ID] GROUP BY [Workorders].[Contractor ID]
HAVING
((([Workorders].QCcheck) Is Null))

SQL to select 10% of all completed jobs with QCcheck blank:
SELECT TOP 10 PERCENT [Workorders].ID, [Workorders].[Contractor ID]
FROM [Contractors] INNER JOIN [Workorders] ON [Contractors].[Contractor
ID]
= [Workorders].[Contractor ID] GROUP BY [Workorders].ID,
[Workorders].[Contractor ID], [Contractors].GeneralCont,
Rnd(IsNull([WO#])*0+1), randomizer(), [Workorders].QCcheck HAVING
((([Contractors].GeneralCont)=-1) AND ((randomizer())=0) AND
(([Workorders].QCcheck) Is Null)) ORDER BY Rnd(IsNull([WONum])*0+1)

(Randomizer copied from this group. Thanks!)

My latest VBA attempt was to create a recordset to see which contractors
had
work, then use the ID for that contractor as a variable for the
randomizing
query. Once that is created, the workorder ID could be added to a temp
table
or maybe an array and then the process loops to the next contractor in the
recordset. Once the last contractor is reached, all the id's selected
would
be used to update the QCcheck field with a 1. The remaining empty QCcheck
fields would become 0’s. If I can accomplish that much, the rest of it is
easy.

I have read dozens of posts that are related but none tackle this specific
issue in detail. I probably rambled on but I wanted to give as much info
as
possible. I’m not terrible with VBA but once you add recordsets and SQL
and
something with this many variables, my head explodes.

Any help is appreciated.
 
M

mariogonçalves

nena said:
Mushroom said:
I have reached the end of my VBA knowledge and desperately seek
assistance.
I need to randomly select 10% of the work issued to each of multiple
contractors for QC purposes. I can’t determine how to select from
multiple
groups like this.

I do have a query that can tell me how many jobs each contractor
completed
during the timeframe. I also have a query which returns a random 10% of
the
total jobs in the timeframe. I just can’t get 10% of each contractor all
in
one set! I pasted the SQL for these below.

Here is my thought process on how to do this:
I have the timeframe set by simply adding a field to my workorder table
that
is initially blank. Then I can query all of the completed workorders for
the
blanks and know which jobs are newly completed. My plan was to run this
about once a month, but it shouldn’t matter using the added field. Once
this is initiated, I’d like to change the blanks according to certain
criteria.
0 = the work was completed and the record was used to calculate 10% but
was
not selected for QC
1 = the job is selected for QC
2 = the QC is completed.

Updating the 0 and 2 isn’t a problem. Designating the 1 is a problem I
can’t grasp.

SQL to count jobs:
SELECT Count([Workorders].[Contractor ID]) AS [CountOfContractor ID],
[Workorders].QCcheck, [Workorders].[Contractor ID],
[Contractors].[Contractor
Name]
FROM [Contractors] INNER JOIN [Workorders] ON [Contractors].[Contractor
ID]
= [Workorders].[Contractor ID] GROUP BY [Workorders].[Contractor ID]
HAVING
((([Workorders].QCcheck) Is Null))

SQL to select 10% of all completed jobs with QCcheck blank:
SELECT TOP 10 PERCENT [Workorders].ID, [Workorders].[Contractor ID]
FROM [Contractors] INNER JOIN [Workorders] ON [Contractors].[Contractor
ID]
= [Workorders].[Contractor ID] GROUP BY [Workorders].ID,
[Workorders].[Contractor ID], [Contractors].GeneralCont,
Rnd(IsNull([WO#])*0+1), randomizer(), [Workorders].QCcheck HAVING
((([Contractors].GeneralCont)=-1) AND ((randomizer())=0) AND
(([Workorders].QCcheck) Is Null)) ORDER BY Rnd(IsNull([WONum])*0+1)

(Randomizer copied from this group. Thanks!)

My latest VBA attempt was to create a recordset to see which contractors
had
work, then use the ID for that contractor as a variable for the
randomizing
query. Once that is created, the workorder ID could be added to a temp
table
or maybe an array and then the process loops to the next contractor in
the
recordset. Once the last contractor is reached, all the id's selected
would
be used to update the QCcheck field with a 1. The remaining empty
QCcheck
fields would become 0’s. If I can accomplish that much, the rest of it
is
easy.

I have read dozens of posts that are related but none tackle this
specific
issue in detail. I probably rambled on but I wanted to give as much info
as
possible. I’m not terrible with VBA but once you add recordsets and SQL
and
something with this many variables, my head explodes.

Any help is appreciated.
 
D

De Jager

nena said:
Mushroom said:
I have reached the end of my VBA knowledge and desperately seek
assistance.
I need to randomly select 10% of the work issued to each of multiple
contractors for QC purposes. I can’t determine how to select from
multiple
groups like this.

I do have a query that can tell me how many jobs each contractor
completed
during the timeframe. I also have a query which returns a random 10% of
the
total jobs in the timeframe. I just can’t get 10% of each contractor all
in
one set! I pasted the SQL for these below.

Here is my thought process on how to do this:
I have the timeframe set by simply adding a field to my workorder table
that
is initially blank. Then I can query all of the completed workorders for
the
blanks and know which jobs are newly completed. My plan was to run this
about once a month, but it shouldn’t matter using the added field. Once
this is initiated, I’d like to change the blanks according to certain
criteria.
0 = the work was completed and the record was used to calculate 10% but
was
not selected for QC
1 = the job is selected for QC
2 = the QC is completed.

Updating the 0 and 2 isn’t a problem. Designating the 1 is a problem I
can’t grasp.

SQL to count jobs:
SELECT Count([Workorders].[Contractor ID]) AS [CountOfContractor ID],
[Workorders].QCcheck, [Workorders].[Contractor ID],
[Contractors].[Contractor
Name]
FROM [Contractors] INNER JOIN [Workorders] ON [Contractors].[Contractor
ID]
= [Workorders].[Contractor ID] GROUP BY [Workorders].[Contractor ID]
HAVING
((([Workorders].QCcheck) Is Null))

SQL to select 10% of all completed jobs with QCcheck blank:
SELECT TOP 10 PERCENT [Workorders].ID, [Workorders].[Contractor ID]
FROM [Contractors] INNER JOIN [Workorders] ON [Contractors].[Contractor
ID]
= [Workorders].[Contractor ID] GROUP BY [Workorders].ID,
[Workorders].[Contractor ID], [Contractors].GeneralCont,
Rnd(IsNull([WO#])*0+1), randomizer(), [Workorders].QCcheck HAVING
((([Contractors].GeneralCont)=-1) AND ((randomizer())=0) AND
(([Workorders].QCcheck) Is Null)) ORDER BY Rnd(IsNull([WONum])*0+1)

(Randomizer copied from this group. Thanks!)

My latest VBA attempt was to create a recordset to see which contractors
had
work, then use the ID for that contractor as a variable for the
randomizing
query. Once that is created, the workorder ID could be added to a temp
table
or maybe an array and then the process loops to the next contractor in
the
recordset. Once the last contractor is reached, all the id's selected
would
be used to update the QCcheck field with a 1. The remaining empty
QCcheck
fields would become 0’s. If I can accomplish that much, the rest of it
is
easy.

I have read dozens of posts that are related but none tackle this
specific
issue in detail. I probably rambled on but I wanted to give as much info
as
possible. I’m not terrible with VBA but once you add recordsets and SQL
and
something with this many variables, my head explodes.

Any help is appreciated.
 

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

Top