Transposing Data

[darylharris]

Hello all,
I have data in a query that I need to transpose as demonstrated. Any thoughts on how I might do this, appreciated.

Regards

Daryl
Existing Format
DRAWING PROTOTYPE _3 _4 _5 _6 _7 _8 _9 _10
MC_I_E_43 DOLSHT1 A B C D E F G H
MC_I_E_44 DOLSHT2 F N K
MC_I_E_45 DOLSHT1 A B C D E F G H



Required Format
MC_I_E_43 PROTOTYPE DOLSHT1
MC_I_E_43 _3 A
MC_I_E_43 _4 B
MC_I_E_43 _5 C
MC_I_E_43 _6 D
MC_I_E_43 _7 E
MC_I_E_43 _8 F
MC_I_E_43 _9 G
MC_I_E_43 _10 H
MC_I_E_44 PROTOTYPE DOLSHT2
MC_I_E_44 _3 F
MC_I_E_44 _4
MC_I_E_44 _5
MC_I_E_44 _6 N
MC_I_E_44 _7
MC_I_E_44 _8 K
MC_I_E_44 _9
MC_I_E_44 _10
MC_I_E_45 PROTOTYPE DOLSHT1
MC_I_E_45 _3 A
MC_I_E_45 _4 B
MC_I_E_45 _5 C
MC_I_E_45 _6 D
MC_I_E_45 _7 E
MC_I_E_45 _8 F
MC_I_E_45 _9 G
MC_I_E_45 _10 H

 

[ajetrumpet]

Existing Format
DRAWING PROTOTYPE _3 _4 _5 _6 _7 _8 _9 _10
MC_I_E_43 DOLSHT1 A B C D E F G H
MC_I_E_44 DOLSHT2 F N K
MC_I_E_45 DOLSHT1 A B C D E F G H



Required Format
MC_I_E_43 PROTOTYPE DOLSHT1
MC_I_E_43 _3 A
MC_I_E_43 _4 B
MC_I_E_43 _5 C
MC_I_E_43 _6 D
MC_I_E_43 _7 E
MC_I_E_43 _8 F
MC_I_E_43 _9 G
MC_I_E_43 _10 H
MC_I_E_44 PROTOTYPE DOLSHT2
MC_I_E_44 _3 F
MC_I_E_44 _4
MC_I_E_44 _5
MC_I_E_44 _6 N
MC_I_E_44 _7
MC_I_E_44 _8 K
MC_I_E_44 _9
MC_I_E_44 _10

A bit of explanation would help here. Where are the field names? What does the data represent? What is Prototype? What is DOLSHT? Why does it look like there is supposed to be a record in the new format that contains "old format" field names? For one thing, that does make much sense.

Moving data around like this is not difficult, but you need to offer an explanation of what is what, not just copy and paste a bunch of data onto the page.

 

[darylharris]

Revised Transpose Post

Yes sorry for the format. I was rushing to catch a train from work and copied it in quickly and it turned out terrible.
I will now attempt to explain it better...

I am having trouble trying to get it to look right in text so will attach screenshots of what I'm trying to do. The screenshot of the existing format is from access and I have used excel to try and demonstrate what I am trying to acheive.

Any help appreciated.

Regards

Daryl

 

[mdb4me]

A union query would do it e.g.

SELECT Drawing, "Block1" as Block, Block1 as Utensil
FROM tblDwg as A
Union
SELECT Drawing, "Block2" as Block, Block2 as Utensil
FROM tblDwg as A
UNION SELECT Drawing, "Block3" as Block, Block3 as Utensil
FROM tblDwg as A;

 

[ajetrumpet]

Daryl,

Here's an illustration...

 

[darylharris]

Thankyou

Thankyou all so much for your help.

Adam, your code works brilliantly!!

I owe you several beers...

Cheers

Daryl

 

[ajetrumpet]

I owe you several beers...

Cheers

Hmmm...can I trade that for a commission?

Good luck to you Daryl.

 

[saman]

Daryl,

Here's an illustration...

hi all
I want used caption of fields instead fields.Name
how can it? for example:
field.name(A) with caption "aaa"
field.name(B) with caption "bbb"
field.name(C) with caption "ccc"
or please look at picture
thanks