Results 1 to 11 of 11
  1. #1
    maintt is offline Novice
    Windows Vista Access 2003
    Join Date
    Jul 2010
    Posts
    26

    age range summary

    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

  2. #2
    Join Date
    May 2010
    Posts
    339
    Quote Originally Posted by maintt View Post
    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

  3. #3
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    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.

    Code:
    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.

  4. #4
    maintt is offline Novice
    Windows Vista Access 2003
    Join Date
    Jul 2010
    Posts
    26

    age range

    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

  5. #5
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    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:


    Code:
    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.

  6. #6
    maintt is offline Novice
    Windows Vista Access 2003
    Join Date
    Jul 2010
    Posts
    26
    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

  7. #7
    jzwp11 is offline VIP
    Windows 7 Access 2007
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    You're welcome! Best of luck with your project.

  8. #8
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    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.

  9. #9
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    I have taken the liberty of modifying the sample attached.

    Check:

    Query tblClient_Age

    Report:

    Report1

  10. #10
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    were you able to solve your problem?

  11. #11
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    mark this thread solved if you were able o solve your problem.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Summary Query - How to?
    By ritch in forum Access
    Replies: 16
    Last Post: 02-17-2010, 03:18 PM
  2. How do I do a summary of my records
    By cowboy in forum Access
    Replies: 6
    Last Post: 02-04-2010, 11:58 AM
  3. need help with summing a range
    By sundance0000 in forum Access
    Replies: 3
    Last Post: 01-12-2010, 02:12 PM
  4. Product in a Summary Query
    By Fletch in forum Queries
    Replies: 0
    Last Post: 12-11-2008, 03:14 PM
  5. Summary Report Help Please?
    By solitaire in forum Reports
    Replies: 3
    Last Post: 03-03-2006, 08:10 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums