?
Solved

Access VBA, Loop Union Query

Posted on 2012-08-21
4
Medium Priority
?
1,077 Views
Last Modified: 2012-08-22
Hey Guys,

I'm am under the gun to get this finished this week and I'm stuck. Here I am attempting to loop through a table and then use the results from 1 column as the value in constructing the UNION query. My code here shows the code, except I'm show Debug.Print so that the Debug screen shows what the Final SQL code should look like. I just don't know how to construct it and actually run it. Note that the values I'm pulling from the table TBL_TIMESELECTED_TMP can change based on User Selections and is variable in Number and Value. That's why I have to be able to build this Dynamically.


************************************************************************************************************
Code:
Private Sub useRecordset()

Dim strSQL As String
Dim dbs As Database
Dim rst As Recordset
Dim ColTime As String
Dim sSQLA As String
Dim sSQLB As String
Dim sSQLC As String
Dim sSQLD As String
Set dbs = CurrentDb

sSQLA = "SELECT * INTO TBL_FORMATTED_DATASET FROM ("
sSQLC = ") AS UNIONTABLES;"

strSQL = "SELECT TBL_TIMESELECTED_TMP.[ID], "
strSQL = strSQL & "TBL_TIMESELECTED_TMP.[COLUMN_TIME], "
strSQL = strSQL & "TBL_TIMESELECTED_TMP.[FMT_TIME_SORT]"
strSQL = strSQL & "FROM TBL_TIMESELECTED_TMP;"

Set rst = dbs.OpenRecordset(strSQL)

rst.MoveLast
rst.MoveFirst

Debug.Print sSQLA

Do While Not rst.EOF

ColTime = rst.Fields(1).Value

sSQLB = "SELECT TMC_CD, LEN, DSCRPTN, RTE_SECTION_ID, SEGMENT_NBR, DIRECTION_TRAVEL, Description, ROUTE_CD," & vbNewLine & _
       "SPDLMT, SPDLMT_1, SPDLMT_2, SPDLMT_3, RCRD_DT, '" & ColTime & "'," & ColTime & " FROM TBL_FIRST_DATASET UNION"
       
rst.MoveNext
 
Debug.Print sSQLB
 
Loop

Debug.Print sSQLC
 
rst.Close
dbs.Close

***************************************************************************************************************
When I run that, here is what shows up in the Debug Screen...I just need to make it build this so I can actually run it.

SELECT * INTO TBL_FORMATTED_DATASET FROM (
SELECT TMC_CD, LEN, DSCRPTN, RTE_SECTION_ID, SEGMENT_NBR, DIRECTION_TRAVEL, Description, ROUTE_CD,
SPDLMT, SPDLMT_1, SPDLMT_2, SPDLMT_3, RCRD_DT, 'A0000_0015',A0000_0015 FROM TBL_FIRST_DATASET UNION
SELECT TMC_CD, LEN, DSCRPTN, RTE_SECTION_ID, SEGMENT_NBR, DIRECTION_TRAVEL, Description, ROUTE_CD,
SPDLMT, SPDLMT_1, SPDLMT_2, SPDLMT_3, RCRD_DT, 'A0015_0030',A0015_0030 FROM TBL_FIRST_DATASET UNION
SELECT TMC_CD, LEN, DSCRPTN, RTE_SECTION_ID, SEGMENT_NBR, DIRECTION_TRAVEL, Description, ROUTE_CD,
SPDLMT, SPDLMT_1, SPDLMT_2, SPDLMT_3, RCRD_DT, 'A0030_0045',A0030_0045 FROM TBL_FIRST_DATASET UNION
SELECT TMC_CD, LEN, DSCRPTN, RTE_SECTION_ID, SEGMENT_NBR, DIRECTION_TRAVEL, Description, ROUTE_CD,
SPDLMT, SPDLMT_1, SPDLMT_2, SPDLMT_3, RCRD_DT, 'A0045_0100',A0045_0100 FROM TBL_FIRST_DATASET UNION
) AS UNIONTABLES;

