Results 1 to 11 of 11
  1. #1
    ncub is offline Novice
    Windows XP Access 2007
    Join Date
    Apr 2015
    Posts
    10

    Count by age range AND gender

    For example, I have 1 table with 3 fields : Name, Birth date, Gender.


    Then I want to create a report where all data are grouped by age range and gender

    Click image for larger version. 

Name:	Forum.jpg 
Views:	26 
Size:	34.6 KB 
ID:	21226

    Any help would be appreciated.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Will have to construct a field with calculation to assign range value to each record then use that field in CROSSTAB query. No one age greater than 20 years?

    Like:

    SELECT *, DateDiff("yyyy", [Birthday], Date()) AS Age, Switch([Age]<=5, "0-5 years", [Age]<=10, "6-10 years", [Age]<=15, "11-15 years", [Age]>15, "16-20 years") AS AgeGroup FROM tablename;

    Now use that query in a CROSSTAB. If no records fall into a group, group will not have a row in the CROSSTAB.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    CJ_London is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    something like

    Code:
    SELECT (((DateDiff("yyyy",Birthday,Date())-1) \ 5)+1 & " - " & (((DateDiff("yyyy",Birthday,Date())-1) \ 5)+5 & " years" AS [Age Range], sum(iif(Gender="Male",1,0))  AS Males, sum(iif(Gender="Female",1,0))  AS Females
    FROM myTable
    GROUP BY (((DateDiff("yyyy",Birthday,Date())-1) \ 5)+1 & " - " & (((DateDiff("yyyy",Birthday,Date())-1) \ 5)+5 & " years"

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Nice, Ajax, emulating CROSSTAB occurred to me but did not pull it together, wasn't seeing the GROUP BY expression. Again, if no records fit into a particular category, won't be a record.

    Thanks orange, learned a new function.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    CJ_London is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    new function for me as well

  7. #7
    ncub is offline Novice
    Windows XP Access 2007
    Join Date
    Apr 2015
    Posts
    10
    Thanks all !!
    Calculate age and store it in a new field, then applied Ajax's code.
    It works.
    Next I want to try Partition function since it looks more simple and easier to read, but still don't know how to separate the result between Male & Female

  8. #8
    CJ_London is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    you shouldn't store things like age - next year it will be different - my suggestion calculates the age on the fly

    but still don't know how to separate the result between Male & Female
    my suggestion does that as well - I've used your field names, so you just need to change the tablename to whatever your table is called and run it to produce the result you required

  9. #9
    ncub is offline Novice
    Windows XP Access 2007
    Join Date
    Apr 2015
    Posts
    10
    Thanks Ajax,
    I've tried your code and it works perfectly.

    but still curious to use Partition....

  10. #10
    CJ_London is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    Not tested, but I would suggest something like this

    Code:
    SELECT Partition(DateDiff("yyyy",Birthday,Date()),0,100,5) & " years" AS [Age Range], sum(iif(Gender="Male",1,0))  AS Males, sum(iif(Gender="Female",1,0))  AS Females
    FROM myTable
    GROUP BY Partition(DateDiff("yyyy",Birthday,Date()),0,100,5) & " years"

  11. #11
    ice_age is offline Novice
    Windows 8 Office 365
    Join Date
    Oct 2020
    Posts
    1
    I had this problem and I searched for maybe 2 hours to find a solution ! you are a life save thanks !

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

Similar Threads

  1. How to group people's titles by gender?
    By Idznak in forum Queries
    Replies: 3
    Last Post: 11-17-2013, 09:56 AM
  2. Replies: 2
    Last Post: 11-25-2010, 11:01 AM
  3. Sorting a questionnaire by gender
    By uchiha37 in forum Access
    Replies: 1
    Last Post: 10-07-2010, 07:37 AM
  4. Replies: 3
    Last Post: 08-26-2010, 02:11 PM
  5. Count Dates within Date Range
    By eckert1961 in forum Queries
    Replies: 2
    Last Post: 03-08-2009, 10:58 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