Selected records write to table with field based on percentage of selected records

[bobjackson]

I've hit a complete blank on where to start on the following other than the pre-requisites so this is a big ask but any suggestions/help would be gratefully appreciated, even if just a starting point. Due to the complexity I've included a cut down sample version with tables etc.

I have a form Frm_WO_Contents which points to a query Qry_WO_Selection at the bottom of the form I have a combobox combo7 which points to Tbl_Work_orders for it’s list and also a command button Command9 that I wish to click to create new records in another table based on the selection of combo7.
However one of the new fields that I want to create in the new table is based on some statistical calculations. I have another table called Tbl_Insp_Strategy which has a no. of fields “From”, “To”, “V”, “C”, “D” there are currently only 3 records in this table and this is unlikely to change. For example record one is “From” 0, “To” 3, “V” 20, “C” 80, “D” 0
The query Qry_WO_Selection has a field called “Insp Score” for each record in Qry_WO_Selection relating to the WO_ID selected in Combo7 I want to check the records against each of the 3 records in Tbl_Insp_Strategy so for all the records in Qry_WO_Selection with WO_ID (e.g. 1) from record one of Tbl_Insp_Strategy quantify no. of records whose “Insp Score” is between the “From” and “To” values of record one of Tbl_Insp_Strategy (i.e. 0 to 3)
From these filtered records i.e. only records in Qry_WO_Selection with selected WO_ID and Insp Score between 0 and 3 randomly create new records in table Tbl_Inspections including WO_ID, PIN, SHT, and Insp Type. (WO_ID, PIN and SHT are fields in Qry_WO_Selection) Insp Type needs to be generated for all records with Insp Score between 0 and 3 randomly select 20% of records(value from record in Tbl_Insp_Strategy) and write “V” to field “Insp Type”, randomly select 80% of remainder records (value from record in Tbl_Insp_Strategy) and write “C” to field “Inst Type” and then finally select remainder records and write “D” to field “Inst Type”
Repeat the above for the other records of Tbl_Insp_Strategy (i.e. another two records)
Once all complete write “True” to Yes/No field to “Insp_Created” field for relevant record relating to WO_ID in table Tbl_Work_Orders

Regards,

Rob

 

[dhookom]

I don't get "20% of records". 20% of records from which table?

What do V, C, and D represent? It's difficult to understand your question without you providing some context.

I would have probably created the Tbl_Insp_Strategy with fields like:

[pre]TblInspStrategy
==========================
istIStID - formerly ID
istFrom - "From" is a bad name for a field since it is a reserved word
istTo
istInspType
istPercent
[/pre]
You could get the same by using a union query:

SELECT ID AS ististID, [From] AS istFrom, [To] AS istTo, "V" AS istInspType, V AS istPercent
FROM Tbl_Insp_Strategy
UNION ALL
SELECT ID, [From], [To], "C", C
FROM Tbl_Insp_Strategy
UNION ALL
SELECT ID, [From], [To], "D", D
FROM Tbl_Insp_Strategy
ORDER BY 1,2;

When dealing with random and percents of records (I have created apps for random selection of employees for drug testing) consider first writing records to a table and include a random number.
Notice this query returns the same random value for all records:

SELECT Tbl_Equipment.IS_Equip_ID, Rnd() AS RandNum
FROM Tbl_Equipment;

Adding a numeric value to the Rnd() function will return nearly random numbers.

SELECT Tbl_Equipment.IS_Equip_ID, Rnd([IS_Equip_ID]) AS RandNum
FROM Tbl_Equipment;

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[bobjackson]

Hi,

20% of records refers to the number of records in Qry_WO_Selection that have a [Insp Score] value between the "From" and "To" values of each record in Tbl_Insp_Strategy. The 20% refers to the V value of that record.

V,C and D refer to Visual, Close and Detailed. We need to carry out inspections and based on the inspection score decides the percentage that are inspected visual, close or detailed. These values are detailed in Tbl_Insp_Strategy i.e. for all records with an inspection score between 0 and 3 we would do 20% of those as a visual and 80% close, for all records with an inspection score of between 4 and 7 we would do 25% visual, 70% close and 5% detailed. The selection of records for each percentage needs to be random. The end game is to have the WO_ID, PIN and type of inspection either V, C or D in the Tbl_Inspections

