asked on

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,

htamraz1

ASKER

EE-Example.xls

Jeffrey Coachman

Transpose a MS Access table:
http://support.microsoft.com/kb/202176

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

;-)

JeffCoachman

htamraz1

ASKER

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?

Jeffrey Coachman

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...

htamraz1

ASKER

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.

Jeffrey Coachman

This looks like it can be done with a recordset.

I'll give it a go...

Stay tuned...

JeffCoachman

htamraz1

ASKER

Any thoughts on this?

ASKER CERTIFIED SOLUTION

rveliz

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

htamraz1

ASKER

Genius. This is exactly what I was looking for!