Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 656
  • Last Modified:

Transpose query results without using a crosstab

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
btl2008
Asked:
btl2008
  • 3
  • 2
  • 2
2 Solutions
 
miqrogrooveCommented:
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
 
btl2008Author Commented:

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
 
miqrogrooveCommented:
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
Jeffrey CoachmanCommented:
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
 
btl2008Author Commented:
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
 
miqrogrooveCommented:
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
 
Jeffrey CoachmanCommented:
btl2008,

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

JeffCoachman
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now