By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,619 Members | 2,309 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,619 IT Pros & Developers. It's quick & easy.

Convert column result to rows

P: 48
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
Attached Images
File Type: gif queryresult.GIF (13.3 KB, 660 views)
File Type: gif targetresult.GIF (6.6 KB, 548 views)
Aug 22 '06 #1
Share this Question
Share on Google+
4 Replies


PEB
Expert 100+
P: 1,418
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!
Aug 29 '06 #2

P: 48
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
Aug 29 '06 #3

PEB
Expert 100+
P: 1,418
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
Aug 30 '06 #4

P: 48
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
Sep 12 '06 #5

Post your reply

Sign in to post your reply or Sign up for a free account.