How to group and count results of calculation

[Guest]

I have a query that calculates age of new admissions on date of
admission(DateDiff) in a selected period. I need to calculate the number of
new admissions for the period by several age ranges; e.g., 7-12, 13-20.

Can I do this in the same query, anothery querty, a report, or...?

Thanks

 

[Guest]

I would create two queries
The first query return the age as you already have
The second query, based on the first query, a group by query with a count

SELECT IIF(Query1Name.Age Between 1 And 6, "1-6", IIF(Query1Name.Age Between
7 And 12,"7-12", IIF(Query1Name.Age Between 13 And 20,"13-20","21-100"))) ,
Count(Query1Name.Age) AS CountOfAge
FROM Query1Name
GROUP BY Query1Name.Age

 

[Duane Hookom]

I would never hard-code the age ranges into an expression in a query.

Ideally it would be best to create a table of age ranges with MinAge,
MaxAge, and Title. Otherwise create a function in a standard module that
accepts the Age value (or date) and returns the age range title.

 

[Guest]

Thanks to both of you

I am going to try to work out both recommendations and i will poet my
results early tomorrow, 21-Sep.

 

[Guest]

Ofer, I have a new query as you recommended and it works, Thanks. Here is
the SQL it took for me to get it to work. Does this look OK?

SELECT IIf([qryAgeIniVisitV1].[AgeIniVisit] Between 0 And
6,"0-6",IIf([qryAgeIniVisitV1].[AgeIniVisit] Between 7 And
12,"7-12",IIf([qryAgeIniVisitV1].[AgeIniVisit] Between 13 And
20,"13-20",IIf([qryAgeIniVisitV1].[AgeIniVisit] Between 21 And
30,"21-30",IIf([qryAgeIniVisitV1].[AgeIniVisit] Between 31 And
40,"31-40",IIf([qryAgeIniVisitV1].[AgeIniVisit] Between 41 And
50,"41-50",IIf([qryAgeIniVisitV1].[AgeIniVisit] Between 51 And
60,"51-60",IIf([qryAgeIniVisitV1].[AgeIniVisit] Between 61 And 64,"61-64","65
and older")))))))) AS AgeRange, Count(qryAgeIniVisitV1.ChartNumber) AS
CountOfChartNumber
FROM qryAgeIniVisitV1
GROUP BY IIf([qryAgeIniVisitV1].[AgeIniVisit] Between 0 And
6,"0-6",IIf([qryAgeIniVisitV1].[AgeIniVisit] Between 7 And
12,"7-12",IIf([qryAgeIniVisitV1].[AgeIniVisit] Between 13 And
20,"13-20",IIf([qryAgeIniVisitV1].[AgeIniVisit] Between 21 And
30,"21-30",IIf([qryAgeIniVisitV1].[AgeIniVisit] Between 31 And
40,"31-40",IIf([qryAgeIniVisitV1].[AgeIniVisit] Between 41 And
50,"41-50",IIf([qryAgeIniVisitV1].[AgeIniVisit] Between 51 And
60,"51-60",IIf([qryAgeIniVisitV1].[AgeIniVisit] Between 61 And 64,"61-64","65
and older"))))))));

Duane, I am still trying to sort out how to follow your recommendation and
will work on that today.

Thanks

 

[Guest]

That what I had in mind, try Duane suggestion mybe you'll find it easier

--
I hope that helped
Good luck

Ofer, I have a new query as you recommended and it works, Thanks. Here is
the SQL it took for me to get it to work. Does this look OK?

SELECT IIf([qryAgeIniVisitV1].[AgeIniVisit] Between 0 And
6,"0-6",IIf([qryAgeIniVisitV1].[AgeIniVisit] Between 7 And
12,"7-12",IIf([qryAgeIniVisitV1].[AgeIniVisit] Between 13 And
20,"13-20",IIf([qryAgeIniVisitV1].[AgeIniVisit] Between 21 And
30,"21-30",IIf([qryAgeIniVisitV1].[AgeIniVisit] Between 31 And
40,"31-40",IIf([qryAgeIniVisitV1].[AgeIniVisit] Between 41 And
50,"41-50",IIf([qryAgeIniVisitV1].[AgeIniVisit] Between 51 And
60,"51-60",IIf([qryAgeIniVisitV1].[AgeIniVisit] Between 61 And 64,"61-64","65
and older")))))))) AS AgeRange, Count(qryAgeIniVisitV1.ChartNumber) AS
CountOfChartNumber
FROM qryAgeIniVisitV1
GROUP BY IIf([qryAgeIniVisitV1].[AgeIniVisit] Between 0 And
6,"0-6",IIf([qryAgeIniVisitV1].[AgeIniVisit] Between 7 And
12,"7-12",IIf([qryAgeIniVisitV1].[AgeIniVisit] Between 13 And
20,"13-20",IIf([qryAgeIniVisitV1].[AgeIniVisit] Between 21 And
30,"21-30",IIf([qryAgeIniVisitV1].[AgeIniVisit] Between 31 And
40,"31-40",IIf([qryAgeIniVisitV1].[AgeIniVisit] Between 41 And
50,"41-50",IIf([qryAgeIniVisitV1].[AgeIniVisit] Between 51 And
60,"51-60",IIf([qryAgeIniVisitV1].[AgeIniVisit] Between 61 And 64,"61-64","65
and older"))))))));

Duane, I am still trying to sort out how to follow your recommendation and
will work on that today.

Thanks

Thanks to both of you

I am going to try to work out both recommendations and i will poet my
results early tomorrow, 21-Sep.

 

[Guest]

I am sorry it has taken me so long to reply, preparing for huricane Rita.

I think I probably will need to get some off line help, I am a bit confused
as to how to proceed with your recommendation. I made a table, tblAgeRangeV1
and have created a query, but do not get info with it. I do not have any
data in the table.

Here is SQL for the query:
SELECT tblAgeRangeV1.AgeRange, Count(qryAgeIniVisitV1.AgeIniVisit) AS
CountOfAgeIniVisit
FROM ((tblAppointmentsV1 INNER JOIN tblPatientV1 ON tblAppointmentsV1.ID =
tblPatientV1.ID) INNER JOIN tblAgeRangeV1 ON tblAppointmentsV1.ID =
tblAgeRangeV1.ID) INNER JOIN qryAgeIniVisitV1 ON tblAgeRangeV1.ChartNumber =
qryAgeIniVisitV1.ChartNumber
GROUP BY tblAgeRangeV1.AgeRange, tblAppointmentsV1.ApptDate,
tblAppointmentsV1.ApptKept, tblAppointmentsV1.InitialVisit
HAVING (((tblAppointmentsV1.ApptKept)=True) AND
((tblAppointmentsV1.InitialVisit)=True));

Should I have a form that posts data to tblAgeRangeV1?

 

[Duane Hookom]

You need to enter records into the tblAgeRangeV1. Then add the table to your
query (the one containing the age) with no join lines. Add the criteria
under the age calculation to
Between [MinAge] and [MaxAge]

 

[Guest]

Got, that works!

Many thanks

You need to enter records into the tblAgeRangeV1. Then add the table to your
query (the one containing the age) with no join lines. Add the criteria
under the age calculation to
Between [MinAge] and [MaxAge]

--
Duane Hookom
MS Access MVP

I am sorry it has taken me so long to reply, preparing for huricane Rita.

I think I probably will need to get some off line help, I am a bit
confused
as to how to proceed with your recommendation. I made a table,
tblAgeRangeV1
and have created a query, but do not get info with it. I do not have any
data in the table.

Here is SQL for the query:
SELECT tblAgeRangeV1.AgeRange, Count(qryAgeIniVisitV1.AgeIniVisit) AS
CountOfAgeIniVisit
FROM ((tblAppointmentsV1 INNER JOIN tblPatientV1 ON tblAppointmentsV1.ID =
tblPatientV1.ID) INNER JOIN tblAgeRangeV1 ON tblAppointmentsV1.ID =
tblAgeRangeV1.ID) INNER JOIN qryAgeIniVisitV1 ON tblAgeRangeV1.ChartNumber
=
qryAgeIniVisitV1.ChartNumber
GROUP BY tblAgeRangeV1.AgeRange, tblAppointmentsV1.ApptDate,
tblAppointmentsV1.ApptKept, tblAppointmentsV1.InitialVisit
HAVING (((tblAppointmentsV1.ApptKept)=True) AND
((tblAppointmentsV1.InitialVisit)=True));

Should I have a form that posts data to tblAgeRangeV1?

 

[Guest]

Here is SQL of query that appears to work properly. Do you see any problem
with it?
SELECT Count(tblAgeRangeV1.AgeRange) AS CountOfAgeRange, tblAgeRangeV1.Min,
tblAgeRangeV1.Max
FROM qryAgeIniVisitV1, tblAgeRangeV1
WHERE (((qryAgeIniVisitV1.AgeIniVisit) Between [Min] And [Max]))
GROUP BY tblAgeRangeV1.Min, tblAgeRangeV1.Max;

You need to enter records into the tblAgeRangeV1. Then add the table to your
query (the one containing the age) with no join lines. Add the criteria
under the age calculation to
Between [MinAge] and [MaxAge]

--
Duane Hookom
MS Access MVP

I am sorry it has taken me so long to reply, preparing for huricane Rita.

I think I probably will need to get some off line help, I am a bit
confused
as to how to proceed with your recommendation. I made a table,
tblAgeRangeV1
and have created a query, but do not get info with it. I do not have any
data in the table.

Here is SQL for the query:
SELECT tblAgeRangeV1.AgeRange, Count(qryAgeIniVisitV1.AgeIniVisit) AS
CountOfAgeIniVisit
FROM ((tblAppointmentsV1 INNER JOIN tblPatientV1 ON tblAppointmentsV1.ID =
tblPatientV1.ID) INNER JOIN tblAgeRangeV1 ON tblAppointmentsV1.ID =
tblAgeRangeV1.ID) INNER JOIN qryAgeIniVisitV1 ON tblAgeRangeV1.ChartNumber
=
qryAgeIniVisitV1.ChartNumber
GROUP BY tblAgeRangeV1.AgeRange, tblAppointmentsV1.ApptDate,
tblAppointmentsV1.ApptKept, tblAppointmentsV1.InitialVisit
HAVING (((tblAppointmentsV1.ApptKept)=True) AND
((tblAppointmentsV1.InitialVisit)=True));

Should I have a form that posts data to tblAgeRangeV1?

 

[Duane Hookom]

looks good to me.

--
Duane Hookom
MS Access MVP
--

Here is SQL of query that appears to work properly. Do you see any
problem
with it?
SELECT Count(tblAgeRangeV1.AgeRange) AS CountOfAgeRange,
tblAgeRangeV1.Min,
tblAgeRangeV1.Max
FROM qryAgeIniVisitV1, tblAgeRangeV1
WHERE (((qryAgeIniVisitV1.AgeIniVisit) Between [Min] And [Max]))
GROUP BY tblAgeRangeV1.Min, tblAgeRangeV1.Max;

You need to enter records into the tblAgeRangeV1. Then add the table to
your
query (the one containing the age) with no join lines. Add the criteria
under the age calculation to
Between [MinAge] and [MaxAge]

--
Duane Hookom
MS Access MVP

I am sorry it has taken me so long to reply, preparing for huricane
Rita.

I think I probably will need to get some off line help, I am a bit
confused
as to how to proceed with your recommendation. I made a table,
tblAgeRangeV1
and have created a query, but do not get info with it. I do not have
any
data in the table.

Here is SQL for the query:
SELECT tblAgeRangeV1.AgeRange, Count(qryAgeIniVisitV1.AgeIniVisit) AS
CountOfAgeIniVisit
FROM ((tblAppointmentsV1 INNER JOIN tblPatientV1 ON
tblAppointmentsV1.ID =
tblPatientV1.ID) INNER JOIN tblAgeRangeV1 ON tblAppointmentsV1.ID =
tblAgeRangeV1.ID) INNER JOIN qryAgeIniVisitV1 ON
tblAgeRangeV1.ChartNumber
=
qryAgeIniVisitV1.ChartNumber
GROUP BY tblAgeRangeV1.AgeRange, tblAppointmentsV1.ApptDate,
tblAppointmentsV1.ApptKept, tblAppointmentsV1.InitialVisit
HAVING (((tblAppointmentsV1.ApptKept)=True) AND
((tblAppointmentsV1.InitialVisit)=True));

Should I have a form that posts data to tblAgeRangeV1?

:

I would never hard-code the age ranges into an expression in a query.

Ideally it would be best to create a table of age ranges with MinAge,
MaxAge, and Title. Otherwise create a function in a standard module
that
accepts the Age value (or date) and returns the age range title.

--
Duane Hookom
MS Access MVP
--

I would create two queries
The first query return the age as you already have
The second query, based on the first query, a group by query with a
count

SELECT IIF(Query1Name.Age Between 1 And 6, "1-6", IIF(Query1Name.Age
Between
7 And 12,"7-12", IIF(Query1Name.Age Between 13 And
20,"13-20","21-100")))
,
Count(Query1Name.Age) AS CountOfAge
FROM Query1Name
GROUP BY Query1Name.Age

--
I hope that helped
Good luck


:

I have a query that calculates age of new admissions on date of
admission(DateDiff) in a selected period. I need to calculate the
number
of
new admissions for the period by several age ranges; e.g., 7-12,
13-20.

Can I do this in the same query, anothery querty, a report, or...?

Thanks

 

[Guest]

On one table I have multiple blocks or ranges of four digit phone extensions?
For example:
"2000-3000"
"5000-6500"
Then on another table I have used the extension 2001 as it's own record.

How do I return records that will show what numbers are still available?

 

[Gary Walter]

On one table I have multiple blocks or ranges of four digit phone
extensions?
For example:
"2000-3000"
"5000-6500"
Then on another table I have used the extension 2001 as it's own record.

How do I return records that will show what numbers are still available?

Hi Christopher,

I'd start with a Michel's numbers table

http://groups.google.com/groups?&selm=#k8KyJYfBHA.1872@tkmsftngp02

***quote***
Making the table Iotas:

Make a table Ds with field d, 10 records,
values for d = 0 to 9.

In a make table query,
bring Ds table 4 times so you have
Ds
Ds_1
Ds_2
Ds_3

In a free column type:

Iota: Ds.d + 10*Ds_1.d + 100*Ds_2 + 1000*Ds_3

Generate the table Iotas and make Iota a primary key.
***unquote***

I don't know why you cannot have a
"high" and "low" field in your first table,
but, if you don't, then I would compute
them in a preliminary table

qryBlockRange

SELECT
t.ExtBlock,
CInt(Left([ExtBlock],4)) AS Low,
CInt(Right([ExtBlock],4)) AS High
FROM yurtable As t;

then, use in query with Iotas table
to enumerate all possible numbers

qryEnumExt

SELECT
q.ExtBlock,
Iotas.Iota AS Ext
FROM qryBlockRange As q, Iotas
WHERE
(((Iotas.Iota) Between [Low] And [High]));

Solution:

Left Join qryEnumExt to your
second table on Ext,
filter where second table.Ext IS NULL

SELECT
q.ExtBlock,
q.Ext
FROM
qryBlockRange As q
LEFT JOIN
othertable As o
ON
q.Ext = o.Ext
WHERE
o.Ext IS NULL