UnPivot columns with column data and column names using Access/VBA

[JJNero]

Hello gurus,
I have been struggling for the past three days with this. I really need this to be done.
I need my columns with data un-pivoted and also have a title column adjacent populated with “column names” for data reference.
I did achieve a bit of it but the Do loop is not working for me. Below is my test.

This is de-normalized source table:
Entry form:

id	Fname	Lname	NumOfCars	NumOfBikes	NumOfVans	TotalCount
1	James	Philman	2	0	1	3
2	Robert	Kinsey	1	1	2	4

This is what my results should look like:
Result should be:

id	Title	ItemCount
1	NumOfCars	2
1	NumOfBikes	0
1	NumOfVans	1
2	NumOfCars	1
2	NumOfBikes	1
2	NumOfVans	2

Things to note:
1. In the result set some of the columns are skipped during pivot like Fname, LName, TotalCount.
2. I also need the item count to be “Numeric field”. When I change it in my table I get an error during run time.
3. The above source table is de-normalized and done this way to meet a certain business need.
4. The id repeats with every represented data column. Like in my above source have 2 (id) but result set shows 3 (id) rows. This is correct because 3 columns have the related data (NumOfCars, NumOfBikes, NumOfVans). Even a null or a default zero.
5. The bottom table shows the result from my code. Think I got the column data. Column names are problem. The column names should show to their related id and data. I my case (NumOfCars, NumOfBikes, NumOfVans) twice for each unique id.


This is what I got so far:

id	Title	itemCount
	id
	FName
	Lname
	NumOfCars
	NumOfBikes
	NumOfVans
	TotalCount
	id
	FName
	Lname
	NumOfCars
	NumOfBikes
	NumOfVans
	TotalCount
1		2
1
1		1
2		1
2		1
2		2

Code:

Private Sub Command1_Click()
Dim db As DAO.Database
Dim sqlStr1 As String
Dim rs2 As DAO.TableDef

DoCmd.SetWarnings False

Set db = Currentdb

sqlStr1 = "SELECT * FROM tblData"
Set rs1 = db.OpenRecordset(sqlStr1)

Set rs2 = db.TableDefs("tblData")

Do While Not rs1.EOF = True
For i = 3 To rs1.Fields.Count - 2

CurrentProject.Connection.Execute "INSERT Into tblResult (id,ItemCount) Values('" & (rs1!id) & "','" & (rs1(i - l)) & "') "
Next

For Each p In rs2.Fields
CurrentProject.Connection.Execute "INSERT Into tblResult (Title) Values('" & (p.Name) & "') "

Next
rs1.MoveNext
Loop


Set rs1 = Nothing
Set rs2 = Nothing

DoCmd.SetWarnings True
End Sub

I really appreciate any help I can get with this. Thanks in advance.

[June7]

That looks like a crosstab query created from an aggregate query. Why don't you just build a report on the aggregate query or even the raw table and use report Grouping & Sorting with aggregate calcs in group footer section? This will allow display of detail records as well as summary calcs.

A UNION query can normalize data.

SELECT ID, Fname, Lname, "Cars" AS Type, NumOfCars AS Quantity FROM tablename
UNION SELECT ID, Fname, Lname, "Bikes", NumOfBikes FROM tablename
UNION SELECT ID, Fname, Lname, "Vans", NumOfVans FROM tablename
UNION SELECT ID, "None", "None", "Total", TotalCount FROM tablename;