I will change the fields as suggested, I could possibly create the random number when I create the work order records detailed in the previous thread.

Regards,

Rob

 

[bobjackson]

Hi,

Made some progress, copied all required records from qry to Tbl_Inspections with fields WO_ID, PIN, Insp_Score, Insp_Rnd which leaves the percentages.

So within Tbl_Inspections there is a field Insp_Score I now need to look at Tbl_Insp_Strategy record 1 and filter all the records in Tbl_Inspections that have a Insp_Score value between instFrom and InstTo. Of those filtered records field Insp_Type should be updated to read "V" for a percentage of the records equating to InstV (i.e. 20% in the same record of Tbl_Insp_Strategy), Of the remainder records in the filter field Insp_Type should be updated to read "C" for a percentage of the original records equating to InstC (i.e. 80%) any remainder records in the filter should then have field Inst_Type to be updated to read "D"

This then needs repeating for record 2 and record 3 of the Tbl_Insp_Strategy

Hope the above gives more clarity on what I'm trying to achieve. I've attached demo db for further clarity

Thanks

Rob

 

[bobjackson]

To make progress I am now thinking of writing the actual percentage figures into the code instead of taking them from another table, if I can get it to work like this then look at what needs doing to link in the table, So

I have now created another field called Insp_Cat in Qry_WO_Selection the value of this will be either 1, 2 or 3 based on Insp_Score, so I now need to filter records in Qry_WO_Selection to where Insp_Cat = 1 then write "V" to Insp_Type for 20% of records and write "C" to 80% records. Then filter records in Qry_WO_Selection to where Insp_Cat = 2 then write "V" to Insp_Type for 20% of records and write "C" to Insp_Type for 75% of records and write "D" to Insp_Type for 5% of records. Where Insp_Type is in Tbl_Inspections.

So in summary I need to select WO_ID in combo7, click command9 and I want it to duplicate all records in Qry_WO_Selection to Tbl_Inspections fields WO_ID, SHT, PIN, Insp_Cat, Insp_Rnd and Insp_Type based on calc above.

Please see updated db

Any help would be welcome, Thanks, Rob

 

[bobjackson]

Hi,

Well it looks like I've made some progress, I want to use the count function to count how many records matching combo7 and Insp_Cat = 1, then calculate 20% of the total records. then using the Update statement update the field Insp_Type to "C" limiting the number of records to 20% of total records previously calculated.

This is as far as I've got, but if I can crack this I'm almost there, not quite referring to the separate table but I'm sure with further tinkering I'll get there.

This is the code I have to date but getting a syntax error on the count line.

Private Sub Command9_Click()
Dim strSql As String
Dim Rec_Qty As Integer
Dim Rec_Perc As Integer
'Copy all records matching WO_ID in Combo7 into Tbl_Inspections
strSql = "INSERT INTO Tbl_Inspections (WO_ID, SHT, PIN, Insp_Cat, Insp_Rnd) SELECT "
strSql = strSql & "WO_ID, SHT, PIN, Insp_Cat, Insp_Rnd FROM Qry_WO_Selection WHERE [Qry_WO_Selection].[WO_ID] = " & Me.[Combo7]
Debug.Print "strSQL value: " & strSql
CurrentDb.Execute strSql
'Return record count for all records in Tbl_Inspections matching WO_ID in Combo7 and Insp_Cat =1
Rec_Qty = Count (WO_ID & Insp_Cat) Where [WO_ID]= Me.[Combo7]& [Insp_Cat]=1 From Tbl_Inspections
Rec_Per = Rec_Qty * 0.2
'Update records for "C" 20% records using Rec_Per value in limit function of Update command
strSql = "Update Tbl_Inspections"
strSql = strSql & "Set Insp_Type = 'C' WHERE WO_ID = Me.Combo7 & Insp_Cat = 1 & Limit = Rec_Per"
Debug.Print "strSQL value: " & strSql
CurrentDb.Execute strSql
End Sub

Thanks

Rob