Results 1 to 3 of 3
  1. #1
    Dwillis472 is offline Novice
    Windows 8 Access 2013
    Join Date
    Jun 2015
    Posts
    1

    How to populate a table field from query calculation

    I have a table that contains information on hundreds of families. Part of that data are the ages of each child of each family. The table contains columns for different age groups..... Ages 0-12, 13-18, 19-24 that I need to populate. Is there a query expression that will determine the age group a child belongs and show in the appropriate group field in the table?


    The table contains some of the following columns:

    Family Child 1 Age Child 2 Age Child 3 Age Child 4 Age Child 5 Age Ages 0-12 Ages 13-18 Ages 19-24
    Smith 3 6
    Jones


    I am an experienced Access user but not a great programmer. I was attempting to write the following expression but not sure if this is the way to go.
    Expr1: IIf([Child 1 Age]>0 and <13,"SET [Ages 0-12]"=???. The expression needs to be able to calculate if one or more children of each family fall in the same age group and record the total (for Smith above, I need to record 2 in the Ages 0-12 group)

    F.Y.I, I have a query that will calculate the child's age based on the DOB and the System date to make sure the ages are always correct.

    I appreciate any responses.

  2. #2
    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
    It seems your table/database structure is not correct. When readers see fields/columns like Child1, Child2, Child3... it's a red flag suggesting your table(s) are not normalized. See this for normalization links

    It appears you have at least 2 tables:

    Family and Children where 1 Family can have 1 or many Children.

    Good luck.

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    This calc result does not and should not be saved into table. Calculate when needed.

    I agree with Orange that your data structure appears non-normalized and will give you lots of headaches. This issue is just the first one as it greatly complicates the calculation for how many children fall into each group.
    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. Replies: 1
    Last Post: 12-04-2014, 01:43 AM
  2. How to auto-populate a field based on a query on another table
    By lissy_vincent@yahoo.com in forum Forms
    Replies: 2
    Last Post: 02-17-2014, 04:08 PM
  3. Replies: 2
    Last Post: 11-27-2013, 05:24 PM
  4. Replies: 3
    Last Post: 05-06-2013, 03:03 PM
  5. Calculation Field in table vs Query calculation
    By Leonidsg in forum Database Design
    Replies: 18
    Last Post: 02-28-2013, 09:38 PM

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