Solved: CrossTab help

[calenger]

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.

[Cosmos75]

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?

[calenger]

Yes Thank you, Works great. I used the transform statement.