Solved

Group Similar values in cross tab query

Posted on 2008-06-16
6
219 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
[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 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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

733 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