Report design

[Tracy]

Hi,

I am new at designing reports--I'm used to importing data from Access into
SAS for data analysis, but now I need to design some reports in Access for
end users to run with their data. Can you design reports (I am guessing
using queries as the record source) that look similar to the following?

Demographic Report

Report Generated on 4/29/2008
Query is “All Recordsâ€
Number of Records=400



Age N %
< 5
5-14
15-19
20-24
25-44
45-64
65+
Unknown

Sex
Male
Female
Unknown

Etc.

Also, for a design like this (or at least portraying the age groups and can
be repeated for each gender):

Intent by Age and Gender

< 5 5-14
M F M F
Intent N % N % N % N %
Unintentional
Self-harm
Assault
Undetermined intent
Legal intervention
Operations of war
Other
Unknown
Total


Thanks!

 

[KARL DEWEY]

Try this ---
TRANSFORM Count(Demographic.Intent) AS CountOfIntent
SELECT IIf([Age]<5,"< 5",IIf([Age] Between 5 And 14,"5-14",IIf([Age] Between
15 And 19,"15-19",IIf([Age] Between 20 And 24,"20-24",IIf([Age] Between 25
And 44,"25-44",IIf([Age] Between 45 And
64,"45-64",IIf([Age]>64,"65+",IIf([Age] Is Null,"Unknown")))))))) AS
[Person's Age], Demographic.Sex
FROM Demographic
GROUP BY Demographic.Age, IIf([Age]<5,"< 5",IIf([Age] Between 5 And
14,"5-14",IIf([Age] Between 15 And 19,"15-19",IIf([Age] Between 20 And
24,"20-24",IIf([Age] Between 25 And 44,"25-44",IIf([Age] Between 45 And
64,"45-64",IIf([Age]>64,"65+",IIf([Age] Is Null,"Unknown")))))))),
Demographic.Sex
PIVOT Demographic.Intent;

 

[Tracy]

Hi Karl,

Thanks for your suggestion. I tried it, but am getting an error message
stating that there is a syntax error in TRANSFORM statement, here is the code
I used in building a query:

TRANSFORM Count([Trauma Registry].Intent) AS CountOfIntent
SELECT IIf([Age]<5,"< 5",IIf([Age] Between 5 And 14,"5-14",IIf([Age] Between
15 And 19,"15-19",IIf([Age] Between 20 And 24,"20-24",IIf([Age] Between 25
And 44,"25-44",IIf([Age] Between 45 And
64,"45-64",IIf([Age]>64,"65+",IIf([Age] Is Null,"Unknown"))))))))
AS AgeTxt, [Trauma Registry].Sex
FROM [Trauma Registry]
GROUP BY [Trauma Registry].Age, IIf([Age] Between 0 And 4,"< 5",
IIf([Age] Between 5 And 14,"05-14",IIf([Age] Between 15 And 19,"15-19",
IIf([Age] Between 20 And 24,"20-24",IIf([Age] Between 25 And 44,"25-44",
IIf([Age] Between 45 And 64,"45-64",IIf([Age]>=65,"65+",IIf([Age]='
',"Unknown")))))))), [Trauma Registry].Sex;
PIVOT [Trauma Registry].Intent;


Any suggestions on how to correct the error. Thanks!

--
TY

Try this ---
TRANSFORM Count(Demographic.Intent) AS CountOfIntent
SELECT IIf([Age]<5,"< 5",IIf([Age] Between 5 And 14,"5-14",IIf([Age] Between
15 And 19,"15-19",IIf([Age] Between 20 And 24,"20-24",IIf([Age] Between 25
And 44,"25-44",IIf([Age] Between 45 And
64,"45-64",IIf([Age]>64,"65+",IIf([Age] Is Null,"Unknown")))))))) AS
[Person's Age], Demographic.Sex
FROM Demographic
GROUP BY Demographic.Age, IIf([Age]<5,"< 5",IIf([Age] Between 5 And
14,"5-14",IIf([Age] Between 15 And 19,"15-19",IIf([Age] Between 20 And
24,"20-24",IIf([Age] Between 25 And 44,"25-44",IIf([Age] Between 45 And
64,"45-64",IIf([Age]>64,"65+",IIf([Age] Is Null,"Unknown")))))))),
Demographic.Sex
PIVOT Demographic.Intent;

--
KARL DEWEY
Build a little - Test a little

Hi,

I am new at designing reports--I'm used to importing data from Access into
SAS for data analysis, but now I need to design some reports in Access for
end users to run with their data. Can you design reports (I am guessing
using queries as the record source) that look similar to the following?

Demographic Report

Report Generated on 4/29/2008
Query is “All Recordsâ€
Number of Records=400



Age N %
< 5
5-14
15-19
20-24
25-44
45-64
65+
Unknown

Sex
Male
Female
Unknown

Etc.

Also, for a design like this (or at least portraying the age groups and can
be repeated for each gender):

Intent by Age and Gender

< 5 5-14
M F M F
Intent N % N % N % N %
Unintentional
Self-harm
Assault
Undetermined intent
Legal intervention
Operations of war
Other
Unknown
Total


Thanks!

 

[KARL DEWEY]

Your problem is an extra semicolon --
',"Unknown")))))))), [Trauma Registry].Sex;
PIVOT [Trauma Registry].Intent;

Remove the semicolon after [Trauma Registry].Sex

--
KARL DEWEY
Build a little - Test a little

Hi Karl,

Thanks for your suggestion. I tried it, but am getting an error message
stating that there is a syntax error in TRANSFORM statement, here is the code
I used in building a query:

TRANSFORM Count([Trauma Registry].Intent) AS CountOfIntent
SELECT IIf([Age]<5,"< 5",IIf([Age] Between 5 And 14,"5-14",IIf([Age] Between
15 And 19,"15-19",IIf([Age] Between 20 And 24,"20-24",IIf([Age] Between 25
And 44,"25-44",IIf([Age] Between 45 And
64,"45-64",IIf([Age]>64,"65+",IIf([Age] Is Null,"Unknown"))))))))
AS AgeTxt, [Trauma Registry].Sex
FROM [Trauma Registry]
GROUP BY [Trauma Registry].Age, IIf([Age] Between 0 And 4,"< 5",
IIf([Age] Between 5 And 14,"05-14",IIf([Age] Between 15 And 19,"15-19",
IIf([Age] Between 20 And 24,"20-24",IIf([Age] Between 25 And 44,"25-44",
IIf([Age] Between 45 And 64,"45-64",IIf([Age]>=65,"65+",IIf([Age]='
',"Unknown")))))))), [Trauma Registry].Sex;
PIVOT [Trauma Registry].Intent;


Any suggestions on how to correct the error. Thanks!

--
TY

Try this ---
TRANSFORM Count(Demographic.Intent) AS CountOfIntent
SELECT IIf([Age]<5,"< 5",IIf([Age] Between 5 And 14,"5-14",IIf([Age] Between
15 And 19,"15-19",IIf([Age] Between 20 And 24,"20-24",IIf([Age] Between 25
And 44,"25-44",IIf([Age] Between 45 And
64,"45-64",IIf([Age]>64,"65+",IIf([Age] Is Null,"Unknown")))))))) AS
[Person's Age], Demographic.Sex
FROM Demographic
GROUP BY Demographic.Age, IIf([Age]<5,"< 5",IIf([Age] Between 5 And
14,"5-14",IIf([Age] Between 15 And 19,"15-19",IIf([Age] Between 20 And
24,"20-24",IIf([Age] Between 25 And 44,"25-44",IIf([Age] Between 45 And
64,"45-64",IIf([Age]>64,"65+",IIf([Age] Is Null,"Unknown")))))))),
Demographic.Sex
PIVOT Demographic.Intent;

--
KARL DEWEY
Build a little - Test a little

Hi,

I am new at designing reports--I'm used to importing data from Access into
SAS for data analysis, but now I need to design some reports in Access for
end users to run with their data. Can you design reports (I am guessing
using queries as the record source) that look similar to the following?

Demographic Report

Report Generated on 4/29/2008
Query is “All Recordsâ€
Number of Records=400



Age N %
< 5
5-14
15-19
20-24
25-44
45-64
65+
Unknown

Sex
Male
Female
Unknown

Etc.

Also, for a design like this (or at least portraying the age groups and can
be repeated for each gender):

Intent by Age and Gender

< 5 5-14
M F M F
Intent N % N % N % N %
Unintentional
Self-harm
Assault
Undetermined intent
Legal intervention
Operations of war
Other
Unknown
Total


Thanks!