Data Control, LLC

Finding data holes and filling them in Microsoft Access

So you are running 20 lines of production and you have 3 shifts a day. But some times you don’t run a shift due to a variety of reasons. You decide that during these missed shifts you want to record a shift that SHOULD have run and give that shift a downtime code. You want to use ‘No Demand’ as your downtime code which means that there was no reason to run on that day on that production line during that shift.
By having this ‘No Demand’ data added to your production run data, you’ll be able to more accurately track your efficiency, downtime, and more.

So how do you identify where these holes are in your data and how do you fill these holes? You’ve got 20 machines times 3 shifts a day. That’s 60 potential holes to fill.

Here’s how I approached. Note that the two currentdb.execute “…SQL Code Here…” statements (at the end of the procedure) are creating records. One in the production table and one in a tsub downtime table. Call me at 804 928 4111 with any questions if you want to get this code working for you.
Create a table of all possible production runs for a given period of time:

In code I create this table with a currentdb.execute SQL statement. I put this inside of a For/Next loop that is defined by the most recent date in the table up until today. Therefore I will only have to add the most recent days everytime I run this code.

dtmLastDate = DMax(“ProductionDate”, “tbl_Historical_Shifts”)
iOutStandingDays = Date – dtmLastDate

If iOutStandingDays = 0 Then Exit Sub

CurrentDb.Execute “delete * from tbl_Historical_Shifts”

For i = 1 To iOutStandingDays

CurrentDb.Execute “INSERT INTO tbl_Historical_Shifts ( lineid, ShiftNumber, ShiftOccured, Productiondate ) ” & _
“SELECT tlkp_Lines.lineid, tlkp_Shifts.ShiftNumber, 0 AS ShiftOccured, #” & dtmLastDate + i & “# AS Productiondate ” & _
“FROM tlkp_Lines, tlkp_Shifts”

Next

Note above that I assume the ‘ShiftOccured’ field is false. Therefore I assume that none of the shifts actually ran.

I then run an update query comparing this historical table to the actual production table:

””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””
‘fill in whether or not a production record exists
””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””
CurrentDb.Execute “UPDATE tbl_Historical_Shifts INNER JOIN tbl_Daily_Production ON (tbl_Historical_Shifts.ProductionDate = tbl_Daily_Production.productiondate) AND (tbl_Historical_Shifts.lineid = tbl_Daily_Production.lineid) AND (tbl_Historical_Shifts.ShiftNumber = tbl_Daily_Production.productionshift) SET tbl_Historical_Shifts.ShiftOccured = True”
Which looks like this in design mode:

At this point, if you reviewed the historical table for 3/14/17, you would find that a few of the shifts did not occur:

