Results 1 to 5 of 5

Thread: Query to auto populate another field in same record
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2018
    Location
    Saint Charles, MO
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Query to auto populate another field in same record

    Form called Input Allotments that shows the current funds of one state.
    Subform that shows all that states transactions: master_data_auto_fiscal_year_subform based on a Query.
    Query: Master_Data_auto_fiscal_year it has all the fields from the MasterData table
    The query takes the date entered and breaks it down to Monthnum field, then a field called: FY: IIf([monthnum]<10,Year([DATE]),Year([DATE])+1) this gives me the fiscal year in field FY.

    How do you get FY to auto populate FiscalYear field?

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,743
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Query to auto populate another field in same record

    Use an Update Query.
    See here: https://support.office.com/en-us/art...ee1e0514#bmcr2

    Note that it usually isn't necessary to store a value which can easily be calculated (as matter as fact, sometime it can violate the rules of data normalization, in that fields within a table should not be dependent on each other). If it can be calculated easy enough in a query, often storing them at the table level becomes totally unnecessary.
    Last edited by Joe4; Sep 11th, 2019 at 03:30 PM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    New Member
    Join Date
    May 2018
    Location
    Saint Charles, MO
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Query to auto populate another field in same record

    Field Monthnum: Month ([Date]) FY: IIf([monthnum]<10,Year([DATE]),Year([DATE])+1) Date FiscalYear REVISED_ALLOTMENTS
    Table Master_Data Master_Data Master_Data
    Sort YES Yes Yes
    Show
    Criteria [CURRENT_ALLOTMENT]+[GS_INCREASE]-[GS_DECREASE]+[SB_INCREASE]-[SB_DECREASE]+[LEASING_INCREASE]-[LEASING_DECREASE]
    or: [FY]

    Nothing happens but asks me what I want FY to be when you run the query.
    Last edited by CLCoop; Sep 11th, 2019 at 03:53 PM.

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,743
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Query to auto populate another field in same record

    You have to have a field pre set-up in your table to hold this value to hold this value (FY).
    Then in your Update Query, you would place the calculation on the "Update To" line of this FY field.
    If you look closely at the link I showed you, it shows you some examples and some example calculations.

    You probably will also want to add criteria under this FY field to only update records where this field is empty (Is Null), so if you run this query multiple times, you are only updating new records that have a blank in this field.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    New Member
    Join Date
    May 2018
    Location
    Saint Charles, MO
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Query to auto populate another field in same record

    Ok I was able to update records based on Parent Child just had to manually type in each field I wanted to do. As well as there is a limit to how many parent/child relationships but it was enough to "auto fill" what I was looking for.

    Go to the form, edit mode, click on the sub form you are wanting to auto fill based on form. go to Data. See the Link Master Fields and Link Child Fields. When you select this you get three boxes to link up. You can however add more fields by putting a ; with now spaces after the last field listed and keep adding. Remember to add the same to both Parent and Child if you skip one you may have data in the wrong field.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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