Need to query a range of Ages (numbers)

  • Thread starter Thread starter James
  • Start date Start date
J

James

We have a table that has fields for Applicant, spouse and
dependents. Each of these items also has a separate
field for Age (Age, spouse age, depedent 1 age, etc...).
We need to be able to list in a report the number of
persons that fall within certain age groups such as 0-5,
6-17, 18-64, 64-74 and 75 and older. I just need to know
how many people for each record fall into these groups.
For instance, with a typical record, I would have husband
and wife between 18-64 and about 2 children between 6-
17. I would need to know that there are 2 persons
between 18-64 and 2 persons between 6-17 and be able to
put these numbers on our report.

Thanks for the help
 
We have a table that has fields for Applicant, spouse and
dependents. Each of these items also has a separate
field for Age (Age, spouse age, depedent 1 age, etc...).
Click to expand...

So do you update every record in the table every day, to track
birthdays!? Age is NOT really a suitable table field; it keeps
*changing*. You may want to consider storing the Date of Birth in a
date/time field DOB, and calculate the age in your query. You can do
this by typing

Age: DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") >
Format(Date(), "mmdd"), 1, 0)

in a vacant Field cell.

Also, it sounds like you're storing data for multiple people in a
single record. This isn't properly normalized - you should have a
table for People related many-to-one to a table of applications, so
that each person is in a record by themselves.
We need to be able to list in a report the number of
persons that fall within certain age groups such as 0-5,
6-17, 18-64, 64-74 and 75 and older. I just need to know
how many people for each record fall into these groups.
For instance, with a typical record, I would have husband
and wife between 18-64 and about 2 children between 6-
17. I would need to know that there are 2 persons
between 18-64 and 2 persons between 6-17 and be able to
put these numbers on our report.
Click to expand...

Probably the best way to do this is to create another Table in your
database, named Ranges, with fields Low, High, and RangeName. Create a
Query with this table and your "people" table with NO join line;
instead put a criterion on the calculated (or stored, if you insist)
Age field of
= [Ranges].[Low] AND <= [Ranges].[High]
Click to expand...

Make it a Totals query and group by RangeName.
 
John, Thanks for the help.
What we have is a simple database that is only being used
for a one year period for low income families, thus the
reason for stating the Age only info. We are required to
simply look at the Value for the Age and see if the
persons qualify for other services. The table we have
developed is very simple and flat. It only asks for the
names of household residents and asks their age (again,
this is only for a period of one year). So, we have
fields for Head of Household, Spouse and fields for up to
6 Dependents. The report that we generate from this
table needs to be able to tell us how many people in the
home are in certain age groups such as 0-5, 6-17, 18-64,
etc. I tried the criterion you wrote at the bottom of
your reply with our current table but I could not get any
results to come out. Any suggestions?

Thanks
-----Original Message-----
We have a table that has fields for Applicant, spouse and
dependents. Each of these items also has a separate
field for Age (Age, spouse age, depedent 1 age, etc...).
Click to expand...

So do you update every record in the table every day, to track
birthdays!? Age is NOT really a suitable table field; it keeps
*changing*. You may want to consider storing the Date of Birth in a
date/time field DOB, and calculate the age in your query. You can do
this by typing

Age: DateDiff("yyyy", [DOB], Date()) - IIF(Format ([DOB], "mmdd") >
Format(Date(), "mmdd"), 1, 0)

in a vacant Field cell.

Also, it sounds like you're storing data for multiple people in a
single record. This isn't properly normalized - you should have a
table for People related many-to-one to a table of applications, so
that each person is in a record by themselves.
We need to be able to list in a report the number of
persons that fall within certain age groups such as 0- 5,
6-17, 18-64, 64-74 and 75 and older. I just need to know
how many people for each record fall into these groups.
For instance, with a typical record, I would have husband
and wife between 18-64 and about 2 children between 6-
17. I would need to know that there are 2 persons
between 18-64 and 2 persons between 6-17 and be able to
put these numbers on our report.
Click to expand...

Probably the best way to do this is to create another Table in your
database, named Ranges, with fields Low, High, and RangeName. Create a
Query with this table and your "people" table with NO join line;
instead put a criterion on the calculated (or stored, if you insist)
Age field of
= [Ranges].[Low] AND <= [Ranges].[High]
Click to expand...

Make it a Totals query and group by RangeName.


.
Click to expand...
 
The report that we generate from this
table needs to be able to tell us how many people in the
home are in certain age groups such as 0-5, 6-17, 18-64,
etc. I tried the criterion you wrote at the bottom of
your reply with our current table but I could not get any
results to come out. Any suggestions?
Click to expand...

Did you in fact create the Range table as suggested? If so, please
post the SQL view of the query that's not working.
 
Here is the info from SQL View. I'm a newbie with Access
so I know I'm not creating this query right as you
suggested. Please Help.

SELECT [Household Information].[Household Information].[*]
FROM Ranges, [Household Information]
GROUP BY [Household Information].[Household Information].
[*]
HAVING ((([Household Information].[Household Information].
[*])>=[Ranges].[Low] And ([Household Information].
[Household Information].[*])<=[Ranges].[High]));


James
 
I seem to be struggling with the Query Structure for the
Ranges and Household Information tables. It doesn't seem
to let me group by RangeName

James
 
Here is the info from SQL View. I'm a newbie with Access
so I know I'm not creating this query right as you
suggested. Please Help.

