AgeGroup

[Kebs]

I am beginner in access. I am working on a table with DOB to calculate the
age. This is fine but my problem is to place the calculated Age field into
AgeGroups eg 10-16, 16-21, 22-30 etc. Is there any way to create a query that
would place the records in the appropriate group? Please help.

 

[Allen Browne]

Create a table, with a record for each age group.
The table will have 2 fields, like this:
- MinAge Number primary key
- AgeName Text

Sample data:
MinAge AgeName
====== ========
0 Under 10
10 10 - 15
16 16 - 21
22 22 - 30
31 Over 30

Now that you have taught Access about your age groups, you can match the
age group to the person's age. One approach would be to copy the ELookup()
function from this age into a standard module in your database, and then use
an expression like this (as one line):
=ELookup("AgeName", "AgeGroupTable",
"MinAge <= " & Nz([Age],0), "MinAge DESC")

Another approach would be to use a subquery, by typing something like this
into the Field row in query design:
AgeGroup: (SELECT TOP 1 AgeName
FROM AgeGroupTable
WHERE MinAge <= Nz([Age],0)
ORDER BY MinAge DESC)
If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

Finally, if you have lots of records and find these approaches are too slow,
here's a more detailed solution from Tom Ellison:
Lookup in a range of values in a query
at:
http://allenbrowne.com/ser-58.html

 

[Kebs]

Thanx Allen,
I understood what you meant. But the problem with your suggestion is that,
The ages generated by the DOB field are not specific numbers as stated in
your AgeName field. The age numbers vary so the link the primary table will
not work as there will be ages in the related table that are not existing in
the structure you're suggesting. I need a criteria setting under the age
field that will generate the age groups. I hope this makes sense - Guys
looking forward to more help please!

Create a table, with a record for each age group.
The table will have 2 fields, like this:
- MinAge Number primary key
- AgeName Text

Sample data:
MinAge AgeName
====== ========
0 Under 10
10 10 - 15
16 16 - 21
22 22 - 30
31 Over 30

Now that you have taught Access about your age groups, you can match the
age group to the person's age. One approach would be to copy the ELookup()
function from this age into a standard module in your database, and then use
an expression like this (as one line):
=ELookup("AgeName", "AgeGroupTable",
"MinAge <= " & Nz([Age],0), "MinAge DESC")

Another approach would be to use a subquery, by typing something like this
into the Field row in query design:
AgeGroup: (SELECT TOP 1 AgeName
FROM AgeGroupTable
WHERE MinAge <= Nz([Age],0)
ORDER BY MinAge DESC)
If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

Finally, if you have lots of records and find these approaches are too slow,
here's a more detailed solution from Tom Ellison:
Lookup in a range of values in a query
at:
http://allenbrowne.com/ser-58.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am beginner in access. I am working on a table with DOB to calculate the
age. This is fine but my problem is to place the calculated Age field into
AgeGroups eg 10-16, 16-21, 22-30 etc. Is there any way to create a query
that would place the records in the appropriate group? Please help.

 

[Bob Quintal]

Thanx Allen,
I understood what you meant. But the problem with your suggestion
is that, The ages generated by the DOB field are not specific
numbers as stated in your AgeName field. The age numbers vary so
the link the primary table will not work as there will be ages in
the related table that are not existing in the structure you're
suggesting. I need a criteria setting under the age field that
will generate the age groups. I hope this makes sense - Guys
looking forward to more help please!

Reread Allen's post. You will not link to this table, you use a
Dlookup() function or the more sophisticated elookup() to find the
first record where the actual age is less than the Minage value in
the lookup table.

Create a table, with a record for each age group.
The table will have 2 fields, like this:
- MinAge Number primary key
- AgeName Text

Sample data:
MinAge AgeName
====== ========
0 Under 10
10 10 - 15
16 16 - 21
22 22 - 30
31 Over 30

Now that you have taught Access about your age groups, you can
match the age group to the person's age. One approach would be
to copy the ELookup() function from this age into a standard
module in your database, and then use an expression like this (as
one line):
=ELookup("AgeName", "AgeGroupTable",
"MinAge <= " & Nz([Age],0), "MinAge DESC")

Another approach would be to use a subquery, by typing something
like this into the Field row in query design:
AgeGroup: (SELECT TOP 1 AgeName
FROM AgeGroupTable
WHERE MinAge <= Nz([Age],0)
ORDER BY MinAge DESC)
If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

Finally, if you have lots of records and find these approaches
are too slow, here's a more detailed solution from Tom Ellison:
Lookup in a range of values in a query
at:
http://allenbrowne.com/ser-58.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am beginner in access. I am working on a table with DOB to
calculate the age. This is fine but my problem is to place the
calculated Age field into AgeGroups eg 10-16, 16-21, 22-30 etc.
Is there any way to create a query that would place the records
in the appropriate group? Please help.

 

[Kjetil Lenes]

