UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Counting Individuals Grouped By Different Age Groups, Access 2013    
 
   
ED Mattison
post Jan 11 2018, 04:44 PM
Post#1



Posts: 7
Joined: 25-July 17



I have to report information to a funder who wants to know the number of clients who are aged19-24,24-40,41-64,over 64. Even though I a new to Access, I had no trouble writing a query that showed the # of clients for each age, and used as a criterion any of the age groups. But is it possible to create a query that will give me totals for all the age groups? Thanks. Ed
Go to the top of the page
 
projecttoday
post Jan 11 2018, 05:06 PM
Post#2


UtterAccess VIP
Posts: 8,807
Joined: 10-February 04
From: South Charleston, WV


Yes.

Are you calculating the age from the date of birth or do you have an age column?

You need to add a column to the query for the age group and you can do this with an Iif, Switch, or Choose function.

Or you can use a join to an age-group table.
In the join instead of the standard = you use >= loage and <= hiage. Then change the group to agegroup.

tbAgegroups

table layout:
AgegroupID - autonumber primary key (you don't really need this)
Agegroup - integer
Loage - integer
Hiage - integer

data:
0/0/18
1/19/24
2/25/40
3/41/64
4/65/9999

--------------------
Robert Crouser

Go to the top of the page
 
doctor9
post Jan 11 2018, 05:10 PM
Post#3


UtterAccess Editor
Posts: 18,022
Joined: 29-March 05
From: Wisconsin


ED,

Assuming your data table is storing the birthdate for the person, and you're using a public function called "Age" that takes the birthdate as an argument and returns their date as of today, here's the basic setup:

19-24:
CODE
DCount("*","NameOfTableThatHoldsYourData","Age([NameOfBirthdateField])>=19 and Age([NameOfBirthdateField])<=24")


Adjust your two numbers in the DCount's condition to return different age brackets.

If the age brackets are subject to change, you can create a table that has two columns; one for the low age (19 in this example) and one for the high age (24). Then, in the query, you'd insert those numbers into the condition instead of hard-coding them. Sort by the low age field to sort your query's data from the lowest age bracket to the highest.

For example:
SQL
SELECT intLowAge AS [From], intHighAge AS [To],
DCount("*","tblClients","Age([dteBirthdate])>=" & [intLowAge] & " And Age([dteBirthdate])<=" & [intHighAge]) AS HowMany
FROM tblAgeBrackets ORDER BY intLowAge;


Just in case you need a function to calculate age, here's one:

CODE
Function Age(varBirthDate As Variant) As Integer

    Dim varAge As Variant
    
    If IsNull(varBirthDate) Then Age = 0: Exit Function
    
    varAge = DateDiff("yyyy", varBirthDate, Now)
    
    If Date < DateSerial(Year(Now), Month(varBirthDate), _
        Day(varBirthDate)) Then
        varAge = varAge - 1
    End If
    
    Age = CInt(varAge)

End Function


Hope this helps,

Dennis

EDIT: By the way, you need to clearly define your brackets. 19-24 and 24-40 has an overlap. Do you want the first bracket to include people who are 24? Or the second one? I would guess the answer is not "both".

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
JonSmith
post Jan 11 2018, 05:38 PM
Post#4



Posts: 3,227
Joined: 19-October 10



Btw, the partition function is basically designed for this.
Sadly I think it requires groups of equal size but its a pretty cool function.
I even used it to partition times in slots of 15 or 30 etc minute periods
Go to the top of the page
 
WildBird
post Jan 11 2018, 10:39 PM
Post#5


UtterAccess VIP
Posts: 3,288
Joined: 19-August 03
From: Perth, Australia


The way I would do it is have a calculated field with the Age, and another similar one to get the groupings (19-24, 25 - 30 etc) and present this is a pivot table in Excel. That way they can basically build their own reports, not keep coming back asking what if scenarios etc.

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
penfold098
post Jan 12 2018, 08:45 AM
Post#6



Posts: 134
Joined: 5-March 14



If you would like age ranges of different sizes, maybe you could employ Allen Browne's tip for looking up values in a range, then perform the sum query on the output field ( [AgeRange] below ).

For example:
[MinAge] [AgeRange]
19 "19-23"
24 "24-40"
41 "41-63"
64 "64+"


HTH
Go to the top of the page
 
ED Mattison
post Jan 12 2018, 06:23 PM
Post#7



Posts: 7
Joined: 25-July 17



Thank you all so much for taking the time to help me figure this problem out. I have an age column and I got lost trying out your suggestions. So here is an idea I can undstand and maybe there is some way to make it work. How about if I created a calculated field called Age _series. I thought I could use the IIf function to fill the Age series field when I entered an age. But I can't figure out the right expression. I thought it would look something like IIf (AGE [between 19 and 24],1924 or [between 25 and 40],2540 or [between 41and 64},4164 etc. I could then write a query using the count function to total each group. I realize I am just showing my ignorance, but I would appreciate your thoughts because my deadline for this report is coming soon and I don't want to count by hand. Thanks. Ed
Go to the top of the page
 
projecttoday
post Jan 12 2018, 08:13 PM
Post#8


UtterAccess VIP
Posts: 8,807
Joined: 10-February 04
From: South Charleston, WV


I think it would be just as simple to make a little table. Did you try it?

--------------------
Robert Crouser

Go to the top of the page
 
WildBird
post Jan 15 2018, 12:55 AM
Post#9


UtterAccess VIP
Posts: 3,288
Joined: 19-August 03
From: Perth, Australia


I now have Olympic birthdays - one every 4 years, was getting too [censored] old having one every year! But others have one every year, so their age is changing every year. This is why you don't store Age, but rather their data of birth, and it can be calculated easily.

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
doctor9
post Jan 15 2018, 09:10 AM
Post#10


UtterAccess Editor
Posts: 18,022
Joined: 29-March 05
From: Wisconsin


ED,

It's rarely a good idea to store the person's age, as that value changes over time. If you have 100 clients, that means you need to update 100 records every year. On the other hand, if you know their birthdate, just do it in the query like I suggested. Storing calculated values is also rarely a good idea.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
RJD
post Jan 15 2018, 10:51 AM
Post#11


UtterAccess VIP
Posts: 7,976
Joined: 25-October 10
From: Gulf South USA


Hi Ed:

QUOTE
I thought I could use the IIf function to fill the Age series field when I entered an age. But I can't figure out the right expression. I thought it would look something like IIf (AGE [between 19 and 24],1924 or [between 25 and 40],2540 or [between 41and 64},4164 etc. I could then write a query using the count function to total each group.

Your IIf syntax needs some work ...

I agree with the comments in the posts above concerning calculating age from DOB instead of storing it. But I am assuming that you are keeping up with age each year. There are cases where a table might contain an age (instead of a DOB) that is "age at course completion" for example - and that would not require calculation from DOB. I don't know your situation, so you can adjust my solution accordingly.

Here is a way to get counts by age group, with the age calculated from DOB in a subquery (instead of a UDF) and groupings by IIf instead of from a table...

CODE
SELECT
Sum(IIf([Age] Between 19 And 24,1,0)) AS Persons1924,
Sum(IIf([Age] Between 25 And 40,1,0)) AS Persons2540,
Sum(IIf([Age] Between 41 And 64,1,0)) AS Persons4164,
Sum(IIf([Age] Between 65 And 80,1,0)) AS Persons6580
FROM tblMyRecords INNER JOIN
(SELECT tblMyRecords.ID, IIf(Not IsNull([DOB]),DateDiff("yyyy",[DOB],Date())+(Format([DOB],"mmdd")>Format(Date(),"mmdd")),"") AS Age FROM tblMyRecords)  AS A ON tblMyRecords.ID = A.ID;

I am certainly not opposed to either a UDF for age nor to a table of groups. But now you have some options.

Of course, my solution assumes that the age groupings are pretty well fixed. I use this approach in a few databases that are used by charitable food pantries/programs to report to their food source organizations and other stake holders. But if the age groups are frequently changed, then the table approach might be easier than adjusting the SQL, depending on who is using the application.

Also in regards to the age calculations, there are several formulas in use, each producing the same results. If seeing and understanding the logic is important, then perhaps Dennis' UDF suggestion might be better.

Just a working solution (see attached) and some thoughts ...

HTH
Joe
Attached File(s)
Attached File  GroupsByDifferentAges.zip ( 19.09K )Number of downloads: 0
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
projecttoday
post Jan 15 2018, 11:21 AM
Post#12


UtterAccess VIP
Posts: 8,807
Joined: 10-February 04
From: South Charleston, WV


I decided to go ahead and create an example and I think this could go in the code archive. Somebody please take a look. It illustrates:

1. Collapsing metrics
2. Calculating age from the date of birth
3. Modifying a join so it looks at ranges instead of single values

So you have a main table with data about people (their names and their dates of birth) and a lookup table with age ranges. The query qryAgeGroups gives us the results. Note that we have to use the DateDiff expression twice in the join, but overall it's very simple and I think the best way to solve this problem.

You can use this as a template for other metrics. Others can enter their own age groups simply by changing the age groups table.
Attached File(s)
Attached File  AgeGroupsTableLookup.zip ( 19.28K )Number of downloads: 1
 

--------------------
Robert Crouser

Go to the top of the page
 
projecttoday
post Jan 15 2018, 11:28 AM
Post#13


UtterAccess VIP
Posts: 8,807
Joined: 10-February 04
From: South Charleston, WV


Oh, and yes you could do a count of that (just saw Joe's post and I forgot you're looking for a count).

--------------------
Robert Crouser

Go to the top of the page
 
ED Mattison
post Today, 04:49 PM
Post#14



Posts: 7
Joined: 25-July 17



I am overwhelmed by all the help you having been giving me, but as a Newbie, I am having a little trouble getting my brain around it. I would like to fill in some details about our reports, so you can see what we need. We are a social services agency, funded by the city, to provide short term housing for homeless people. We have found that our clients are much surer of their ages than their birth dates, so we use their ages in all our work. Since we only provide an immediate service, we don't need to keep their ages current. The city also wants to know their birthdates so we collect them, but don't use them. Since we have to report their ages by catagories (19-24. 25-40, 41-64,64+),it seems like it would be much easier if we could catagorize their ages in the intake process by adding an extra column in the in the datasheet then, which be calculated from the reported ages. Does this help? Thanks. Ed
Go to the top of the page
 
RJD
post Today, 06:01 PM
Post#15


UtterAccess VIP
Posts: 7,976
Joined: 25-October 10
From: Gulf South USA


Hi again:

QUOTE
Since we only provide an immediate service, we don't need to keep their ages current. The city also wants to know their birthdates so we collect them, but don't use them. Since we have to report their ages by catagories (19-24. 25-40, 41-64,64+),it seems like it would be much easier if we could catagorize their ages in the intake process by adding an extra column in the in the datasheet then, which be calculated from the reported ages. Does this help?

Not really. You really don't need anything but the age. And it looks like you are gathering that and not using the DOB (for the reason stated). The grouping is very simple then, using the SQL I indicated above (with a demo), but without the subquery. This should do ...

SELECT
Sum(IIf([Age] Between 19 And 24,1,0)) AS Persons1924,
Sum(IIf([Age] Between 25 And 40,1,0)) AS Persons2540,
Sum(IIf([Age] Between 41 And 64,1,0)) AS Persons4164,
Sum(IIf([Age] Between 65 And 80,1,0)) AS Persons6580
FROM tblMyRecords;

You can certainly put the age grouping in the form, triggered by the entry of Age, if you really want that, in an unbound control. That's easy with something like a Switch function in the control. But if you want to bind that to a table field, that will take some code behind the form, and I don't see that as at all necessary - and in some cases, a detriment.

See the revision to my previously posted demo, attached.

HTH
Joe
Attached File(s)
Attached File  GroupsByDifferentAges_Rev1.zip ( 18.31K )Number of downloads: 0
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
projecttoday
post Today, 06:38 PM
Post#16


UtterAccess VIP
Posts: 8,807
Joined: 10-February 04
From: South Charleston, WV


No column is necessary, or even desirable, on the intake form other than date-of-birth.

--------------------
Robert Crouser

Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    18th January 2018 - 06:59 PM