?
Solved

Group Similar values in cross tab query

Posted on 2008-06-16
6
Medium Priority
?
221 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 is a Denial of Service (DoS)?

A DoS is a malicious attempt to prevent the normal operation of a computer system. You may frequently see the terms 'DDoS' (Distributed Denial of Service) and 'DoS' used interchangeably, but there are some subtle differences.

 
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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

762 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