age range summary

[maintt]

hi guys first post be gentle

i have a form with client info and i have age as a field and a combobox with age range which is selected by the user, i need a report that tells me how many clients there are in each age range ie

5 in 16-24
25 in 25-34
40 in 35-44


etc any ideas please


maintt

[Access_Blaster]

hi guys first post be gentle

i have a form with client info and i have age as a field and a combobox with age range which is selected by the user, i need a report that tells me how many clients there are in each age range ie

5 in 16-24
25 in 25-34
40 in 35-44
etc any ideas please


maintt

Check out this link:
http://office.microsoft.com/en-us/ac...001137763.aspx

Richard

[jzwp11]

Welcome to the forum!

First, I would not store the age since it is a value that changes constantly. It would be better to store the date of birth. With the date of birth, you can calculate the age of a person at any point in time.

Regarding the age ranges, I would probably set them up in a table.

tblAgeGroups
-pkAgeGroupID primary key autonumber
-txtAgeGroupName (this would be optional)
-longLowerLimit
-longUpperLimit

Technically speaking the above table is not normalized since each age group has 2 age limits (one-to-many relationship), but to keep things simple, we'll leave the table unnormalized.

For reference, the normalized structure would look like this:
tblAgeGroups
-pkAgeGroupID primary key, autonumber
-txtAgeGroupName

tblAgeGroupLimits
-pkAgeGroupLimitID primary key, autonumber
-longAge
-limit (just a field to distinguish whether the age is the lower or upper limit)


Now there are many ways to calculate the age of a person given their date of birth. I found this custom function a while back. You can actually use it to find the age of person at any point in time not just relative to today's date.

Public Function GetAge(varDOB As Variant, Optional varAsOf As Variant) As Variant
    'Purpose:   Return the Age in years.
    'Arguments: varDOB = Date Of Birth
    '           varAsOf = the date to calculate the age at, or today if missing.
    'Return:    Whole number of years.
    Dim dtDOB As Date
    Dim dtAsOf As Date
    Dim dtBDay As Date  'Birthday in the year of calculation.
    GetAge = Null          'Initialize to Null
    'Validate parameters
    If IsDate(varDOB) Then
        dtDOB = varDOB
        If Not IsDate(varAsOf) Then  'Date to calculate age from.
            dtAsOf = Date
        Else
            dtAsOf = varAsOf
        End If
        If dtAsOf >= dtDOB Then      'Calculate only if it's after person was born.
            dtBDay = DateSerial(Year(dtAsOf), Month(dtDOB), Day(dtDOB))
            GetAge = DateDiff("yyyy", dtDOB, dtAsOf) + (dtBDay > dtAsOf)
        End If
    End If
End Function

You would create a new module and add this code to it; then you can call the function whenever you need it.

Now you can create a query that shows the person and their age; something like this:

SELECT tblClient.txtClientFName, tblClient.txtClientLName, tblClient.dteBirth, getage(tblCLient.dteBirth) AS CurrentAge
FROM tblClient;

Now to show which age group they belong to, you would need to nest a query within the above query. I show the nested query in red below

query name: qryShowAgeGroupForEachClient
SELECT tblClient.txtClientFName, tblClient.txtClientLName, tblClient.dteBirth, getage(tblCLient.dteBirth) AS CurrentAge, (Select pkAgeGroupID from tblAgeGroups WHERE getAge(tblCLient.dteBirth) between longlowerlimit and longupperlimit) AS AgeGroup
FROM tblClient;


You can then use the above query to count the number of people in each age group using an aggregate query

query name: qryCountByAgeGroup
SELECT qryShowAgeGroupForEachClient.AgeGroup, Count(qryShowAgeGroupForEachClient.AgeGroup) AS CountOfAgeGroup
FROM qryShowAgeGroupForEachClient
GROUP BY qryShowAgeGroupForEachClient.AgeGroup;


You can then join the above query back to the age group table to get the lower/upper limits for each age group and the count in one place. I assume that if no people fall into a particular age group, you still want to show the age group and a zero for the count. To do this, you have to use a left join between the age group table and the query that does the counting. Furthermore, if there is not count for an age group, the query will return NULL, so you have to use an IIF() function to test for NULL and set the value to zero if true. If false, show the count. That final query will look like this:

query name: qryFinal
SELECT tblAgeGroups.longLowerLimit & "-" & tblAgeGroups.longUpperLimit AS AgeRanges, IIF (isnull(qryCountByAgeGroup.CountOfAgeGroup),0,qryC ountByAgeGroup.CountOfAgeGroup) AS CountByGroup
FROM tblAgeGroups LEFT JOIN qryCountByAgeGroup ON tblAgeGroups.pkAgeGroupID = qryCountByAgeGroup.AgeGroup;


You could then base your report on this final query.

I've attached an example database for reference.

[maintt]

Thank you for the reply and example, i will work on this tonight, very much appreciated, also I have used the date-add function for the age.

Can i ask another question?

I will have many reports on my database and some will have to be by date range Ie from and to, i want the user to be able to select a date range on a form which will print the report ie 01/01/2010 31/01/2010 how can this be done


Once again thanks for the reply

Maintt

[jzwp11]

You can use an unbound form with two textbox controls (for the 2 dates) and a command button. In the on_click event of the button, you would have code similar to the following:

Dim stDocName As String
 
    stDocName = "yourreportname"
    DoCmd.OpenReport stDocName, acPreview, , "yourdatefield between #" & Me.StartDate & "# and #" & Me.EndDate & "#"

StartDate and EndDate are the names of the two textbox controls on the unbound form. You will have to supply the actual report name and the date fieldname in the code. The record source for the report must include the date field of course.

I have used the date-add function for the age.

You would just substitute your age expression for the function in the query. Remember that SQL always assumes that dates are in the mm/dd/yyyy format.

[maintt]

hey thanks for the reply's

you know i ask IT guys at work and they are so reluctant to help, but the web is so friendly, thank you so much

maintt

[jzwp11]

You're welcome! Best of luck with your project.

[maximus]

Well my knowledge is very limited so excuse me if I am wrong. Assuming the Ranges are like this:

5 in 16-24
25 in 25-34
40 in 35-44


I assume I have a table with my employee details that contains DOB.
I prepare a simple query in which I add a Calculative Field to calculate Age:
=DateDiff("yyyy",[DOB],Now())

I name my query tblmember_Age

Then in a Report Unbound Report:
I put a unbound Text Box with this as Control Source:

=IIf(IsNull(DCount("[Member_id]","tblmember_Age","[Age] Between 0 And 10")),0,DCount("[Member_id]","tblmember_Age","[Age] Between 0 And 10"))

and in the Label I type 0-10

The Expression will give me the count of all the members that have age between 0-10.

Assuming Human beings have a average life expectancy of 75 and live upto a maximum of 100 (No offence to those who have crossed the century mark) The range list will be pretty small and thus this method is worth giving a try.

[maximus]

I have taken the liberty of modifying the sample attached.

Check:

Query tblClient_Age

Report:

Report1

[maximus]

were you able to solve your problem?

[maximus]

mark this thread solved if you were able o solve your problem.