I've tried this (need to convert individual weigths to weight groups), but a
bug has crept in:

The query correctly places the posts in the appropriate groups, but repeats
the returns times the number of posts. Ie: With 9 posts in my test database I
get the correct return, but repeated 9 times.

Any thoughts on this?

Kjetil Lenes

Allen Browne skrev:

Create a table, with a record for each age group.
The table will have 2 fields, like this:
- MinAge Number primary key
- AgeName Text

Sample data:
MinAge AgeName
====== ========
0 Under 10
10 10 - 15
16 16 - 21
22 22 - 30
31 Over 30

Now that you have taught Access about your age groups, you can match the
age group to the person's age. One approach would be to copy the ELookup()
function from this age into a standard module in your database, and then use
an expression like this (as one line):
=ELookup("AgeName", "AgeGroupTable",
"MinAge <= " & Nz([Age],0), "MinAge DESC")

Another approach would be to use a subquery, by typing something like this
into the Field row in query design:
AgeGroup: (SELECT TOP 1 AgeName
FROM AgeGroupTable
WHERE MinAge <= Nz([Age],0)
ORDER BY MinAge DESC)
If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

Finally, if you have lots of records and find these approaches are too slow,
here's a more detailed solution from Tom Ellison:
Lookup in a range of values in a query
at:
http://allenbrowne.com/ser-58.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am beginner in access. I am working on a table with DOB to calculate the
age. This is fine but my problem is to place the calculated Age field into
AgeGroups eg 10-16, 16-21, 22-30 etc. Is there any way to create a query
that would place the records in the appropriate group? Please help.

 

[Kjetil Lenes]

I've tried this, but a bug has crept in. The query returns the expected
result (in my case individual weights converted to weight groups), but
repeated several times. The pattern is that the result is repeated as many
times as there is posts in the database.

Anny thoughts on this?

Kjetil Lenes

Allen Browne skrev:

Create a table, with a record for each age group.
The table will have 2 fields, like this:
- MinAge Number primary key
- AgeName Text

Sample data:
MinAge AgeName
====== ========
0 Under 10
10 10 - 15
16 16 - 21
22 22 - 30
31 Over 30

Now that you have taught Access about your age groups, you can match the
age group to the person's age. One approach would be to copy the ELookup()
function from this age into a standard module in your database, and then use
an expression like this (as one line):
=ELookup("AgeName", "AgeGroupTable",
"MinAge <= " & Nz([Age],0), "MinAge DESC")

Another approach would be to use a subquery, by typing something like this
into the Field row in query design:
AgeGroup: (SELECT TOP 1 AgeName
FROM AgeGroupTable
WHERE MinAge <= Nz([Age],0)
ORDER BY MinAge DESC)
If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

Finally, if you have lots of records and find these approaches are too slow,
here's a more detailed solution from Tom Ellison:
Lookup in a range of values in a query
at:
http://allenbrowne.com/ser-58.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am beginner in access. I am working on a table with DOB to calculate the
age. This is fine but my problem is to place the calculated Age field into
AgeGroups eg 10-16, 16-21, 22-30 etc. Is there any way to create a query
that would place the records in the appropriate group? Please help.

 

[Allen Browne]

Not sure what's going on in your query.

Perhaps you need to create a prior query, and set its Unique Values property
to Yes, to remove your duplicates.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I've tried this, but a bug has crept in. The query returns the expected
result (in my case individual weights converted to weight groups), but
repeated several times. The pattern is that the result is repeated as many
times as there is posts in the database.

Anny thoughts on this?

Kjetil Lenes

Allen Browne skrev:

Create a table, with a record for each age group.
The table will have 2 fields, like this:
- MinAge Number primary key
- AgeName Text

Sample data:
MinAge AgeName
====== ========
0 Under 10
10 10 - 15
16 16 - 21
22 22 - 30
31 Over 30

Now that you have taught Access about your age groups, you can match the
age group to the person's age. One approach would be to copy the
ELookup()
function from this age into a standard module in your database, and then
use
an expression like this (as one line):
=ELookup("AgeName", "AgeGroupTable",
"MinAge <= " & Nz([Age],0), "MinAge DESC")

Another approach would be to use a subquery, by typing something like
this
into the Field row in query design:
AgeGroup: (SELECT TOP 1 AgeName
FROM AgeGroupTable
WHERE MinAge <= Nz([Age],0)
ORDER BY MinAge DESC)
If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

Finally, if you have lots of records and find these approaches are too
slow,
here's a more detailed solution from Tom Ellison:
Lookup in a range of values in a query
at:
http://allenbrowne.com/ser-58.html

I am beginner in access. I am working on a table with DOB to calculate
the
age. This is fine but my problem is to place the calculated Age field
into
AgeGroups eg 10-16, 16-21, 22-30 etc. Is there any way to create a
query
that would place the records in the appropriate group? Please help.