We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Normalize Access table on the fly by way of query

htamraz1
htamraz1 asked
on
Medium Priority
441 Views
Last Modified: 2012-06-21

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,
Comment
Watch Question

htamraz1Director of Technology

Author

Commented:
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

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

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

;-)

JeffCoachman
htamraz1Director of Technology

Author

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?

Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
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...
htamraz1Director of Technology

Author

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.
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
This looks like it can be done with a recordset.

I'll give it a go...

Stay tuned...

JeffCoachman
htamraz1Director of Technology

Author

Commented:
Any thoughts on this?
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
htamraz1Director of Technology

Author

Commented:
Genius. This is exactly what I was looking for!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.