• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 405
  • 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
 
htamraz1Author Commented:
0
 
Jeffrey CoachmanCommented:
Transpose a MS Access table:
http://support.microsoft.com/kb/202176

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

;-)

JeffCoachman
0
 
htamraz1Author 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Jeffrey CoachmanCommented:
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
 
htamraz1Author 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 CoachmanCommented:
This looks like it can be done with a recordset.

I'll give it a go...

Stay tuned...

JeffCoachman
0
 
htamraz1Author 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
 
htamraz1Author Commented:
Genius. This is exactly what I was looking for!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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