By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,630 Members | 1,166 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,630 IT Pros & Developers. It's quick & easy.

how to group data in column headings in crosstab query?

P: n/a
Lee
I have a crosstab query that lists the number of patients(DECnum)by
County. County is my Row Heading. Currently, the Column heading is Age
in months, which is set at one month intervals. I would like to reduce
the number of columns by specifying age groupings. For example, Column
1 shoud cover ages 0 to 6 months, Column 2: 7 to 18 months, column 3:
19 -29 months and column 4: 30-36 months.

I know I need to insert code for Pivot format, but I don't know how to
do that. Here's the SQL code I currently have:

TRANSFORM Count(qryEntryReferralsbyCounty.DECNum) AS CountOfDECNum
SELECT qryEntryReferralsbyCountyByAge.[County Name],
Count(qryEntryReferralsbyCountyByAge.DECNum) AS [Total Of DECNum]
FROM qryEntryReferralsbyCountyByAge
GROUP BY qryEntryReferralsbyCountyByAge.[County Name]
PIVOT qryEntryReferralsbyCountyByAge.age;

Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Lee,
Beginning with Access 2002 Microsoft finally began including something
similar to Excel's Pivot Tables in the Pivot Table & Pivot Chart view. If
you have Access 2002 I'd explore that feature.
--
Alan Webb
kn*******@SPAMhotmail.com
"It's not IT, it's IS"

"Lee" <le********@yahoo.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...
I have a crosstab query that lists the number of patients(DECnum)by
County. County is my Row Heading. Currently, the Column heading is Age
in months, which is set at one month intervals. I would like to reduce
the number of columns by specifying age groupings. For example, Column
1 shoud cover ages 0 to 6 months, Column 2: 7 to 18 months, column 3:
19 -29 months and column 4: 30-36 months.

I know I need to insert code for Pivot format, but I don't know how to
do that. Here's the SQL code I currently have:

TRANSFORM Count(qryEntryReferralsbyCounty.DECNum) AS CountOfDECNum
SELECT qryEntryReferralsbyCountyByAge.[County Name],
Count(qryEntryReferralsbyCountyByAge.DECNum) AS [Total Of DECNum]
FROM qryEntryReferralsbyCountyByAge
GROUP BY qryEntryReferralsbyCountyByAge.[County Name]
PIVOT qryEntryReferralsbyCountyByAge.age;

Nov 13 '05 #2

P: n/a
Lee
My particular machine has Access 2003; however, the database has to be
maintained in Access 2000 format because that's what most of my users
have on their machines.

Nov 13 '05 #3

P: n/a
"Lee" <le********@yahoo.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...
My particular machine has Access 2003; however, the database has to be
maintained in Access 2000 format because that's what most of my users
have on their machines.


I would create a function to provide the column headings

Function AgeGroupings(Age As Integer) As String
Select Case Age
Case 0 To 6
AgeGroupings = "0 - 6 Months"
Case 7 To 18
AgeGroupings = "7 - 18 Months"
Case 19 To 29
AgeGroupings = "19 - 29 Months"
Case Else
AgeGroupings = "30 Months or greater"
End Select
End Function

You can then use the function in the column header for the crosstab.

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.