By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,475 Members | 1,910 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,475 IT Pros & Developers. It's quick & easy.

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

P: 1
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:
Expand|Select|Wrap|Line Numbers
  1. tblData
  2. id    FName    LName    NumofCars    NumofBikes    NumofVans    TotalCount
  3. 1    James    Philman    2    0    1    3
  4. 2    Robery    KINSEY    1    1    2    4
This is what my results should look like:
Expand|Select|Wrap|Line Numbers
  1. tblResult
  2. id    Title    ItemCount
  3. 1    NumofVans    1
  4. 1    NumofBikes    0
  5. 1    NumofCars    2
  6. 2    NumofVans    2
  7. 2    NumofBikes    1
  8. 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
Attached Files
File Type: zip MyTestResults.zip (47.2 KB, 27 views)
Nov 11 '13 #1
Share this Question
Share on Google+
1 Reply


zmbd
Expert Mod 5K+
P: 5,287
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!
Nov 11 '13 #2

Post your reply

Sign in to post your reply or Sign up for a free account.