Solved

Group Similar values in cross tab query

Posted on 2008-06-16
6
216 Views
Last Modified: 2011-10-19
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
0
Comment
Question by:budorat
  • 3
  • 2
6 Comments
 
LVL 3

Expert Comment

by:fcfang
ID: 21798396
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.
0
 
LVL 3

Accepted Solution

by:
fcfang earned 500 total points
ID: 21798401
Oops. No attachment
Test2.mdb
0
 
LVL 44

Expert Comment

by:GRayL
ID: 21798420
Change the PIVOT to:

PIVOT Left(tblpmkPersAIRN.[Med Code],1) & Mid(tblpmkPersAIRN.[Med Code],2)
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 44

Expert Comment

by:GRayL
ID: 21798441
It duplicated what you did with 'hardcoding'.
0
 
LVL 5

Author Closing Comment

by:budorat
ID: 31467796
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
0
 
LVL 44

Expert Comment

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

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating and Managing Databases with phpMyAdmin in cPanel.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now