How do I set 3 conditions with IF in an Access rpt?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to group ages in a report. I calculated age from DOB (I don't know if
that makes a difference). Now i need three age groups and I can only figure
out how to do 2. I need the groups to be 13-24, 25-49, and >50.
 
Create a table of age ranges:
tblAgeRange
====================
MinAge MaxAge AgeTitle
0 12 "Who cares"
13 24 "13-24"
25 49 "25-49"
50 999 "50+"

Then you add this table to your reports record source and set the criteria
under your Age column to:
Between MinAge and MaxAge
Add AgeTitle to the query grid so that you can use it in your sorting and
grouping.

When your pointed-haired boss says "change the age groups to 10-20, 21-30,
31-40, 41-50, and 51+" you can say "But that will take days!". You spend the
next 3 days playing Doom and other games on-line and then change the records
at the end of the 3rd day.
 
okay, seemed easy but I cannot get it to work. I have these variables in a
table:

CountofChartnumb=the count I am using for the report
Race=race of client (Black, White, Native American, Asian, Latino, Other)
RiskFactor=risk factor for HIV/AIDS (MSM,MSM/IDU,IDU,Hetero,Hetero/IDU,etc)
DOB=Date of Birth
Age=Calculted age from DOB

I want a report with the columns: Race, Risk Factor, Age group, count.
 
Try
SELECT Race, [Risk Factor], Sum(CountOfChartNumb) as Count, AgeTitle
FROM tblWithNoNameGiven, tblAgeRange
WHERE Age Between MinAge And MaxAge
GROUP BY Race, [Risk Factor];
 
Sorry, but I still don'et understand. Where do I put in this code?

Duane Hookom said:
Try
SELECT Race, [Risk Factor], Sum(CountOfChartNumb) as Count, AgeTitle
FROM tblWithNoNameGiven, tblAgeRange
WHERE Age Between MinAge And MaxAge
GROUP BY Race, [Risk Factor];

--
Duane Hookom
MS Access MVP


SEPRT said:
okay, seemed easy but I cannot get it to work. I have these variables in
a
table:

CountofChartnumb=the count I am using for the report
Race=race of client (Black, White, Native American, Asian, Latino, Other)
RiskFactor=risk factor for HIV/AIDS
(MSM,MSM/IDU,IDU,Hetero,Hetero/IDU,etc)
DOB=Date of Birth
Age=Calculted age from DOB

I want a report with the columns: Race, Risk Factor, Age group, count.
Click to expand...
Click to expand...
 
I tried this as a query and I get a message that says:

You tried to execute a query that does not include teh specified expression
'agetitle' as part of an aggregate function.

SELECT tblriskage.Race, tblriskage.RiskFactor,
Sum(tblriskage.CountOfChartnumb) AS [Count],tblageranges.AgeTitle
FROM tblriskage, tblAgeRanges
WHERE (((tblriskage.Age) Between [MinAge] And [MaxAge]))
GROUP BY tblriskage.Race, tblriskage.RiskFactor;

Duane Hookom said:
Try
SELECT Race, [Risk Factor], Sum(CountOfChartNumb) as Count, AgeTitle
FROM tblWithNoNameGiven, tblAgeRange
WHERE Age Between MinAge And MaxAge
GROUP BY Race, [Risk Factor];

--
Duane Hookom
MS Access MVP


SEPRT said:
okay, seemed easy but I cannot get it to work. I have these variables in
a
table:

CountofChartnumb=the count I am using for the report
Race=race of client (Black, White, Native American, Asian, Latino, Other)
RiskFactor=risk factor for HIV/AIDS
(MSM,MSM/IDU,IDU,Hetero,Hetero/IDU,etc)
DOB=Date of Birth
Age=Calculted age from DOB

I want a report with the columns: Race, Risk Factor, Age group, count.
Click to expand...
Click to expand...
 
Try:

SELECT tblriskage.Race, tblriskage.RiskFactor,
Sum(tblriskage.CountOfChartnumb) AS [Count],
tblageranges.AgeTitle
FROM tblriskage, tblAgeRanges
WHERE (((tblriskage.Age) Between [MinAge] And [MaxAge]))
GROUP BY tblriskage.Race, tblriskage.RiskFactor,tblageranges.AgeTitle;

--
Duane Hookom
MS Access MVP
--

SEPRT said:
I tried this as a query and I get a message that says:

You tried to execute a query that does not include teh specified
expression
'agetitle' as part of an aggregate function.

SELECT tblriskage.Race, tblriskage.RiskFactor,
Sum(tblriskage.CountOfChartnumb) AS [Count],tblageranges.AgeTitle
FROM tblriskage, tblAgeRanges
WHERE (((tblriskage.Age) Between [MinAge] And [MaxAge]))
GROUP BY tblriskage.Race, tblriskage.RiskFactor;

Duane Hookom said:
Try
SELECT Race, [Risk Factor], Sum(CountOfChartNumb) as Count, AgeTitle
FROM tblWithNoNameGiven, tblAgeRange
WHERE Age Between MinAge And MaxAge
GROUP BY Race, [Risk Factor];

--
Duane Hookom
MS Access MVP


SEPRT said:
okay, seemed easy but I cannot get it to work. I have these variables
in
a
table:

CountofChartnumb=the count I am using for the report
Race=race of client (Black, White, Native American, Asian, Latino,
Other)
RiskFactor=risk factor for HIV/AIDS
(MSM,MSM/IDU,IDU,Hetero,Hetero/IDU,etc)
DOB=Date of Birth
Age=Calculted age from DOB

I want a report with the columns: Race, Risk Factor, Age group, count.

:

Create a table of age ranges:
tblAgeRange
====================
MinAge MaxAge AgeTitle
0 12 "Who cares"
13 24 "13-24"
25 49 "25-49"
50 999 "50+"

Then you add this table to your reports record source and set the
criteria
under your Age column to:
Between MinAge and MaxAge
Add AgeTitle to the query grid so that you can use it in your sorting
and
grouping.

When your pointed-haired boss says "change the age groups to 10-20,
21-30,
31-40, 41-50, and 51+" you can say "But that will take days!". You
spend
the
next 3 days playing Doom and other games on-line and then change the
records
at the end of the 3rd day.

--
Duane Hookom
MS Access MVP


I need to group ages in a report. I calculated age from DOB (I don't
know
if
that makes a difference). Now i need three age groups and I can
only
figure
out how to do 2. I need the groups to be 13-24, 25-49, and >50.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top