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