Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2005
    Posts
    144

    Unanswered: categorizing with age only in reports

    Hello All,
    I have designed a membership database with various functions. Currently Ii am working with reports in which I ran across an issue that requires expert advice.

    I have members of all ages in my database, and I need to categorize the ones in the age range of 16-25 years. How can I single out only the members within this age criteria so there name will show up in my report??

    Thanks alot

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Well, you can use the datediff() function to determine age. Once you have an age, you can use grouping levels to clump them together...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    i hope you have there birth date in the database not there age other wise you will a job once a year fixing the database

    here is a function that will work out there age

    Code:
    Function Age(Birth_Date,End_Date)
    '***************************************
    ' Works out the age of to the month
    '
    '***************************************
    Dim Months
    Dim Years
    Dim Temp
    'Check if no error
    If IsNull(Birth_Date) or Birth_Date ="" Then
        Age=0.0 
    else
     
        Months = DateDiff("m", CDate(Birth_Date), End_Date)
        Years = Int(Months / 12)
        Temp = Years * 12
        If Years = 0 then Years = "" 
        Age =  Years & "." & Months - Temp
    
    End if
    
    End Function
    to use it paste it into a module and sae the module

    you can use it in a query by age:Age([DOB],date())

    Know lets write a grouping function to group the ages

    Code:
    Function GroupAge(Age as double)
    
    Select case Age
    
    Case 0 to 5
    GroupAge = "to young"
    Case 5 to 10
    GroupAge = "5 - 10 Year"
    Case 10 to 16
    GroupAge = "10 - 16 Year"
    Case 16 to 25
    GroupAge = "16 - 25 Year"
    Case Else
    GroupAge = "To Old"
    End Select
    
    End Function
    That was easy

    use it in the query

    Age_Group: GroupAge(age([DOB],Date())

    done
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  4. #4
    Join Date
    Jul 2005
    Posts
    144
    First of all thanks alot both of you guys for a quick response!
    Let me tell you this first that i am novice to access database

    I have tried using myles idea but i cant go much further since i am not sure how will the module add into the database, i mean i have created the module just dont know how to attach it and also how can i attach it to the query??

    here is the link to my database here so you guys can look at it and tell me how to proceed. please try to understand i am new at this and trying very hard to learn it.
    thanks alot
    http://www.megaupload.com/?d=RMG98PV9

    ps: instead of all that can i simply restrict my report to those people that
    are 16 to 25 years of age
    Last edited by ranjah; 12-29-05 at 19:11. Reason: have an idea

  5. #5
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    can't download it

    email to me will work on it to nite when I get home stephanATmyle.co.nz

    1 click in modules
    2. click new then Plaste the above code in modules
    3. alt + q ( return you back to ms access

    open a Query Window

    In the
    Feilds:
    Table:

    In the Feilds part of the query try this PAge:Age([DATE_OF_BIRTH_FEILD_NAME],DATE())

    replace [DATE_OF_BIRTH_FEILD_NAME] with you feildname that hold the date of birth



    then Run it
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  6. #6
    Join Date
    Jul 2005
    Posts
    144
    Thanks myle
    i went ahead and emailed you as of last night. i hope you have received it.
    i have created a new query with this statement in it
    Age1: DateDiff("yyyy",Lifetimetbl.Dob1,Date())+(Date()<D ateSerial(Year(Date()),Month(Lifetimetbl.Dob1),Day (Lifetimetbl.Dob1)))
    criteria >=16 And <=25

    it does work however if i want to add more fields with dob2...and so forth it comesup with undefined function [year] in expression.

    Age2: DateDiff("yyyy",Lifetimetbl.Dob2,Date())+(Date()<D ateSerial(Year(Date()),Month(Lifetimetbl.Dob2),Day (Lifetimetbl.Dob2)))
    criteria >=16 And <=25

    any ideas??

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Pardon for asking, how the heck can someone have a second date of birth, or is your application trying to track Hindu re-incarnation?

  8. #8
    Join Date
    Jul 2005
    Posts
    144
    Quote Originally Posted by healdem
    Pardon for asking, how the heck can someone have a second date of birth, or is your application trying to track Hindu re-incarnation?
    Actually here is the deal, i have this membership database that i have been working on it has members name details along with spouse details and up to 5 kids info as well and they are named as child1..child5 there dob1..dob5.

    how do I enter each kid into the database so that they are connected to their respective parents?

    I tried to solve this by creating a simple form out of the child table, and it works perfectly for the age range issue, but it isnt practical for the database as a whole, because each member is added along with his whole family (spouse, kids) in the same form.

    I am attaching my database here.
    Attached Files Attached Files

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ranjah
    the reason you have a problem is that your database is not normalised. Going the route you are currently proposing means that you will have to make some arbitary decisions about how many children comprise a family. Virtually any decision you make could be broken - I'm aware of families in the UK of 15..20 children upto age 18.

    instead I'd reccomend that you consider storing the details of the members as a separate table, that way round you can associate a persons age with a person record - and store it as a date of birth.
    HTH

  10. #10
    Join Date
    Jul 2005
    Posts
    144
    Thanks for your advice, I am going to work on that immediately.
    Just one thing I am having trouble with for sometime now: how can you add two records together that do not have any numerical values.

    For example I need the total of FirstName, SpouseName and ChildName.

    If I have two tables with member[firstname][spousename] and kids[childname] how can I get the total number of records in these three fields??

    If only I could figure that out!
    Do you have any ideas, or maybe any sample database I could take a look at?

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I suppose it all depends on how your membeship records are structured.

    I have used a table contianing the members details and a separate column self referring to the members table contianing the membership number of the member who pays that members fees.

    As with all relational databases providing you have some means of relating the data you can extract what ever values you require

    you can use the count clause in your SQL

    eg select count(<mycolumn>) for <mytable> where <myparameter>=<parametervalue>

    However what is it you are tryinmg to count
    is it a childs name (ie number or occurances of each childs name) or is it the number of children a member may have).

    if the latter than as before normalising your data simplifies the process - its a single query
    use the having clause in place of where clause

    have a look a rudy's site www.r937.com/relational.htm for some pointers on table design.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •