Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003
    Posts
    1

    Unanswered: Help With Quaery Function - MS Access

    We have set up a simple DB - to enable us to assertain age groups of children in local families.

    We have a table which has many columns - which we use for various data.

    The columns that we are having problems with are:

    Date of birth - child 1 , Date of birth - child 2 , Etc... we have 5 columns with childrens dates of birth. - as we have no families with more than 5 children

    The data is excellent when viewed in table format - however when we run a query we require the following info:- (in separate query's)

    1. List only children under 5Years old.

    2. List only children aged 5-11

    3. list only children aged 11-16

    4. list only children 16+

    We beleive that the table has been setup corrrectly using the Date format for entering data.

    At present we are entering the query criteria as:- (in the show column) between #01/09/98 and #01/09/2003.

    this is fine - however it only looks in the table for children under five in the column named "child 1 DOB"

    we paste the same criteria across to the next column in the "OR" section - but the search results are not showing the data requested.

    we want it to search all of the table - and only display the children under 5.

    obviously once we know the format for finding the 5 year olds - it will be simple to chnage the parameter to find the other age groups.

    Many Thanks.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    here ya go:
    Code:
    select family_id, child1name
      from yourtable
     where year(date()) - year(child1DOB)
           - iif(month(date()) > month(child1DOB),0, 
             iif(month(date()) < month(child1DOB),1,
             iif(day(date()) < day(child1DOB),1,0))) 
           between [enter lower age (0 if birth)]            
               and [enter upper age]
    union all
    select family_id, child2name
      from yourtable
     where year(date()) - year(child2DOB)
           - iif(month(date()) > month(child2DOB),0, 
             iif(month(date()) < month(child2DOB),1,
             iif(day(date()) < day(child2DOB),1,0))) 
           between [enter lower age (0 if birth)] 
               and [enter upper age]
    union all
    select family_id, child3name
      from yourtable
     where year(date()) - year(child3DOB)
           - iif(month(date()) > month(child3DOB),0, 
             iif(month(date()) < month(child3DOB),1,
             iif(day(date()) < day(child3DOB),1,0))) 
           between [enter lower age (0 if birth)] 
               and [enter upper age]
    union all
    select family_id, child4name
      from yourtable
     where year(date()) - year(child4DOB)
           - iif(month(date()) > month(child4DOB),0, 
             iif(month(date()) < month(child4DOB),1,
             iif(day(date()) < day(child4DOB),1,0))) 
           between [enter lower age (0 if birth)] 
               and [enter upper age]
    union all
    select family_id, child5name
      from yourtable
     where year(date()) - year(child5DOB)
           - iif(month(date()) > month(child5DOB),0, 
             iif(month(date()) < month(child5DOB),1,
             iif(day(date()) < day(child5DOB),1,0))) 
           between [enter lower age (0 if birth)] 
               and [enter upper age]
    rudy
    http://r937.com/

Posting Permissions

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