• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 417
  • Last Modified:

Normalize Access table on the fly by way of query


I have a table that is not normalized and I need to transpose the columns as follows:

Data Now (Before)
================
ID |    P1    |     P2   | P3 | P4 | P5 |
1  |  2102  |  2105  |  0  |  0  |  0  |
2  |  2102  |     0     |  0  |  0  |  0  |
3  |  2068  |  2100  |  0  |  0  |  0  |


Desired Format (After)
===================
ID |    P    |
1  | 2102 |
1  | 2105 |
2  | 2102 |
3  | 2068 |
3  | 2100 |

How can do this on the fly in MS Access by query for all values greater than zero (0)?

Thank you,
0
htamraz1
Asked:
htamraz1
  • 5
  • 3
1 Solution
 
htamraz1Director of TechnologyAuthor Commented:
0
 
Jeffrey CoachmanMIS LiasonCommented:
Transpose a MS Access table:
http://support.microsoft.com/kb/202176

I am sure you can adapt this to ignore the Zeros.

;-)

JeffCoachman
0
 
htamraz1Director of TechnologyAuthor Commented:
Method 1 is not an option for me.

I tried the code. It's neat, but it does not produced the result I documented in this case.

Have you tested the solution in Method2 against what I am looking for?

0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Jeffrey CoachmanMIS LiasonCommented:
Method 2 is all I ever used, as I needed this to be automated.

<Have you tested the solution in Method2 against what I am looking for?>
Meaning you have this working in your db?
...and all that is lacking is the exclusion of zeros?
What is the final output?

It might be easier to just filter/delete the zero Rows afterwards...
0
 
htamraz1Director of TechnologyAuthor Commented:
The final output from the code your referenced in the kb article transposeas the rows and columns which is not what I am trying to do in the example that I laid out at the top of my question. I am not worried about the exclusion of zeros. It not the right output according to my before and after example.
0
 
Jeffrey CoachmanMIS LiasonCommented:
This looks like it can be done with a recordset.

I'll give it a go...

Stay tuned...

JeffCoachman
0
 
htamraz1Director of TechnologyAuthor Commented:
Any thoughts on this?
0
 
rvelizCommented:
Are there a fixed number of columns?  A kind of ugly solution, but one that works is to create a query for each column and then take the UNION of all those queries.

Something like:

SELECT id, p1 p FROM yourtable WHERE p1 <> 0
UNION
SELECT id, p2 p FROM yourtable WHERE p2 <> 0
UNION
SELECT id, p3 p FROM your table WHERE p3 <> 0
.
.
.
0
 
htamraz1Director of TechnologyAuthor Commented:
Genius. This is exactly what I was looking for!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

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