Results 1 to 14 of 14
  1. #1
    Join Date
    Feb 2004
    Posts
    13

    Unanswered: Query for Age and Gender Groups?

    I am trying to run a report with Age group and gender showing frequency.

    e.g.
    Age Male Female
    16-20 38 35
    21-30 12 55
    31-35 5 14. Etc…

    The problem is I am unsure how to perform this task.

    Will have to perform 1 query per age group and per gender? That’s 28 sep queries, also how would I go about setting up the report to show this.

    I am stuck thanks…

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What have you tried so far? It might also help if you indicated what you are trying to report on. As in, what do the tables look like...
    oh yeah... documentation... I have heard of that.

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

  3. #3
    Join Date
    Feb 2004
    Posts
    13
    So far I have created separate queries for each age group and gender, it worked but I am unable to put them all on 1 report.

    The data is pulling from one table with PersonAge (with their age) and PersonGender (M or F).

    Currentley I made 1 query to pull everything, and then on the report for Gender I am trying a IF stmt to separate them out - e.g.

    =IIf([PersonGender]="F","F",[PersonGender]) Or IIf([PersonGender]="M","M",[PersonGender])

    However thats not working, this way I could pull 1 query from the DB and just let the report do the work.

    Thoughts?

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    That's the exact same thing as saying:

    =[PersonGender]

    think about the logic you're using...

    You're going to need to devise a way to seperate you the age groups. You can then use GROUP BY to pull your aggregates. I would probably create a table defining age groups and link to it.

    tblAgeGroups
    ----------
    age_goup_id
    min_age
    max_age

    Then you can do things like:

    SELECT age_group_id, SUM(IIF([PersonAge] = "M", 1, 0)) AS male_count, SUM(IIF([PersonAge] = "F", 1, 0)) AS female_count
    FROM tblPerson INNER JOIN tblAgeGroup ON tblPerson.age BETWEEN tblAgeGroup.min_age AND tblAgeGroup.max_age
    GROUP BY age_group_id
    oh yeah... documentation... I have heard of that.

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

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Teddy
    SELECT age_group_id, SUM(IIF([PersonAge] = "M", 1, 0)) AS male_count, SUM(IIF([PersonAge] = "F", 1, 0)) AS female_count
    FROM tblPerson INNER JOIN tblAgeGroup ON tblPerson.age BETWEEN tblAgeGroup.min_age AND tblAgeGroup.max_age
    GROUP BY age_group_id
    Whoa - when did Access start accomadating Theta joins? Is that an A03 thing?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Holy crap - just been playing around in A02. Where did I get the impression Theta joins weren't possible in Access? I had to enclose your expression in brackets but works fine. As does using <= and >=.

    Totally stunned - cheers Teddy
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Lol - more stuff I thought wasn't allowed:
    Code:
     SELECT A.Stuff, B.Stuff
    FROM tStuff AS A LEFT JOIN tStuff AS B ON A.Stuff = B.Stuff AND B.Stuff = MID(A.Stuff, 1, 3)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I'm lazy, I like shortcuts...
    oh yeah... documentation... I have heard of that.

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

  9. #9
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Here is a Function I wrote that works @ your 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
    it will return the year.months

    40.1 = 40 year and 1 month
    or
    40.11 = 40 year and 11 months




    just paste it in a module
    then in a Query

    Age:Age([DOB],Now()) = age now

    Age:Age([DOB],[DOD]) = age at deaf


    Let Make a New Function call Age_Group(Value)


    this is off the top or head ...

    [code]
    Function Age_Group(Value)

    select case Value

    case 1 to 5
    Age_Group = "baby"
    case 5 to 10
    Age_Group = "nice age"
    case 10 to 15
    Age_Group = "o my god"
    case else
    Age_Group = "left home "
    end select
    End Function
    [\code]

    so join then as one in a Query

    Age_Group:Age_Group(Age([DOB],Now()))
    Last edited by myle; 12-20-05 at 15:12.
    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.

  10. #10
    Join Date
    Feb 2004
    Posts
    13
    Can you please explain how you got this working by enclosing the expression in brackets? I finally have some time to get this query working and I am getting errors on it.

    select fld_age_group_id,
    Sum(IIF([PersonGender] = "M",1,0)) AS male_count,
    Sum(IIF([PersonGender]="F",1,0)) as female_Count
    from AccidentPerson
    INNER JOIN tbl_AgeGroups ON AccidentPerson.PersonAge BETWEEN tbl_AgeGroups.fld_min_age AND tbl_AgeGroups.fld_max_age
    group by fld_age_group_id

    Help!!!
    Last edited by jaymdouglas; 01-30-06 at 11:35.

  11. #11
    Join Date
    Feb 2004
    Posts
    13

    Error Message!

    Between operator without And in query expression 'AccidentPerson.PersonAge BETWEEN tbl_AgeGroups.fld_min_age'.

  12. #12
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    oh yeah... documentation... I have heard of that.

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

  13. #13
    Join Date
    Feb 2004
    Posts
    13
    Teddy,

    I've read through it and played with several different combinations and I still cannot grasp this query...

    SELECT Sum(IIf([PersonGender]="M",1,0)) AS male_count, Sum(IIf([PersonGender]="F",1,0)) AS female_Count
    FROM AccidentPerson
    WHERE (((AccidentPerson.PersonGender) Between [tbl_AgeGroups].[fld_min_age] And [tbl_AgeGroups].[fld_max_age]));
    Last edited by jaymdouglas; 01-30-06 at 12:17.

  14. #14
    Join Date
    Feb 2004
    Posts
    13

    Smile Solved!

    Ok, after banging my head against the monitor I finally got it...

    SELECT tbl_AgeGroups.Name, Sum(IIf([PersonGender]="M",1,0)) AS male_count, Sum(IIf([PersonGender]="F",1,0)) AS female_count
    FROM AccidentPerson, tbl_AgeGroups
    WHERE (((AccidentPerson.PersonAge) Between [Minimum] And [Maximum]))
    GROUP BY tbl_AgeGroups.Name
    ORDER BY tbl_AgeGroups.Name;

    Thanks Teddy for pointing me in the right direction!

Posting Permissions

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