query with a loop generate data then append to a temporary table

Hi there ,

I need help in Ms Access 2013 in
creating a macro.

It will query by using SQL
statement through searching
records in table(tblChanges) and tblDates(with dates pre put say 1/9, 2/9,3/9...8/9) to generate data
then append to a temporary
table(tblChanges1)

How do I go about scripting it in
Access.

Let's say today date 8/9

SQL statement that do the query:
SELECT qryRelevantDate.Date, tblChanges.Value, tblChanges.ConNo
FROM (SELECT tblDates.Date, Max(tblChanges.Date1) AS MapDate FROM tblDates INNER JOIN tblChanges ON tblDates.Date >= tblChanges.Date1 GROUP BY tblDates.Date)  AS qryRelevantDate INNER JOIN tblChanges ON qryRelevantDate.MapDate = tblChanges.Date1
ORDER BY tblChanges.Value, tblChanges.ConNo;

Date   ConNo  Value
1/9      001           A
3/9      001           B
7/9      001           C
1/9      002           A
3/9      002           B
              .
              .
              .



After compile,
1/9   001   A
2/9   001   A
3/9   001   B
4/9   001   B
5/9   001   B
6/9   001   B
7/9   001   C
8/9   001   C
1/9   002   A
2/9   002   A
3/9   002   B
4/9   002   B
5/9   002   B
6/9   002   B
7/9   002   B
8/9   002   B

Thanks so much

Following I have done so far in scripting with the error message" Run-time error'3134': Syntax error in INSERT INTO statement"


Sub QueryPopulateAll()

Dim db As DAO.Database
 Dim rs1 As DAO.Recordset
 Dim rs2 As DAO.Recordset
 Dim SQL As String

 Set db = CurrentDb()
 Set rs1 = db.OpenRecordset("tblChanges")
 Set rs2 = db.OpenRecordset("tblChanges1")
 
 DoCmd.RunSQL "Delete * from tblChanges1"

 SQL = "INSERT INTO tblChanges1(ConNo,Value, Date)"
 SQL = SQL & "SELECT qryRelevantDate.Date, tblChanges.Value, tblChanges.ConNo"
 SQL = SQL & "FROM (SELECT tblDates.Date, Max(tblChanges.Date1) AS MapDate GROUP BY tblDates.Date)  AS qryRelevantDate"
 SQL = SQL & "FROM tblDates INNER JOIN tblChanges ON tblDates.Date >= tblChanges.Date1 INNER JOIN tblChanges ON qryRelevantDate.MapDate = tblChanges.Date1"
 SQL = SQL & "ORDER BY tblChanges.Value, tblChanges.ConNo"

 DoCmd.RunSQL SQL

    Do Until rs1.EOF
    rs2.AddNew
    rs1.MoveFirst
   
    rs2!ConNo = rs1!ConNo
    rs2!Value = rs1!Value
    rs2.Update
   
    Debug.Print rs2.Fields("ConNo, Value,Date")
    rs1.MoveNext
   
Loop
rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing


End Sub


Thanks so much
Alex
power12345sgAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongCommented:
>>Following I have done so far in scripting with the error message" Run-time error'3134': Syntax error in INSERT INTO statement"
quick try...

change:

 SQL = "INSERT INTO tblChanges1(ConNo,Value, Date)"

to:

 SQL = "INSERT INTO tblChanges1 "

?
0
Ryan ChongCommented:
also try remove the loop after that statement since your statement will do the insertion of records...

...
 Do Until rs1.EOF
...
Loop
0
power12345sgAuthor Commented:
no luck but will keep trying

" DoCmd.RunSQL SQL" highlighted with the error message" Run-time
error'3134': Syntax error in INSERT INTO statement"
does not run, wondering why?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Ryan ChongCommented:
" DoCmd.RunSQL SQL" highlighted with the error message" Run-time
error'3134': Syntax error in INSERT INTO statement"

INSERT INTO statement"
or
INSERT INTO statement "
?

make sure there is space in proper places... similary, try:
SQL = "INSERT INTO tblChanges1 "
 SQL = SQL & "SELECT qryRelevantDate.Date, tblChanges.Value, tblChanges.ConNo "
 SQL = SQL & "FROM (SELECT tblDates.Date, Max(tblChanges.Date1) AS MapDate GROUP BY tblDates.Date)  AS qryRelevantDate "
 SQL = SQL & "FROM tblDates INNER JOIN tblChanges ON tblDates.Date >= tblChanges.Date1 INNER JOIN tblChanges ON qryRelevantDate.MapDate = tblChanges.Date1 "
 SQL = SQL & "ORDER BY tblChanges.Value, tblChanges.ConNo "

Open in new window

0
power12345sgAuthor Commented:
spaces in proper places were tried,
same message
error message" Run-time

error'3134': Syntax error in INSERT INTO statement"

how do I append to tblChanges1?
0
Ryan ChongCommented:
have you tried this?
SQL = "INSERT INTO tblChanges1(ConNo,Value, [Date]) "
 SQL = SQL & "SELECT qryRelevantDate.[Date], tblChanges.Value, tblChanges.ConNo "
 SQL = SQL & "FROM (SELECT tblDates.[Date], Max(tblChanges.Date1) AS MapDate GROUP BY tblDates.Date)  AS qryRelevantDate "
 SQL = SQL & "FROM tblDates INNER JOIN tblChanges ON tblDates.[Date] >= tblChanges.Date1 INNER JOIN tblChanges ON qryRelevantDate.MapDate = tblChanges.Date1 "
 SQL = SQL & "ORDER BY tblChanges.Value, tblChanges.ConNo "

Open in new window

?
0
Ryan ChongCommented:
if still doesn't work, you can backup your database and try this:
Sub QueryPopulateAll()

 Dim SQL As String
 
 DoCmd.RunSQL "Drop Table tblChanges1"

 SQL = "SELECT qryRelevantDate.[Date], tblChanges.Value, tblChanges.ConNo into tblChanges1 " & _
       "FROM (SELECT tblDates.[Date], Max(tblChanges.Date1) AS MapDate FROM tblDates INNER JOIN tblChanges ON tblDates.[Date] >= tblChanges.Date1 GROUP BY tblDates.Date)  AS qryRelevantDate " & _
       "INNER JOIN tblChanges ON qryRelevantDate.MapDate = tblChanges.Date1 " & _
       "ORDER BY tblChanges.Value, tblChanges.ConNo "

 DoCmd.RunSQL SQL

End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
power12345sgAuthor Commented:
ok I backup database...try next see how
0
Gustav BrockCIOCommented:
You loop will run forever:

    Do Until rs1.EOF
        rs2.AddNew
        rs1.MoveFirst  ' Back to top.

/gustav
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.