Transposing data in a report

[Rudolf14]

Hi all,

I Need help.

This is the data in my report:

Area:
Section:
Machine:
Component:
Date 1
severity 1
Date 2
Severity 2
Date 3
severity 3
.........

I need to trans pose it to this;

Area:
Section:
Machine:
Component:
Date 1 Date 2 Date 3................
Severity 1 Severity 2 Severity 3...........

Regards,


Rudolf

[CJ_London]

Not enough information

I would expect you would use a crosstab query, however you don't transpose data from a report but from a table and it is not at all clear what your table looks like. At the moment it looks denormalised which means you can't use a crosstab

A cross tab result would look like

Area: Section: Machine: Component: Date 1 .....Date 2 .....Date 3................
A.......25.........digger.....C1..............Seve rity 1.Severity 2.Severity 3...........

not as you have shown it

Suggest provide some example data as a table view and clarify what your output is required to look like in a report - your layout is possible but will require the use of subreports

[Rudolf14]

Hi Ajax,

My Table looks like the following:

TblMain

ID
Area
Section
Machine
Component
Date
Severity
Workorder
Finding
Descrition
Recommendation

This specific data is imported from a excel spreadsheet.

Hope this helps....

[CJ_London]

only a partial answer so suggest use a crosstab query

sort ascending, group and rowheading for fields Area, Section, Machine, Component
sort ascending, group and columnheading for date (note date is a reserved word and should not be used as a field name) Also, do not format it if you want to maintain a date order
max (or min or first or last) and Value for severity - since no data provided or what that is required to look like, that's my best guess

Also no idea how many dates you are talking about - there is a limit of 255 columns in total

good luck with your project

[Rudolf14]

Hi Ajax,

Thank you,
I managed to get it to work, My only problem that I have is that my date is a lot of dates, 574 to be exact, how do I limit it to only the last 100 dates?

[CJ_London]

again, insufficient data provided to provide a proper answer, but in principle you would use a criteria something like

WHERE Date in (SELECT TOP 100 Date FROM myTable T ORDER BY Date Desc)