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

Is there a way to display multiple values of a field for a unique item horizantally?

P: 3
Hello All,

Is there a way to display multiple values of a field per unique item horizontally?

Expand|Select|Wrap|Line Numbers
  1. Current Table (as per attached):
  2. ITEM    PO DATE           PO QTY
  3. AAA    1/1/2014    10
  4. AAA    2/1/2014    10
  5. BBB    1/1/2014    10
  6. CCC    1/1/2014    10
  7. CCC    2/1/2014    10
  8. CCC    3/1/2014    10
  9. DDD    1/1/2014    10
  10. DDD    2/1/2014    10
Revised Table (as per attached):
Expand|Select|Wrap|Line Numbers
  1. ITEM PODATE1 POQTY1 PODATE2 POQTY2 PODATE3 POQTY3
  2. AAA  1/1/14  10     2/1/14  10
  3. BBB  1/1/14  10
  4. CCC  1/1/14  10     2/1/14  10     3/1/14   10
  5. DDD  1/1/14  10     2/1/14  10
-


Attached Images
File Type: png Current_Table.png (6.5 KB, 167 views)
File Type: jpg Revised_Table.jpg (35.8 KB, 160 views)
Dec 18 '13 #1

✓ answered by zmbd

Yes, called a cross-tab-query (CTQ)
Simple ones are made using the wizards, more complex ones will require a tad of SQL work, Allen Browne has a nice explanation and some step by step: Crosstab query techniques
The double header with the date and then quantity is beyond the wizard and will take an extra tweek.
How to create a crosstab query with multiple value fields
Rabbit can do these in his sleep.

Can you post what you have already tried?

Share this Question
Share on Google+
5 Replies


zmbd
Expert Mod 5K+
P: 5,397
Yes, called a cross-tab-query (CTQ)
Simple ones are made using the wizards, more complex ones will require a tad of SQL work, Allen Browne has a nice explanation and some step by step: Crosstab query techniques
The double header with the date and then quantity is beyond the wizard and will take an extra tweek.
How to create a crosstab query with multiple value fields
Rabbit can do these in his sleep.

Can you post what you have already tried?
Dec 18 '13 #2

P: 3
@zmbd
Thank you very much for the references! I haven't attempted at doing this as I'm fairly new to Access and would not even know where to begin. I know the CrossTab query would be ideal, but a few attempts at that got me no where.

Thank you again. I will go ahead and take a poke at it.

If you have the code to process the example table, that would be great.
Dec 18 '13 #3

ADezii
Expert 5K+
P: 8,623
This can be done entirely with Code via Recordset Programming, but it will probably be more complex. The only reason that I mention this is that some people are uncomfortable with Crosstab Queries, but more proficient/comfortable with Programming. I know this because I happen to be one of them! (LOL). I won't post anything unless I know that you are interested in this approach.
Dec 18 '13 #4

zmbd
Expert Mod 5K+
P: 5,397
ADezii is quite correct, the CTQ is the thorn in my side too, and there are situations where one just simply has to have VBA "flatten" the data because the CTQ just wont do it.
Dec 18 '13 #5

P: 3
@ADezii
I appreciate the head's up. As of now, I haven't touched VBA. I plan on doing so in the near future as to broaden my repertoire. I'll attempt to mess with the CrossTab approach.

Thanks again.
Dec 18 '13 #6

Post your reply

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