-
Solved: CrossTab help
Hi and thanks for reading
I have the attached Db that I need to build a crosstab report/query from. I have tons of aggreagates fields I need to add but hope some one could get me started.
The final report needs to have the years from 2002 to 2005 across the top and a break down of age down the side. the age needs to be broken down to the count of less then 55, 55-64,65-74 and 75 and older.
I removed any critical info to protect the guilty. (me )
The live access version of this is linked back to any sql server. I do have access to through enterprise manager if there is a better way.
-
Here are two examples of counting patient IDs grouped by the age groups. The year is for the Surgery_Date field.
Select Query
SELECT Year([Surgery_Date]) AS dtYear,
IIf([Age]<55,"<55",IIf([Age]<65,"55-64",IIf([Age]<75,"65-74",">75"))) AS AgeGroup,
SS_Patient_ID AS PatientCount
FROM Sheet1;
Crosstab
TRANSFORM Count(SS_Patient_ID) AS CountOfSS_Patient_ID
SELECT IIf([Age]<55,"<55",IIf([Age]<65,"55-64",IIf([Age]<75,"65-74",">75"))) AS AgeGroup
FROM Sheet1
GROUP BY IIf([Age]<55,"<55",IIf([Age]<65,"55-64",IIf([Age]<75,"65-74",">75")))
PIVOT Year([Surgery_Date]);
Is that what you were looking for?
-
Yes Thank you, Works great. I used the transform statement.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules