asked on

Group Similar values in cross tab query

Hi,

I have a crosstab query that I need to group similar values together under one column heading:

The attached file shows the crosstab report. I need to group the values of 203, 204, 205, 206 and 207 under one column heading of 203-207.
As you can see I have hard coded the column headings. I am not sure how to get them to group as 203-207.
Current code for crosstab is as follows:

TRANSFORM Count(tblpmkPersAIRN.Emplid) AS CountOfEmplid
SELECT tblPersDetails.ServiceType, tluServiceType.SortOrder, tluSubUnits.SortPriority, tblPersDetails.SubUnitCode, Count(tblpmkPersAIRN.Emplid) AS Total
FROM ((tblpmkPersAIRN LEFT JOIN tblPersDetails ON tblpmkPersAIRN.Emplid = tblPersDetails.EID) LEFT JOIN tluServiceType ON tblPersDetails.ServiceType = tluServiceType.ServiceType) LEFT JOIN tluSubUnits ON tblPersDetails.SubUnitCode = tluSubUnits.ID
GROUP BY tblPersDetails.ServiceType, tluServiceType.SortOrder, tluSubUnits.SortPriority, tblPersDetails.SubUnitCode
ORDER BY tluServiceType.SortOrder, tluSubUnits.SortPriority
PIVOT tblpmkPersAIRN.[Med Code] In ("1","201","202","203","204","205","206","207","301","302","303","304","401","402","403");

Open in new window

Test2.mdb

Member_2_978575

One solution is to do a select statement first to group your med codes into your "groupings" and then do the crosstab on it. I modified your mdb and it's enclosed. See queries step1 and step2. Step one uses a small function in the "code" module but you could simply do that within your step1 query.

Good luck.

ASKER CERTIFIED SOLUTION

Member_2_978575

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

GRayL

Change the PIVOT to:

PIVOT Left(tblpmkPersAIRN.[Med Code],1) & Mid(tblpmkPersAIRN.[Med Code],2)

GRayL

It duplicated what you did with 'hardcoding'.

Kev

ASKER

Thanks very much. I love the way you achieved this. It will have application into other areas of my DB.

A perfect result !!!! and so easy to understand.

Kev

GRayL

I'm curious, did you even look at my solution - no steps - just a simple change to the PIVOT clause of your original query.