not arriving on time by production schedules.
1) the 2132 report is the schedule by model, build date(assembled and off line date), sequence for assembly, which line assembled on
2) tblPrefix has model built and what line builds
3) tblLine is a list of assembly lines and sub assembly lines and number of days the line needs to build product
4) tblTmpDate is temp table I create of valid workdays
column one is BuildDate(day0) column 15 is Build minus 14 (day-14)
5) tblLaunch is the created table this table has 3 launch dates the first is a direct join but the next 2 are the sublines (create sql for a temp verision of table is listed below)
I tried to make one sql to pull and populate all but the sub lines and failed. I don't remember joins and didn't find access examples of multiple joins (bad code below)
Expand|Select|Wrap|Line Numbers
- strSQL = "INSERT INTO tblLaunch ( strSerialNumber, dtBuild, dtLaunch ) " & _
- "SELECT (tbl2132.SerialPrefix + '-' + tbl2132.SerialNo) As strSerialNumber, " & _
- "tbl2132.BuildDay as dtSSBuild, " & _
- "Switch(tblLine.intLaunchIncrement=1, tblTmpDate.BuildMinus1, " & _
- "tblLine.intLaunchIncrement=2, tblTmpDate.BuildMinus2, " & _
- "tblLine.intLaunchIncrement=3, tblTmpDate.BuildMinus3, " & _
- "tblLine.intLaunchIncrement=4, tblTmpDate.BuildMinus4, " & _
- "tblLine.intLaunchIncrement=5, tblTmpDate.BuildMinus5, " & _
- "tblLine.intLaunchIncrement=6, tblTmpDate.BuildMinus6, " & _
- "tblLine.intLaunchIncrement=7, tblTmpDate.BuildMinus7, " & _
- "tblLine.intLaunchIncrement=8, tblTmpDate.BuildMinus8, " & _
- "tblLine.intLaunchIncrement=9, tblTmpDate.BuildMinus9, " & _
- "tblLine.intLaunchIncrement=10, tblTmpDate.BuildMinus10, " & _
- "tblLine.intLaunchIncrement=11, tblTmpDate.BuildMinus11, " & _
- "tblLine.intLaunchIncrement=12, tblTmpDate.BuildMinus12, " & _
- "tblLine.intLaunchIncrement=13, tblTmpDate.BuildMinus13, " & _
- "tblLine.intLaunchIncrement=14, tblTmpDate.BuildMinus14, " & _
- "True, Null ) as dtLaunch, " & _ "FROM (tbl2132 INNER JOIN tblPrefix ON tbl2132.SerialPrefix = tblPrefix.strSerialPrefix ) AND " & _
- "tblPrefix INNER JOIN tblLine ON tblPrefix.intLine = tblLine.intLine, " & _
- "tbl2132 INNER JOIN tblTmpDate ON tbl2132.BuildDate = tblTmpDate.BuildDate;"
- MsgBox strSQL
- DoCmd.RunSQL (strSQL)
Expand|Select|Wrap|Line Numbers
- strSQL = "CREATE TABLE tblLaunch2 (" & _
- "strSerialNumber STRING(9), " & _
- "dtBuild DATETIME, " & _
- "dtLaunch DATETIME, " & _
- "dtSubLaunch1 DATETIME, " & _
- "dtSubLaunch2 DATETIME, " & _
- "intSeqNum INTEGER, " & _
- "strSerialPrefix STRING(3), " & _
- "strSerialNo STRING(5), " & _
- "intLine integer, " & _
- "intLaunchIncrement integer);"
- DoCmd.RunSQL (strSQL)
- strSQL = "UPDATE tblLaunch2 INNER JOIN tblPrefix ON tblLaunch2.strSerialPrefix = tblPrefix.strSerialPrefix " & _
- "SET tblLaunch2.intLine = tblPrefix.intLine"
- DoCmd.SetWarnings (False)
- DoCmd.RunSQL (strSQL)
- DoCmd.SetWarnings (True)
- strSQL = "UPDATE tblLaunch2 INNER JOIN tblLine ON tblLaunch2.intLine = tblLine.intLine " & _
- "SET tblLaunch2.intLaunchIncrement = tblLine.intLaunchIncrement"
- DoCmd.SetWarnings (False)
- DoCmd.RunSQL (strSQL)
- DoCmd.SetWarnings (True)
- For intLoopSQL = 1 To 14
- strSQL = "UPDATE tblLaunch2 INNER JOIN tblTmpDate ON tblLaunch2.dtSSBuild = tblTmpDate.BuildDate " & _
- "SET tblLaunch2.dtSSLaunch = tblTmpDate.BuildMinus" & intLoopSQL & " WHERE tblLaunch2.intLaunchIncrement = " & intLoopSQL & ";"
- DoCmd.SetWarnings (False)
- DoCmd.RunSQL (strSQL)
- DoCmd.SetWarnings (True)
- Next intLoopSQL
- strSQL = "UPDATE tblLaunch2 SET tblLaunch2.intLaunchIncrement = (SELECT SUM(tblLine.intLaunchIncrement) as intSumIncrement FROM tblLine WHERE tblLine.intLine=4 AND intLine=tblLaunch2.intLine);"
- ' DoCmd.SetWarnings (False)
- DoCmd.RunSQL (strSQL)
- ' DoCmd.SetWarnings (True)
Here is the bad update code I'm stuck. Any pointers to how to rewrite the above to a more compact sql and get the below to do what I need greatly appreciatted
Expand|Select|Wrap|Line Numbers
- strSQL = "UPDATE tblLaunch2 SET tblLaunch2.intLaunchIncrement = " & _
- "(SELECT Sum(tblLine.intLaunchIncrement) as intSumIncrement " & _
- "FROM tblLine WHERE tblLine.intLine=4 AND intLine=tblLaunch2.intLine);"