Solved

Transpose query results without using a crosstab

Posted on 2008-10-28
7
650 Views
Last Modified: 2013-11-27
I have a table of parsed data which I would like to reorganize using a query or VBA. The source table is structured as such:

Stage      Organism      Lot
Day 0      Pseudomonas aeruginosa      Saline TS 80108
Day 0      Aspergillus brasiliensis      DE 80221
Day 0      Staphylococcus aureus      TSA 80407-1
Day 0      Staphylococcus aureus      Saline TS 80320
Day 0      Pseudomonas aeruginosa      TSA 80407-1
Day 0      Pseudomonas aeruginosa      DE 71120
Day 0      Escherichia coli      TSA 80407-1
Day 0      Aspergillus brasiliensis      SAB 80331
Day 0      Escherichia coli      DE 80211
Day 0      Candia albicans      SAB 80331
Day 0      Candia albicans      Saline TS 80227
Day 0      Candia albicans      DE 71120
Day 0      Escherichia coli      Saline TS 80320
Day 0      Staphylococcus aureus      DE 80221
Day 0      Aspergillus brasiliensis      Saine TS w/ Tween 80227
Day 14      Pseudomonas aeruginosa      DE 70820
Day 14      Pseudomonas aeruginosa      Saline TS 80108
Day 14      Pseudomonas aeruginosa      TSA 80422-1
Day 14      Staphylococcus aureus      DE 80221
Day 14      Staphylococcus aureus      TSA 80422-1
Day 14      Escherichia coli      TSA 80422-1
Day 14      Aspergillus brasiliensis      DE 70820
Day 14      Staphylococcus aureus      Saline TS 80320
Day 14      Escherichia coli      Saline TS 80320
Day 14      Escherichia coli      DE 80221
Day 14      Candia albicans      SAB 80331
Day 14      Candia albicans      Saline TS 80227
Day 14      Candia albicans      DE 70820
Day 14      Aspergillus brasiliensis      Saline TS w/ Tween 80 80227
Day 14      Aspergillus brasiliensis      SAB 80331
Day 28      Candia albicans      DE 70820
Day 28      Aspergillus brasiliensis      DE 70820
Day 28      Staphylococcus aureus      DE 80221
Day 28      Aspergillus brasiliensis      Saline TS w/ Tween  80227
Day 28      Aspergillus brasiliensis      SAB 80507
Day 28      Staphylococcus aureus      Saline TS 80320
Day 28      Pseudomonas aeruginosa      TSA 80505
Day 28      Pseudomonas aeruginosa      Saline TS 80108
Day 28      Pseudomonas aeruginosa      DE70820
Day 28      Candia albicans      Saline TS 80227
Day 28      Escherichia coli      Saline TS 80320
Day 28      Escherichia coli      DE 80221
Day 28      Candia albicans      SAB 80507
Day 28      Escherichia coli      TSA 80505
Day 28      Staphylococcus aureus      TSA 80505
Suspension      Escherichia coli      TSA 80407-1
Suspension      Pseudomonas aeruginosa      P aeruginosa 484772 Exp 2008-10
Suspension      Pseudomonas aeruginosa      TSA Lot: 80219
Suspension      Pseudomonas aeruginosa      Saline TS 80108
Suspension      Staphylococcus aureus      Saline TS 80320
Suspension      Staphylococcus aureus      S aureus 485012 Exp 2009-07
Suspension      Staphylococcus aureus      TSA Lot: 80219
Suspension      Escherichia coli      Saline TS 80320
Suspension      Aspergillus brasiliensis      Saline TS W/ Tween 80227
Suspension      Pseudomonas aeruginosa      TSA 80407-1
Suspension      Escherichia coli      TSA Lot 80307
Suspension      Escherichia coli      E coli 483833 Exp 2009-07
Suspension      Candia albicans      SAB 80331
Suspension      Candia albicans      Saline TS 80227
Suspension      Candia albicans      SAB Lot 80226
Suspension      Aspergillus brasiliensis      SAB 80331
Suspension      Aspergillus brasiliensis      SAB Lot 80226
Suspension      Aspergillus brasiliensis      A brasiliensis 392464 Exp2008-09
Suspension      Staphylococcus aureus      TSA 80407-1
Suspension      Candia albicans      C albicans 443201 Exp 2009-06


