Results 1 to 15 of 15
  1. #1
    Iceblue is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    7

    Group by age

    Hi


    My knowledge and need for access is extremely limited, I however have one problem that seem very advanced.
    I calculate the age of my members and then i need to divide them in groups.
    Younger than 9 years = peewee
    10 to 12 years= younger kadette
    13 to 15 years= older kadet
    16 to 18 years = junior and so on
    I tried iif and switch in a query but keep getting a error, and I have absolutely no idea how to work with a module, or code. Is there a way I can do this with very limited skill?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What error? Post your attempted query. If you want to provide db for analysis, follow instructions at bottom of my post.
    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
    Iceblue is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    7
    Ive tried to keep it very simple, only in cluding one if/or else, not even adding all the age groups and i get a invalid syntax, entered a operand without a operator error

    Agegroup:IIF([ContactAge] <9,"Peewee","Junior")

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    the error implies that ContactAge is null. As June requests, please post your entire query - in query design, go to sql view (button bottom right or choose from dropdown left end of ribbon), then copy and paste the sql

  5. #5
    Iceblue is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    7
    I will as soon as i am at my laptop again, but with what you said now, i think this might be part of my problem. I downloaded a template with a module and a query that get the age from the module, and just buld my data base around that. So if this suggest that contact age is nul, is it possible that i use the field wrong in my query? As mentioned i am a total newby, who watced a few youtube tutorials.

  6. #6
    Join Date
    Apr 2017
    Posts
    1,673
    The query must be something like:
    Code:
    SELECT 
    MemberID, 
    Forename, 
    LastName, 
    SomeFunction(BirthDate, Date) AS ContactAge, 
    Iif(Nz(BirthDate,)=0,Null,Chose(Iif(SomeFunction(BirthDate, Date) < 10,1,0)+Iif(SomeFunction(BirthDate, Date) < 13,1,0)+Iif(SomeFunction(BirthDate, Date) BETWEEN 13 AND 15,1,0)+Iif(SomeFunction(BirthDate, Date) BETWEEN 16 AND 18,1,0)+Iif(SomeFunction(BirthDate, Date) BETWEEN 19 AND24,1,0)+Iif(SomeFunction(BirthDate, Date) > 24,1,0),"peewee","younger cadette","older cadet","junior","young man", "old man")) AS AgeGroup
    FROM YourTable
    Edit: I corrected right query to wrong one immediately after posting it! The right one is
    Code:
    SELECT 
    MemberID, 
    Forename, 
    LastName, 
    SomeFunction(BirthDate, Date) AS ContactAge, 
    Iif(Nz(BirthDate,)=0,Null,Chose(Iif(SomeFunction(BirthDate, Date) > 0,1,0)+Iif(SomeFunction(BirthDate, Date) > 8,1,0)+Iif(SomeFunction(BirthDate, Date) > 12,1,0)+Iif(SomeFunction(BirthDate, Date) > 15,1,0)+Iif(SomeFunction(BirthDate, Date) > 18,1,0)+Iif(SomeFunction(BirthDate, Date) > 24,1,0),"peewee","younger cadette","older cadet","junior","young man", "old man")) AS AgeGroup
    FROM YourTable
    Last edited by ArviLaanemets; 07-08-2019 at 09:44 AM.

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    is it possible that i use the field wrong in my query?
    for when you return to your laptop - repeat - As June requests, please post your entire query

    and if you are using a function, provide the code to the function as well

  8. #8
    Iceblue is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    7
    SELECT [Contacts Extended].ContactID, [Contacts Extended].[Contact Name], [Contacts Extended].ContactAge
    FROM [Contacts Extended];

    Here is the code I get in sql view.

    And These are the catogories I need
    9 and younger = PeeWee
    10-12 Younger Cadette
    13 - 15 OlderKadette
    16 -18 Junior
    19-45 Senior
    45 and older Veteran

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    so you are storing ContactAge in a table called Contacts Extended. If it is a query then show the sql to the query

    as a test open that table/query and sort the age column so you have youngest at the top. Are there any blanks?

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    @arvil - is that chose or choose?

  11. #11
    Iceblue is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    7
    SELECT Contacts.*, IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[Last Name] & ", " & [First Name])) AS [File As], IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[First Name] & " " & [Last Name])) AS [Contact Name], Age([Birth Date]) AS ContactAge, Age([Birth Date]) & " years, " & AgeMonths([Birth Date]) & " months" AS ContactAgeWithMonth
    FROM Contacts
    ORDER BY IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[Last Name] & ", " & [First Name])), IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[First Name] & " " & [Last Name]));

    This is the sql on the contacts extended query that came with the template.
    There arnt currently blanks, but there will be blanks in the future, as I don't always get all the birth dates immediately.

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You did not provide function code. Need to handle possibility of null data. If function doesn't then do it when calling function: IIf([Birth Date] Is Null, 0, Age([Birth Date]))

    Calculate another field in that same query or build another query using that query as source or calculate in textbox. Age 45 cannot be in 2 categories, pick one.


    Either use the Choose() function as described by Arvil

    Choose(Iif(ContactAge > 0,1,0)+Iif(ContactAge> 9,1,0)+Iif(ContactAge> 12,1,0)+Iif(ContactAge> 15,1,0)+Iif(ContactAge> 18,1,0)+Iif(ContactAge> 45,1,0)+Iif(ContactAge=0,1,0), "peewee", "younger kadette", "older kadette", "junior", "senior", "veteran", "unknown"))

    or

    Switch([ContactAge]>45,"veteran", [ContactAge]>18,"senior", [ContactAge]>15,"junior", [ContactAge]>12,"older kadette", [ContactAge]>9,"younger kadette", [ContactAge]>0,"peewee", True,"unknown")

    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.

  13. #13
    Iceblue is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    7
    CT Kickboxing.zip

    So clearly im not the sharpest tool in this shed lol. Please see attached.

  14. #14
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    SELECT Contacts.*, IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[Last Name] & ", " & [First Name])) AS [File As], IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[First Name] & " " & [Last Name])) AS [Contact Name], Age([Birth Date]) AS ContactAge, Age([Birth Date]) & " years, " & AgeMonths([Birth Date]) & " months" AS ContactAgeWithMonth,
    Switch([ContactAge]>45,"veteran",[ContactAge]>18,"senior",[ContactAge]>15,"junior",[ContactAge]>12,"older kadette",[ContactAge]>9,"younger kadette",[ContactAge]>0,"peewee",True,"unknown") AS AgeGrp
    FROM Contacts
    ORDER BY IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[Last Name] & ", " & [First Name])), IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Company],[First Name]),IIf(IsNull([First Name]),[Last Name],[First Name] & " " & [Last Name]));

    All I did was copy/paste my suggested Switch function into cell of query builder and use the alias field name AgeGrp for your query Contacts Extended.

    Advise not to use spaces nor punctuation/special characters in naming convention. Also, don't advise use of multi-value fields.
    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.

  15. #15
    Iceblue is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    7
    I just moved my db to my desktop with a older version of Access (2013) and it works beautifully, yet om my laptop with access 365 2016 it still didn't work. I then tried to rw install office, with no luck. then my husband fiddled around with my windows 10 settings and changed the list separator from a period to a , and now it is working perfectly.
    Your code was perfect, and I really appreciate all your help and patience with me.

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

Similar Threads

  1. Replies: 2
    Last Post: 02-20-2019, 10:45 PM
  2. Replies: 2
    Last Post: 10-30-2017, 03:57 PM
  3. Replies: 2
    Last Post: 11-26-2016, 01:07 PM
  4. Replies: 6
    Last Post: 06-09-2015, 09:51 AM
  5. Replies: 0
    Last Post: 02-25-2013, 04:43 PM

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