SELECT [Household Information].[Household Information].[*]
FROM Ranges, [Household Information]
GROUP BY [Household Information].[Household Information].
[*]
HAVING ((([Household Information].[Household Information].
[*])>=[Ranges].[Low] And ([Household Information].
[Household Information].[*])<=[Ranges].[High]));
Click to expand...

Get rid of the *. That is selecting EVERY SINGLE FIELD from [Household
Information] - it makes no sense to Group By every field in Household
Information, for one thing, and you can't compare every field to Low
and High.

What is the actual table structure of [Household Information]? Does it
have multiple Age fields? If so, which do you want to group by? If
there are people in the household in five different age categories,
how do you want this record grouped?
 
What is the actual table structure of [Household Information]? Does it
have multiple Age fields? If so, which do you want to group by? If
there are people in the household in five different age categories,
how do you want this record grouped?
Click to expand...

I have 8 fields in the Household information table for
age: Age, Spouse Age, Dep1 Age, Dep2 Age, Dep3 Age, Dep4
Age, Dep5 Age and Dep6 Age. Each household member's age
is entered in these fields. We need within our report to
find out how many people fit into certain age groups (0-
5, 6-17, 18-64, 65-74 and 75 and over. The info is
needed for qualifying for a discount from the Local
Electric company at the time of application. On my
report I will have headings that say Age 0-5, Age 6-18,
etc. for each applicant. I need to have a total on each
applicant line for the particular age groups.
 
What is the actual table structure of [Household
Information]? Does it
have multiple Age fields? If so, which do you want to group by? If
there are people in the household in five different age categories,
how do you want this record grouped?
Click to expand...

We have 8 Age Fields. One by each possible household
member. This table is to list people's name with their
current ages. Their are other items in the table. Our
report that is generated lists all of the fields on a
line about each record. On our report, we will have
headings for age groups (0-5, 6-17, 18-64, 65-74 and 75
and Over). We need to add up the household members in
each group and put the number of people that fall into
each age group. This is for our local electric company,
and they need to know how many people in each age bracket
at the time of application fall into these age groups.

Thanks
James
 
James said:
What is the actual table structure of [Household Information]? Does it
have multiple Age fields? If so, which do you want to group by? If
there are people in the household in five different age categories,
how do you want this record grouped?
Click to expand...

I have 8 fields in the Household information table for
age: Age, Spouse Age, Dep1 Age, Dep2 Age, Dep3 Age, Dep4
Age, Dep5 Age and Dep6 Age. Each household member's age
is entered in these fields. We need within our report to
find out how many people fit into certain age groups (0-
5, 6-17, 18-64, 65-74 and 75 and over. The info is
needed for qualifying for a discount from the Local
Electric company at the time of application. On my
report I will have headings that say Age 0-5, Age 6-18,
etc. for each applicant. I need to have a total on each
applicant line for the particular age groups.
Click to expand...

Your data isn't normalized, so you're going to have a real hard time doing
what you want.

Rather than having 8 date fields on a single row, you should have 8 rows,
each with a single date (and a tag saying what type of age it is):

Household ID, Age Type, Age

Now it becomes quite straightforward to do what you're trying to do
 
We have 8 Age Fields. One by each possible household
member. This table is to list people's name with their
current ages. Their are other items in the table. Our
report that is generated lists all of the fields on a
line about each record. On our report, we will have
headings for age groups (0-5, 6-17, 18-64, 65-74 and 75
and Over). We need to add up the household members in
each group and put the number of people that fall into
each age group. This is for our local electric company,
and they need to know how many people in each age bracket
at the time of application fall into these age groups.
Click to expand...

Douglas is exactly correct.

If you have one person in the household, you have seven NULL and
useless fields. If you have eleven people in the household, you cannot
represent that family in your database.

If your table is designed to count PEOPLE, it should have one person
in each record. You have a one-to-many relationship between Households
(an entity residing at one address, with one account) and People - a
Household might consist of one person, or two, or a dozen. Jamming
this one to many relationship into a single record simply makes the
task of generating your report MUCH more difficult.

But not impossible. You'll need a UNION query to "unwrap" the data
into a properly normalized tall-thin table. To get the ranges, you'll
also need the RANGE table that I suggested previously. Given that
table, try:

SELECT RangeName, ApplicantID
FROM [Houshold Information]
INNER JOIN Ranges
ON [Household Information].[Age] >= [Ranges].[Low]
AND [Household Information].[Age] <= [Ranges].[High]
UNION ALL
SELECT RangeName, ApplicantID
FROM [Houshold Information]
INNER JOIN Ranges
ON [Household Information].[Spouse Age] >= [Ranges].[Low]
AND [Household Information].[Spouse Age] <= [Ranges].[High]
UNION ALL
SELECT RangeName, ApplicantID
FROM [Houshold Information]
INNER JOIN Ranges
ON [Household Information].[Dep1 Age] >= [Ranges].[Low]
AND [Household Information].[Dep1 Age] <= [Ranges].[High]
UNION ALL
<etc. for all six dependent age fields>

Save this query as uniAllAgeGroups.

Then use the Crosstab Query Wizard to create a query with RangeName as
the column header, and count ApplicantID's as the column header.

It's a WHOLE LOT EASIER if you have the data normalized though! You
only need to do this very last step.
 
Back
Top