My goal is to reorganize this data in a manner which groups the lot information in organism columns and stage rows:

Stage      Org1      Org2      Orgn
Day 0      Lot1      Lot 1      Lot1
Day 0      Lot 2      Lot n      Lot2
Day 0      Lot 3            Lot n
Day n
Sus...

I can't use a crosstab in Access for this because using an aggregate function on the string would only give me one of the many values I need, and I'm not sure how to transpose the columns while retaining multiple rows. I could use VBA to sort through and send it to another table. Id like to avoid that but if thats the easier path Im open to that as a solution as well. I'm sure I'm having a mental margarita on this. Any thoughts?
0
Comment
Question by:btl2008
  • 3
  • 2
  • 2
7 Comments
 
LVL 11

Expert Comment

by:miqrogroove
ID: 22822687
btl2008, the stated goal is rather ambiguous.  What does "Lot1" mean?  Is it different from "Lot 1"?  What does "Lot n" mean?  What does "Day n" mean?  Why is "Day 0" listed repeatedly?
0
 

Author Comment

by:btl2008
ID: 22822893

At any given stage, an organism will have multiple lots of something associated with it. The 'n' is just used as an abbrevation for 'the rest of the series'.

The goal is to output records in a format similar to below.

Stage A. brasiliensis  C. albicans E. coli             P. aruginosa       S. aureus
Day 0 DE 80221         SAB 80331 TSA 80407-1 Saline TS 80108  TSA 80407-1
Day 0 SAB 80331       Saline TS 80227                DE 80211 TSA 80407-1 Saline TS 80320
Day 0 Saine TS w/ Tween 80227 DE 71120 Saline TS 80320 DE 71120 DE 80221


Does this help clarify the question?
0
 
LVL 11

Accepted Solution

by:
miqrogroove earned 250 total points
ID: 22823189
This is closer to the database concept of grouping, but attempting to combine grouped and ungrouped columns is a big no-no.

If what you need is a nice visualization in a semi-grouped list format, then a PivotTable might be exactly the right thing for you.

Try these steps:

Go to Forms
Click the New button
Select PivotTable Wizard in the list and your source table in the combo box
Click OK
Click Next
Add all three fields to the pivottable (Fields Chosen for Pivoting)
Click Finish
A blank PivotTable and a Field List will appear.
Click on Stage in the Field List and drag it to where it says Drop Row Fields Here in the PivotTable.
Click on Organism in the Field List and drag it to where it says Drop Column Fields Here in the PivotTable.
Click on Lot in the Field List and drag it to where it says Drop Totals or Detail Fields Here in the PivotTable.

You should get something very close to what you wanted.
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 250 total points
ID: 22828454
btl2008,

For the record, what you have asked for is not a true transposition.

In a transposition the Rows become the columns, and the columns become the rows.
In other words the data is literally rotated 90 degrees.

JeffCoachman
0
 

Author Comment

by:btl2008
ID: 22832695
I was able to acheive what I wanted by adding another column in my parse of the orginal data to include an integer for each row. That way when I use a crosstab I can get multiple rows out.

Example

Stage   Int   Organism   Lot
Day 0    1      etc             etc
Day 0    2      etc             etc

transform first of lot
stage and int as columns
pivot on organism

gives something like

stage   int   Organism1 Organism2
Day 0    1      lot1               lot1
Day 0    2      lot2               lot2

Without the Int column I would get the first of all lots for an organism and day, where I wanted a seperate row for each combination.

Thanks for the help!
0
 
LVL 11

Expert Comment

by:miqrogroove
ID: 22833393
btl2008, please be aware that we are not awarded the full number of question points when the answers are graded "B".  Given the data available in the original question we did provide you the best possible answer.  I'm glad we were able to get this working for you so please keep our efforts in mind.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22833871
btl2008,

... and you could have really given miqrogroove all the points.
My post was just an FYI.

JeffCoachman
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question