Make New Table - Use Column Header as Row Values

[accessjack]

I mirrored my code of a similar post which did not seem to work for me. thread705-1375620.

I have a table <tblPercent> which hold department, and product, and percent data across region. Here is some sample data.

Department Product North South East West
1 A 25 25 25 25
1 B 10 50 10 30
2 A 50 50 0 0

I need to convert this data using vba to another table in a different format. I know this can be done easily using pivot table in excel, but I am trying to automate this step.

My new table <tblNew> should have output which looks like this:

Department Product Region Percent
1 A North 25
1 A South 25
1 A East 25
1 A West 25
1 B North 10
1 B South 50
1 B East 10
1 B West 30
2 A North 50
2 A South 50
...

Here's my vba code now:

Function MyData()
Dim rs As DAO.Recordset
Dim x As Integer
Dim sql As String

Set rs = CurrentDb.OpenRecordset("tblPercent")

If rs.EOF And rs.BOF Then
Exit Function
End If

Do Until rs.EOF
For x = 3 To 6

sql = "INSERT INTO tblNew(Department, Product, Region, Percent)" & _
"VALUES(" & rs(0) & "," & rs(1) & ",'" & rs(x).Name & _
"','" & rs(x) & "');"
DoCmd.RunSQL sql

Next x
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
End Function

The code compile fine, but when I execute the vba it seems like something is happening, but the tblNew doesn't get created. I'm not sure if I need to reference it somehow in the code or 1st create the table. I've tried creating it but it did not work. Any suggestions on how to get this to work would be greatly appreciated.

Thanks, AccessJack

 

[PHV]

The Fields collection index start at 0 (not 1), so I suggest this:
For x = 2 To 5
sql = "INSERT INTO tblNew(Department, Product, Region, Percent)" & _
" VALUES(" & rs(0) & ",'" & rs(1) & "','" & rs(x).Name & _
"'," & rs(x) & ")"
DoCmd.RunSQL sql
Next

BTW, for an append query, the destination table should exists.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

Furthermore, Percent is a reserved word, I think:
sql = "INSERT INTO tblNew(Department, Product, Region, [Percent])" & _

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[accessjack]

Tried both suggestions, still when I run the code, I see the progress bar indicating that something is happening, but the tblNew just doesn't get populated. I created a blank table with the 4 fields above.

 

[PHV]

Why not an all SQL way ?
INSERT INTO tblNew (Department, Product, Region, [Percent])
SELECT * FROM (
SELECT Department, Product, 'North' AS Region, North AS [Percent] FROM tblPercent
UNION SELECT Department, Product, 'South' AS Region, South FROM tblPercent
UNION SELECT Department, Product, 'East' AS Region, East FROM tblPercent
UNION SELECT Department, Product, 'West' AS Region, West FROM tblPercent
) AS U

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[accessjack]

Using the immediate window, I got as my 1st value of sql = INSERT INTO tblNew(Department, Product, Region, Value) VALUES(G1004,'JJ00','North',6)

Keep in mind I change the word Percent above to Value as to avoid the reserved keyword. The sql looks good to me, but then when I step into the <DoCmd.RunSQL sql> code, the debugger give me an error, which says "Runtime error '3134' Syntax error in INSERT INTO statement".

Hopefully we can use this approach because I have simplified my question above. There really are like 30 columns I will be using in production, so I don't wont to right a union query with all 30 columns.

 

[PHV]

I'm reluctant to answer questions where the rules are changing ...

 

[accessjack]

Rules are not changing, I have 2 tables exactly as described above. If it will work for this test data, then I can change it later to work for 10,000 record production table later. All I know if the code seems to work fine, but the actual table does not populate.

 

[PHV]

You may create an UNION query with 4 SELECT instructions but you can't with 30 ...

As for the sql you posted 13 Jul 07 16:24, keep in mind that each value for a text field should be enclosed by single quotes.

 

[MichaelRed]

current versions of Ms. Access (Jet) support the "Crosstab" query which is similar to the pivot table query. using a properly constructed on of these and as a make table, the process can be done in a single step. if this is not suitable to your longer term process, it can be a two step process, with layered queries: an append based on the crosstab / piviot table.

an extreme alternative would be to write /find and modify a similar approach (at least one exists here (in Tek-Tips))




MichaelRed

 

[accessjack]

PHV,

Thanks, it seems the issue after much debugging was pretty simple. The code above needed a space in front of the VALUES keyword. Also, as you suggested, I had to change the code to "For x = 2 To 5". Lastly, by putting the [] around the reserved keyword percent, it made the code work perfectly.

To fine tune the code. I added an if then statement to check for 0 values to be excluded, and I also had to set warnings equal to false because when the code finally worked it kept asking me to append each record.

The code works perfectly now except it does run a little slow. It takes a few minutes to run.

Thanks for your help,
Access Jack

 

[PHV]

So, you had the solution 13 Jul 07 14:28.