Any help is MUCH appreciated.

thank,
D
0
Comment
Question by:DwayneTharpe
  • 2
  • 2
4 Comments
 

Author Comment

by:DwayneTharpe
ID: 38318584
I fixed it!!!! No need to Union...Just LOOP through and append records Each Loop!

Private Sub useRecordset()

Dim strSQL As String
Dim dbs As Database
Dim rst As Recordset
Dim ColTime As String
Dim sSQLA As String
Dim sSQLB As String
Dim sSQLC As String
Dim sSQLD As String
Set dbs = CurrentDb

sSQLA = "SELECT * INTO TBL_FORMATTED_DATASET FROM ("
sSQLC = ") AS UNIONTABLES;"

strSQL = "SELECT TBL_TIMESELECTED_TMP.[ID], "
strSQL = strSQL & "TBL_TIMESELECTED_TMP.[COLUMN_TIME], "
strSQL = strSQL & "TBL_TIMESELECTED_TMP.[FMT_TIME_SORT]"
strSQL = strSQL & "FROM TBL_TIMESELECTED_TMP;"

Set rst = dbs.OpenRecordset(strSQL)

rst.MoveLast
rst.MoveFirst



Do While Not rst.EOF

ColTime = rst.Fields(1).Value

sSQLB = "INSERT INTO TBL_FORMATTED_DATASET (TMC_CD, LEN, DSCRPTN, RTE_SECTION_ID, SEGMENT_NBR, DIRECTION_TRAVEL, Description, ROUTE_CD," & _
        "SPDLMT, SPDLMT_1, SPDLMT_2, SPDLMT_3, RCRD_DT, REC_SPD_TIME, REC_SPD )" & _
        "SELECT TMC_CD, LEN, DSCRPTN, RTE_SECTION_ID, SEGMENT_NBR, DIRECTION_TRAVEL, Description, ROUTE_CD," & _
        "SPDLMT, SPDLMT_1, SPDLMT_2, SPDLMT_3, RCRD_DT, '" & ColTime & "' AS REC_SPD_TIME," & ColTime & " AS REC_SPD FROM TBL_FIRST_DATASET"
       
        dbs.Execute (sSQLB)
           
       
rst.MoveNext
 
Loop

rst.Close
dbs.Close

End Sub
0
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 38319053
Glad you solved it.  Couple of hints:

(1)  If your query only has one table, then you don't need the table references in the SELECT clause.  So this...

strSQL = "SELECT TBL_TIMESELECTED_TMP.[ID], "
strSQL = strSQL & "TBL_TIMESELECTED_TMP.[COLUMN_TIME], "
strSQL = strSQL & "TBL_TIMESELECTED_TMP.[FMT_TIME_SORT]"
strSQL = strSQL & "FROM TBL_TIMESELECTED_TMP;"

... can be rewritten as this ...

strSQL = "SELECT ID, COLUMN_TIME, FNT_TIME_SORT FROM TBL_TIMESELECTED_TMP;"

(2)  You can also drop the square brackets [ ]'s as long as your column name doesn't require them.  The Query Designer puts them in there, but it makes it difficult to read in Access VBA.

(3)  If you're in a mixed DAO and ADO environment, you'll want to Dim rst as DAO.Recordset or Dim rst as ADODB.Recordset, just so you don't confuse the two.

(4)  In the below line you may have left out the single tick marks around the second ColTime

'" & ColTime & "' AS REC_SPD_TIME," & ColTime & "

might need to be

'" & ColTime & "' AS REC_SPD_TIME,'" & ColTime & "'

Either way, glad it's working.
Jim
0
 

Author Closing Comment

by:DwayneTharpe
ID: 38319490
Thanks Jim...it's been a long time since I've developed in Access...been a Web Developer in ColdFusion for the past 9 years. Anyway, I appreciate you taking the time to help out.

Peace, D
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38319920
Thanks for the grade.  Good luck with your project.  -Jim
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
I came across an unsolved Outlook issue and here is my solution.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question