Convert column result to rows

[sanniep]

Dear expert,

I need to make a report where multiple faults and multiple resolutions for a single repair are displayed as a single row. The report will contain about a thousand repairs with an average of 4 faults and 3 resolutions per repair. The number of faults and resolutions are variable and do not need to be the same.

Situation now: attachment queryresult.gif
Should be: attachment targetresult.gif (had to skip a couple of fields to match the max. attachment resolution of 1024x768)

This thread kinda covers the subject..
http://www.thescripts.com/forum/thread191519.html

Hope you can help me out.. Sander

[PEB]

Hi,
Your goal is to do: every fault with every possible resolution, isn't it!
IS IT possible to do the union between the 2 fields, devided between them for exemple with _

So you can place this single column as a column heading in a crosstab query and u'll have the columns! But your data won't be so readable! And I don't know if you can exceed more than 255 columns!

But however u'll have instaed 2 columns by row 1 column with the information from the 2!

[sanniep]

PEB, thanks for your reply! Your solution almost covers my needs but, as you mentioned, won't make the report very readable. There are about 100 fault- and about the same number of resolutioncodes in those 1000 repairs so this would give me a table with 200+ columns.

I guess the only solution is a vb-script that creates the necessary columnheaders (fault 1, fault 2, fault 3, resolution 1, resolution 2, etc.) and moves the data from vertical to horizontal rows. Kinda like a Excel-macro that says "cut A2 to B2, A3 to C2, A4 to D2 and so on". It doesn't have to be dynamic columncreation, 10 fixed fault and resolution-columns is fine.

My goal is to get a single row for a repairorder with 10 columns (5 faultcodes, 5 resolutioncodes) filled with data.

Thanks for the help so far! Sander

[PEB]

Hi,
Reelly you can do a VB script that creates a table with the needed columns! There is a small danger! IN MS ACCESS you can create only 255 columns! If your data becomes important you aren't be able to fit it into the table!

In fact if you create the previous crosstab query, then a script that creates a table based on a column from query, you can populate this table using an Append Query that contains a function like Mid(mystr,1, instr(1,mystr,"_")-1) to separate the values from the crosstab.

Some expressions that can help you crating your VB script:

Set DefaultWorkspace = DBEngine.Workspaces(0)
Set CurrentDatabase = DefaultWorkspace.Databases(0)
Set MyTableDef = CurrentDatabase.CreateTableDef(tabl_name)

Set MyField = MyTableDef.CreateField(myrec("[" + Field_name + "]"), myrec("[" + type_name + "]"))

MyTableDef.fields.Append MyField
CurrentDatabase.TableDefs.Append MyTableDef

Have a nice day

[sanniep]

Peb!

I think I've accidently deleted the mail confirming the posting of your reply. Didn't have a clue you answered the post..

I will look into your advise right away, thanks!

Regards, Sander