Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Group Similar values in cross tab query

Posted on 2008-06-16
6
Medium Priority
?
223 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:Kev
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 44

Expert Comment

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

Author Closing Comment

by:Kev
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

610 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