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.
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.