VBA Code to loop an SQL statement

[robertsa123]

Hi all. Was wondering if any of you could help me with some MS Access VBA code containing an SQL statement.

I am trying to copy rows of data from an import table which is in a short-fat structure, and paste them into my new Master table which is in a long thin structure. In my import table, I have weekly data going across the database and I am trying to automate an append query to add the weekly data to my master table, one week underneath the other.
For now I have been manually creating append queries (first 5 weeks) but would like an automatic way (hopefully through VBA code) to add the other weeks of the year.

Here is my SQL view of my append queries I have been using so far:

INSERT INTO [Master Data] ( [Group], Parameter, [Actual OR Target], Units, [Value], [Year-Week], Area, Site )
SELECT [2009 Import Table].[Group], [2009 Import Table].[Parameter], [2009 Import Table].[Actual OR Target], [2009 Import Table].[Units], [2009 Import Table].[2009-Wk05] AS [Value], "2009-Wk05" AS [Year-Week], "Sinter Plant" AS Area, "Port Talbot" AS Site
FROM [2009 Import Table];



If any of you have the time or know of how to add 2009-Wk06 all the way to 2009-Wk06, I'd be very grateful.

Thanks guys!

Adam

[robertsa123]

"all the way to 2009-Wk06" , sorry guys, i mean't 2009-Wk52

[robertsa123]

I have inserted 2 screenshots to make it easier to understand for you.
The top screenshot in the file shows the import table, the second screenshot shows the new master table after the append query has been run.

[BrianMH]

I believe this is what you want. It cycles the sql from week 1 to 52. Make a backup before you test it!

Sub asdf()
Dim i As Integer
Dim sSQl As String
For i = 1 To 52
sSQl = "INSERT INTO [Master Data] ( [Group], Parameter, [Actual OR Target], " & _
"Units, [Value], [Year-Week], Area, Site ) SELECT [2009 Import Table].[Group], " & _
"[2009 Import Table].[Parameter], [2009 Import Table].[Actual OR Target], " & _
"[2009 Import Table].[Units], [2009 Import Table].[2009-Wk" & Format(i, "00") & "] AS [Value], " & _
Chr(32) & "2009-Wk" & Format(i, "00") & Chr(32) & " AS [Year-Week], " & Chr(32) & "Sinter Plant" & Chr(32) & _
"AS Area, " & Chr(32) & "Port Talbot" & Chr(32) & "AS Site FROM [2009 Import Table];"
DoCmd.RunSQL sSQl
Next i
End Sub

[robertsa123]

Brian,

Thank you very much for your help, I appreciate you giving up your time.
I tried pasting the code into a new module and received an error message when i tried to run the macro.

I've attached the screenshot for you to see. Could there be an error in the code?

Many thanks,

Adam :)

[BrianMH]

Try changing "Sinter Plant" to "Sinter Plant "