473,320 Members | 1,766 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

sql and subquery syntax assistance needed

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
1 1702
NeoPa
32,556 Expert Mod 16PB
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

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

Similar topics

2
by: Tim Richardson | last post by:
I've been doing a lot of reading on this and my head is starting to hurt! It seems to be quite a feat to work out how much memory is actually being used by our server. I'm running W2K advanced...
9
by: heruti | last post by:
Hi all... I've been stumped by this for days. Bit of ASP code: (IIS) Set LocalConn = CreateObject("ADODB.Connection") LocalConn.CursorLocation = adUseClient LocalConn.CommandTimeout = 0...
193
by: Michael B. | last post by:
I was just thinking about this, specifically wondering if there's any features that the C specification currently lacks, and which may be included in some future standardization. Of course, I...
3
by: Kristof Nachtergaele | last post by:
Hi, I'm trying to implement Knowledge Base Article - 322090 into my code to be able to send data directly to a pos printer. This works fine under win XP, but when I try the same printer under...
6
by: iclinux | last post by:
I have to build a GUI applicaiton that could run on different OS such as windows and *nix, which GUI toolkit is better? Best Regards.
85
by: | last post by:
List, I'm looking for C links for Standard C for a website targeting professional SW engineers. Intent is to have a convenient reference to Standard C, particularly for those who come from...
2
by: petermichaux | last post by:
Hi, It seems like determining element position in a web page is a difficult task. In the position reporting source code I've looked at there are special fixes for at least some versions of...
3
MMcCarthy
by: MMcCarthy | last post by:
We have a lot of questions in our new DB2 forum but unforunately we don't currently have many experts. If anyone has the time to answer some questions over there please do. Even a little knowledge...
1
by: Shawn Northrop | last post by:
I am trying to create an interface for clients to send newsletters through mailing lists. for the past few weeks I have been exploring options though i still feel a bit lost. First I tried to...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.