Report design

T

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!
 
K

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;
 
T

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


KARL DEWEY said:
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


Tracy said:
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!
Click to expand...
Click to expand...
 
K

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


Tracy said:
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


KARL DEWEY said:
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


Tracy said:
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!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

IIF in a report Footer 3
MIN and MAX across columns 6
custom intervals in reports 5
highlighting differences between records in a report 1
display report from a form with subforms 1
Spreadsheets 3
INDEX, VLOOKUP? What's best for this situation? 7
counter in "If" condition 2

Top