Solved

Transpose query results without using a crosstab

Posted on 2008-10-28
7
646 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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

705 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now