Help with Pivot columns and data in Access VBA. Sample attached

[JJNero]

Guys I have been breaking my head for the past three days with this. I really need this to be done.
I need my columns with data pivoted and also have a title column adjacent populated with “column names” for reference.
I did achieve a bit of it but the Do loop is not working for me. You can see it in my attached sample.

This is my data table:

tblData
id	FName	LName	NumofCars	NumofBikes	NumofVans	TotalCount
1	James	Philman	2	0	1	3
2	Robery	KINSEY	1	1	2	4

This is what my results should look like:

tblResult
id	Title	ItemCount
1	NumofVans	1
1	NumofBikes	0
1	NumofCars	2
2	NumofVans	2
2	NumofBikes	1
2	NumofCars	1

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.

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

JJ

[zmbd]

JJNero:
Your database is not normalized: > Database Normalization and Table Structures.

Once you normalize your data a simple agregate query should return the expected results.


Please understand, many of us cannot or will not d/l un-requested attachments. This has absolutely nothing to do with who you are... In my case, such d/l are prohibited by my ITSec staff. For many others, it is part of "safe computing/best practices" not to d/l such files. A practical example as to why can be found here http://bytes.com/topic/access/answer...l-ms-products. this unfourtunate Member opened a file from a trusted source and is now haveing all sorts of issues!