Reformatting a query

[deb]

Hello Access World,

I have data that looks like this, in the query(TTLDuration)

ChargeNoDDurMDurADur
abc123 9 4 22
def765 10 17 12

I need it to look like (for charting purposes)...

abc123 9 DDur
abc123 4 MDur
abc123 22 ADur
def765 10 DDur
def765 17 MDur
def765 12 ADur


Do you knw the easiest way to make this happen?

Thanks in advance!!

 

[Ziggy]

Hi Deb,


First off the way you want to organize your data is how your table should be structured in the first place. I just making that point because sometimes people start off their tables like a spreadsheet with redundant column headings.

Having said that there may be other way to accomplish the task but I'll explain how I've done it in the past.

=====================================================

Take the query and make 3 new queries as follows.

In each query put the chargeNO field then each query put in these expressions in design view:

QTY:MDUR
Type:"MDUR"

*(I don't know what your fields represent so I have given them those names)

You do it for all 3 but only one of the types in each, you should get the following results:


ChargeNum qty Type
abc123 4 Mdur
def765 17 Mdur


ChargeNum Qty Type
abc123 22 Adur
def765 12 Adur

ChargeNum qty Type
abc123 9 ddur
def765 10 ddur



Now the rest is up to you how you want proceed. You can copy/paste all the records into a new table or set up append queries to move the data for you.

This will give you exactly what you need, like I suggested at the beginning your table should be structured this way to begin with.

Ziggy

 

[deb]

I know the data is organized goofy, but it is imported using ODBC from SAP. I just have to work with it the best I can...

I tried your suggestion and it works great.

Thank you for helping me sort out this pain...

 

[Ziggy]

Glad it helped, I know it sucks when you can't control your datasource..