Results 1 to 10 of 10
  1. #1
    KingOf206 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2013
    Location
    Seattle
    Posts
    153

    Question Create a query to group peoples ages on a specific date

    So this is the data I already have:

    Person 1 : DOB : Age on Nov 5th 2013

    Ex:

    John Smith: Jan 1 1960 : 53.7



    I have thousands of records that are like this. What I would like to do is to create a custom group range (E.G. 18-24, 25-30, 31-40, etc) and i would like to count how many records fall into which age groupings. What is the simpliest and most accurate way to find these counts? Any helpful hints or tricks would be appreciated!

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    First, need a field that assigns a group value to each record. That field can be constructed in a query with an expression, like:

    Switch([Age on Nov 5th 2013]<18,1, [Age on Nov 5th 2013]<25,2, [Age on Nov 5th 2013]<31,3, [Age on Nov 5th 2013]<41,4, [Age on Nov 5th 2013]<51,5, [Age on Nov 5th 2013]<61,6, [Age on Nov 5th 2013]<71,7, [Age on Nov 5th 2013]>70,8)

    or

    Switch([Age on Nov 5th 2013]<18,"Under 18", [Age on Nov 5th 2013]<25,"18-24", [Age on Nov 5th 2013]<"31,"25-30", [Age on Nov 5th 2013]<41,"31-40", [Age on Nov 5th 2013]<51,"41-50", [Age on Nov 5th 2013]<61,"51-60", [Age on Nov 5th 2013]<71,"61-70", [Age on Nov 5th 2013]>70,"Over 70")

    Then do a GROUP BY query that aggregates on that calculated field.
    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
    KingOf206 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2013
    Location
    Seattle
    Posts
    153
    Should the formulae above all go into 1 field within the current query I have? Or should it be on its own? Not sure where that information should go and/or if my current query will be useful...

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    That is a single expression to create a single field. Can probably be in your current query. Is it a GROUP BY query?

    You might want to create and save a SELECT query with that expression so it will be available for other uses besides just a single GROUP BY query. Build the GROUP BY from that saved SELECT query.
    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.

  5. #5
    KingOf206 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2013
    Location
    Seattle
    Posts
    153
    I really appreciate all your help with this. I do know my way around access, but when I plug in your expression I do not get the desired results. I know that I am not inputting the information in correctly.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Are you plugging into a GROUP BY query or a regular SELECT? Post your exact query statement.

    Why do you have a field named [Age on Nov 5th 2013]? What's so special about that date? What about other dates? What about next year?
    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.

  7. #7
    KingOf206 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2013
    Location
    Seattle
    Posts
    153
    Here is the current query I have so far : Click image for larger version. 

Name:	ageonelectionday.png 
Views:	9 
Size:	17.0 KB 
ID:	14126 (this obviously doesn't include the expression you gave me, but what I had before hand)

    I plan to use this function in the future for different dates, so if possible I would like to have an easy time changing what ever specific date I choose. This will only be about 4 different dates per year.

    There will never be anyone in my records that are younger than 18. I am just looking to find specific group totals such as how many people are 18-25 on or before Nov 5th 2013, how many are 26-30 on or before nov 5th 2013, and so on and so forth. just looking for an easy way to set this up once, that way I can change the dates as needed when I switch to a date that is later on..

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    The field name is [Age on Election Day], not [Age on Nov 5th 2013]. Which did you use in the expression?

    You have a field for Election Date? That doesn't make sense to me.

    A parameterized query can be used to set up a dynamic value for the election date. I don't often use dynamic parameters in query. This is an example with popup prompt. This could be reference to a form control instead. Try:

    SELECT *, [input election date] As ElectionDate, ([input election date]-[Voter Date of Birth])/365.25 As [Age on Election Day], Switch([Age on Election Day]<18,"Under 18", [Age on Election Day]<25,"18-24", [Age on Election Day]<"31,"25-30", [Age on Election Day]<41,"31-40", [Age on Election Day]<51,"41-50", [Age on Election Day]<61,"51-60", [Age on Election Day]<71,"61-70", [Age on Election Day]>70,"Over 70") As AgeGroup FROM tablename;
    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.

  9. #9
    KingOf206 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2013
    Location
    Seattle
    Posts
    153
    You obviously know a lot more about access than I do. I have tried to plug in the expression that you gave me but I get nothing but errors.

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I accidentally left an unpaired and unnecessary " mark in front of 31. Also, need to convert the input value to an actual date with CDate function.

    I did a test and this works:

    SELECT ID, BirthDate, CDate([input election date]) AS ElectionDate, ([ElectionDate]-[BirthDate])/365.25 AS AgeOnElectionDay, Switch([AgeOnElectionDay]<18,"Under 18",[AgeOnElectionDay]<25,"18-24",[AgeOnElectionDay]<31,"25-30",[AgeOnElectionDay]<41,"31-40",[AgeOnElectionDay]<51,"41-50",[AgeOnElectionDay]<61,"51-60",[AgeOnElectionDay]<71,"61-70",[AgeOnElectionDay]>70,"Over 70") AS AgeGroup
    FROM Table1;

    Note that I don't use spaces in naming fields and objects.
    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.

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

Similar Threads

  1. Group by year where date in a query
    By Ruegen in forum Queries
    Replies: 1
    Last Post: 09-16-2013, 11:09 PM
  2. Replies: 1
    Last Post: 05-17-2013, 01:50 AM
  3. Create a query to select a date
    By orcaa in forum Queries
    Replies: 1
    Last Post: 12-30-2012, 08:35 PM
  4. SELECT a specific DATE RANGE in Query
    By taimysho0 in forum Queries
    Replies: 28
    Last Post: 06-04-2012, 04:58 PM
  5. Replies: 11
    Last Post: 12-04-2010, 10:20 AM

Tags for this Thread

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