By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,481 Members | 1,211 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,481 IT Pros & Developers. It's quick & easy.

sql and subquery syntax assistance needed

P: 1
I have not used access in years and its been longer since I wrote any SQL. That SQL was on an AS400 so I am running into a steep learning curve. I am trying to create and load a table from 4 other tables created by importing reports. the idea is to help show parts
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
  1.   strSQL = "INSERT INTO tblLaunch ( strSerialNumber, dtBuild, dtLaunch ) " & _
  2.             "SELECT (tbl2132.SerialPrefix + '-' + tbl2132.SerialNo) As strSerialNumber, " & _
  3.                         "tbl2132.BuildDay as dtSSBuild, " & _
  4.                           "Switch(tblLine.intLaunchIncrement=1, tblTmpDate.BuildMinus1, " & _
  5.                                  "tblLine.intLaunchIncrement=2, tblTmpDate.BuildMinus2, " & _
  6.                                  "tblLine.intLaunchIncrement=3, tblTmpDate.BuildMinus3, " & _
  7.                                  "tblLine.intLaunchIncrement=4, tblTmpDate.BuildMinus4, " & _
  8.                                  "tblLine.intLaunchIncrement=5, tblTmpDate.BuildMinus5, " & _
  9.                                  "tblLine.intLaunchIncrement=6, tblTmpDate.BuildMinus6, " & _
  10.                                  "tblLine.intLaunchIncrement=7, tblTmpDate.BuildMinus7, " & _
  11.                                  "tblLine.intLaunchIncrement=8, tblTmpDate.BuildMinus8, " & _
  12.                                  "tblLine.intLaunchIncrement=9, tblTmpDate.BuildMinus9, " & _
  13.                                  "tblLine.intLaunchIncrement=10, tblTmpDate.BuildMinus10, " & _
  14.                                  "tblLine.intLaunchIncrement=11, tblTmpDate.BuildMinus11, " & _
  15.                                  "tblLine.intLaunchIncrement=12, tblTmpDate.BuildMinus12, " & _
  16.                                  "tblLine.intLaunchIncrement=13, tblTmpDate.BuildMinus13, " & _
  17.                                  "tblLine.intLaunchIncrement=14, tblTmpDate.BuildMinus14,  " & _
  18.                                  "True, Null ) as dtLaunch, " & _         "FROM (tbl2132 INNER JOIN tblPrefix ON tbl2132.SerialPrefix = tblPrefix.strSerialPrefix ) AND " & _
  19.                 "tblPrefix INNER JOIN tblLine ON tblPrefix.intLine = tblLine.intLine,  " & _
  20.                 "tbl2132 INNER JOIN tblTmpDate ON tbl2132.BuildDate = tblTmpDate.BuildDate;"
  21.     MsgBox strSQL
  22.     DoCmd.RunSQL (strSQL)
so I broke it into a bunch of smaller SQL's and a temp table. What I have here creates the temp table and pulls in the the assembly line information when piece has to be built.
Expand|Select|Wrap|Line Numbers
  1.   strSQL = "CREATE TABLE tblLaunch2 (" & _
  2.                  "strSerialNumber STRING(9), " & _
  3.                  "dtBuild DATETIME, " & _
  4.                  "dtLaunch DATETIME, " & _
  5.                  "dtSubLaunch1 DATETIME, " & _
  6.                  "dtSubLaunch2 DATETIME, " & _
  7.                  "intSeqNum INTEGER, " & _
  8.                  "strSerialPrefix STRING(3), " & _
  9.                  "strSerialNo STRING(5), " & _
  10.                  "intLine integer, " & _
  11.                  "intLaunchIncrement integer);"
  12.  
  13.   DoCmd.RunSQL (strSQL)
  14.  
  15. strSQL = "UPDATE tblLaunch2 INNER JOIN tblPrefix ON tblLaunch2.strSerialPrefix = tblPrefix.strSerialPrefix " & _
  16.            "SET tblLaunch2.intLine = tblPrefix.intLine"
  17.  
  18.   DoCmd.SetWarnings (False)
  19.   DoCmd.RunSQL (strSQL)
  20.   DoCmd.SetWarnings (True)
  21.  
  22.   strSQL = "UPDATE tblLaunch2 INNER JOIN tblLine ON tblLaunch2.intLine = tblLine.intLine " & _
  23.            "SET tblLaunch2.intLaunchIncrement = tblLine.intLaunchIncrement"
  24.  
  25.   DoCmd.SetWarnings (False)
  26.   DoCmd.RunSQL (strSQL)
  27.   DoCmd.SetWarnings (True)
  28.  
  29.  
  30.  
  31.  
  32.   For intLoopSQL = 1 To 14
  33.  
  34.     strSQL = "UPDATE tblLaunch2 INNER JOIN tblTmpDate ON tblLaunch2.dtSSBuild = tblTmpDate.BuildDate " & _
  35.              "SET tblLaunch2.dtSSLaunch = tblTmpDate.BuildMinus" & intLoopSQL & " WHERE tblLaunch2.intLaunchIncrement = " & intLoopSQL & ";"
  36.  
  37.     DoCmd.SetWarnings (False)
  38.     DoCmd.RunSQL (strSQL)
  39.     DoCmd.SetWarnings (True)
  40.   Next intLoopSQL
  41.  
  42.  
  43.  
  44.   strSQL = "UPDATE tblLaunch2 SET tblLaunch2.intLaunchIncrement = (SELECT SUM(tblLine.intLaunchIncrement) as intSumIncrement FROM tblLine WHERE tblLine.intLine=4 AND intLine=tblLaunch2.intLine);"
  45.   '  DoCmd.SetWarnings (False)
  46.    DoCmd.RunSQL (strSQL)
  47.   '  DoCmd.SetWarnings (True)
What I have (above) gets me all but the sub assemblys lines Launch dates. Thats my sticking point as I need to build on loaded information. I have used the field increment to gain the assembly Launch date now I need to add a value to that increment field that is a constant in the tblLine that tells how many days they need before they send it to the regular assembly line.
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
  1.  strSQL = "UPDATE tblLaunch2 SET tblLaunch2.intLaunchIncrement = " & _
  2.          "(SELECT Sum(tblLine.intLaunchIncrement) as intSumIncrement " & _ 
  3.          "FROM tblLine WHERE tblLine.intLine=4 AND intLine=tblLaunch2.intLine);"
PS also any good sql code references sites for access that have full examples or titles of good reference books for access sql would be a heaven send. I will learn just could use a few pointers to get there quicker.
Jul 19 '10 #1
Share this Question
Share on Google+
1 Reply


NeoPa
Expert Mod 15k+
P: 31,768
I'm not sure if these are what you need, but they may help.
Subqueries in SQL
SQL JOINs
Reasons for a Query to be Non-Updatable


I know you're fairly competent yourself, but the question is a little involved. You are more likely to find interested helpers if you can break it down into smaller chunks, or possibly explain it more succinctly (not an easy task I know).

Welcome to Bytes!
Jul 19 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.