Combining columns into rows

[steeveepee33]

Hi. I'm trying to create a report soon, but I need to complete a query before I can get it done. There's a lot of fields in the table, but for this I can simplify it by imagining it to be only 3 columns that I need to work with.

Basically, I have order numbers for pieces that are tracked at different stages of shipment. Item 1 will be tracked for destination A, B, and C. These destinations will also show dates symbolizing arrival at that certain area. Currently this information is displayed as follows:

Col1 Col2 Col3

Order# Destination Date
1 A 03/07/08
1 B 03/08/08
1 C 03/09/08
2 A 08/24/08
2 B 08/27/08
2 C 08/29/08
3 A 02/22/09
.
.
.
........and on....

What I want to do is take that information and combine it into rows based on the order #, where one row will show all relevant information for that specifice order. One row will show relevant information for order 1, one row will show all relevant information for order 2, and so on, without combining any of the "destination" data or the "date" data. The destination data and date data will show in columns of their own. I would like to see this:
Col 1 Col2 Col3 Col4 Col5 Col6 Col7
Order# Dest 1 Date 1 Dest 2 Date 2 Dest 3 Date 3
1 A 03/07/08 B 03/08/08 C 03/09/08
2 A 08/24/08 B 08/27/08 C 08/29/08
......
........and on.......

I would not like to base this solution on any kind of static number of "destinations", because the amount of destinations will definitely vary for each order number.

I have attached a screenshot of my table as well.



Please help!

Thank you in advance!

[pvanryzin]

Bad example - let me try again

[pvanryzin]

I need an answer to this question too. Essentially I need to convert rows in a detail table into columns in a query result (well, untimately in a report.) I have tables in a many to many relationship. It is easy enough to list the details vertically, but not horizontally. Mine looks more like this

Committees
1 Fund Raising
2 Public Relations
3 Event Planning

Volunteers
1 Tom
2 Dick
3 Harry
4 Sue

Join Table
Committee Volunteers
1 2
1 3
2 1
3 1
3 2
3 4

It is simple enough to write a query that provides output in a format like Steve's, but hard to get the details on a single line.

Fund Raising Dick Harry
Public Relations Tom
Event Planning Tom Dick Sue

[steeveepee33]

Hey, I ended up being able to do this. I'll work off of my example though, and you can see if it also works for what you have.

Off of my original table, I created three queries. The first query would pull all fields, and in the criteria selector under destination, I said "A". I made the second query exactly the same, except in the criteria area, I said "B". In the third query, I did everything the same, except I said "C" in criteria.

So, I was telling the queries to only show data from the table that was relevant to that certainf Destination.

Now, I made a fourth query that queried the first 3 queries. I added a relation to all 3 queries based on the order # field. In the fields to display in the queries, I added the order # off of one of the queries, and then I added the Destination and Date from all the queries. Depending on what data you're working with you may end up with duplicates. How you handle that is up to you.

If you want me to send you more info just let me know. I hope this helps...

[pvanryzin]

This is an OK work around, but still leaves you with the problem of "static number of destinations" - and really with the additional problem of having to enumerate all possible destinations to use them as criteria. In my case, I have thousands of names that I would have to swap in as the criteria. In the previous data model for this database, the committee members were enumerated on the same row as the committee name

Committee Member1 Member2 Member3 Member4 Member...
Events Tom Dick Harry Sue Theresa

This is considered a big no-no in data modeling - forcing one to place a false limit on the number of people on a committee (the same problem with "static number of destinations" that you pointed out. Also since people were on multiple committees, they appeared on multiple lines in the table - so when Theresa decides to go by Terry, updating her name on the Events Committee doesn't take care of her entry in whatever other committees she's on. So I did the highly recommmended thing and made the two tables with a join table for a many-to-many relationship. Little did I know that it would bite me in the behind because of limitations on how to format the data. There has to be a way of doing this. For the short term, I can sort of recreate the old table with multiple members on the comittee line and just use MemberID numbers that relate to a table of members. This takes care of having to update Terry's name in multiple places. But it just stinks as a data model.

If I come up with or hear about a solution that doesn't have the "static number of X (destinations/names)" effect, I'll post back here.

[tinytree]

here is an example