For example the production line BBHAY31 on shift 3 and the BBTUB on shift 2.
So now I create a recordset based on the above table and scroll through that recordset identifying the ‘0’ values in ShiftOccured.
””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””
‘ scroll through the shifts where no production record exists. Build a production record with no demand
””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””
Set rs = CurrentDb.OpenRecordset(“select * from qry_Missing_Shifts WHERE productiondate >=#” & dtmLastDate & “#”)

Do Until rs.EOF
‘build a production record
CurrentDb.Execute “INSERT INTO tbl_Daily_Production ( productiondate, productionshift, starttimeofrun, total_hours ) ” & _
“SELECT #” & rs!ProductionDate & “# AS productiondate, 0 AS productionshift, #12/30/1899 1:0:0# AS starttimeofrun, 8 AS total_hours FROM tbl_Daily_Production”
lngNextProductionID = DMax(“lineid”, “tbl_daily_production”)

‘build a no demand downtime record
‘CurrentDb.Execute “INSERT INTO tsub_Production_Downtime ( productionid, dtcode, explanation, hour1minutes, hour2minutes, hour3minutes, hour4minutes, hour5minutes, hour6minutes, hour7minutes, hour8minutes, totalminutes ) ” & _
“SELECT ” & lngNextProductionID & ” AS productionid, No Demand’ AS dtcode, ‘automated’ AS explanation, 60 AS hour1minutes, 60 AS hour2minutes, 60 AS hour3minutes, 60 AS hour4minutes, 60 AS hour5minutes, 60 AS hour6minutes, 60 AS hour7minutes, 60 AS hour8minutes, 480 AS totalminutes”
rs.MoveNext
Loop

Here’s the entire sub procedure. If you wanted to run this sub procedure, simply create a button on a form (or tie the sub procedure to a form’s on load event if you’d prefer) and call as such:

Private Sub cmdFillHoles_Click()

subCreateNoDemandRecords

End Sub

I then put this sub procedure (subCreateNoDemandRecords) in a global module so it may be called from anywhere in the database including this new button.

Sub subCreateNoDemandRecords()
””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””
‘ Create No Demand Records Where necessary
””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””
Dim dtmLastDate As Date
Dim i As Integer
Dim iOutStandingDays As Integer
Dim rs As Recordset
Dim lngNextProductionID As Long

””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””
‘build a list of all potential days/shifts since the last time this was done
””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””
dtmLastDate = DMax(“ProductionDate”, “tbl_Historical_Shifts”)
iOutStandingDays = Date – dtmLastDate

If iOutStandingDays = 0 Then Exit Sub

CurrentDb.Execute “delete * from tbl_Historical_Shifts”

For i = 1 To iOutStandingDays

CurrentDb.Execute “INSERT INTO tbl_Historical_Shifts ( lineid, ShiftNumber, ShiftOccured, Productiondate ) ” & _
“SELECT tlkp_Lines.lineid, tlkp_Shifts.ShiftNumber, 0 AS ShiftOccured, #” & dtmLastDate + i & “# AS Productiondate ” & _
“FROM tlkp_Lines, tlkp_Shifts”

Next
””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””
‘fill in whether or not a production record exists
””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””
CurrentDb.Execute “UPDATE tbl_Historical_Shifts INNER JOIN tbl_Daily_Production ON (tbl_Historical_Shifts.ProductionDate = tbl_Daily_Production.productiondate) AND (tbl_Historical_Shifts.lineid = tbl_Daily_Production.lineid) AND (tbl_Historical_Shifts.ShiftNumber = tbl_Daily_Production.productionshift) SET tbl_Historical_Shifts.ShiftOccured = True”

””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””
‘ scroll through the shifts where no production record exists. Build a production record with no demand
””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””””
Set rs = CurrentDb.OpenRecordset(“select * from qry_Missing_Shifts WHERE productiondate >=#” & dtmLastDate & “#”)

Do Until rs.EOF
‘build a production record
CurrentDb.Execute “INSERT INTO tbl_Daily_Production ( productiondate, productionshift, starttimeofrun, total_hours ) ” & _
“SELECT #” & rs!ProductionDate & “# AS productiondate, 0 AS productionshift, #12/30/1899 1:0:0# AS starttimeofrun, 8 AS total_hours FROM tbl_Daily_Production”
lngNextProductionID = DMax(“lineid”, “tbl_daily_production”)

‘build a no demand downtime record
‘CurrentDb.Execute “INSERT INTO tsub_Production_Downtime ( productionid, dtcode, explanation, hour1minutes, hour2minutes, hour3minutes, hour4minutes, hour5minutes, hour6minutes, hour7minutes, hour8minutes, totalminutes ) ” & _
“SELECT ” & lngNextProductionID & ” AS productionid, No Demand’ AS dtcode, ‘automated’ AS explanation, 60 AS hour1minutes, 60 AS hour2minutes, 60 AS hour3minutes, 60 AS hour4minutes, 60 AS hour5minutes, 60 AS hour6minutes, 60 AS hour7minutes, 60 AS hour8minutes, 480 AS totalminutes”
rs.MoveNext
Loop

MsgBox “Done!”

End Sub