cross tab query (1 Viewer)

kobiashi · Oct 19, 2018

hi

i am trying to create a cross tab query but i cant seem to display the info how i want it.

i have two tables

table_wheelcavities table 1
wheelcavities_ID
vehicle_IDFK
DateAdded
Mileage
PrintName
MaximoNo

table_wheelCavities_M2M table 2
wheelcavities_M2M_ID
wheelcavities_IDFK
DateAdded
Wheel_IDFK
WorkType_IDFK
PositionOfOilInjector
length
width
depth
height
thickness
rollover

table_wheelcavities is left join to table_wheelcavities_M2M

so from table 2 user chooses worktype_IDFK, options are flats,cavities,flange heights, flange thickness, real flange thickness, rollover,
and adds dimension, not all the fields are filled in, so for example, if the user chooses flats, he only uses length, width and depth.

so in the crosstab query i want to be able to display the dimensions, with worktype as the column headers and the dimension type as the row header, ive tried to use expr as the row headers and the dimensions as values, but i just cant get it to work.

June7 · Oct 19, 2018

You show fields for dimensions. You want to switch so the dimensions are rows? Post example of raw data and desired output.

isladogs · Oct 19, 2018

Just add your length, width, height fields as additional row headings in the query design window. Although the wizard only allows three row headings, the designer has no such restriction.

kobiashi · Oct 19, 2018

June7 said:
You show fields for dimensions. You want to switch so the dimensions are rows? Post example of raw data and desired output.

ss1.png is the raw data from the query

ss2.png is the desired output, so the row headers are the dimension types, ie length, height, width
and the column headers are the work types, the values are the dimension values

kobiashi · Oct 19, 2018

isladogs said:
Just add your length, width, height fields as additional row headings in the query design window. Although the wizard only allows three row headings, the designer has no such restriction.

how can i aggregate the workype so i can use them as column headers?

June7 · Oct 19, 2018

That doesn't look like raw data, looks like a CROSSTAB query.

kobiashi · Oct 19, 2018

June7 said:
That doesn't look like raw data, looks like a CROSSTAB query.

when you say raw data, what do you want to see? just the data, or table with headers, the screen shot is just a select query, thats been grouped, and ive sum totaled the dimensions, length, width, depth, height, thickness, rollver

June7 · Oct 19, 2018

So the dimensions are already in separate fields in the table? You want to switch the dimensions to be rows? That would involve UNION query to reorganize the fields. Then a CROSSTAB to pivot on the Worktype.

Example of UNION:

SELECT wheelcavities_M2M_ID, WTDesc, "Length" AS Dimension, Length AS Data FROM Table1 LEFT JOIN WorkTypes ON Table1.WorkType_IDFK = WorkTypes.WorkTypeID
UNION SELECT wheelcavities_M2M_ID, WTDesc, "Width", Width FROM Table1 LEFT JOIN WorkTypes ON Table1.WorkType_IDFK = WorkTypes.WorkTypeID;

Then the Crosstab:

TRANSFORM Sum(Data) AS SumOfData
SELECT Dimension
FROM Query1
GROUP BY Dimension
PIVOT WTDesc;

kobiashi · Oct 19, 2018

thaks for the reply

all i really want to do is display the values under a column and row header, it really doesnt matter which is which, i need to be able to split the data into columns, and rows, the criteria is per dimension, lets say length and the worktype is cavities and flats, i need to be able to display that data in a table, so the column header could be length and the row header would be cavity and flats

June7 · Oct 19, 2018

Then you already have dimensions as column headers and worktypes as row headers. If you want the worktype description and not the ID, then build a query that joins tables so the description can be retrieved.

That does not involve CROSSTAB, just JOIN.

cross tab query (1 Viewer)

kobiashi

Registered User.

June7

AWF VIP

isladogs

MVP / VIP

kobiashi

Registered User.

Attachments

kobiashi

Registered User.

June7

AWF VIP

kobiashi

Registered User.

June7

AWF VIP

kobiashi

Registered User.

June7

AWF VIP

Similar threads

Users who are viewing this thread