Query to auto populate another field in same record

CLCoop

New Member
Joined
May 30, 2018
Messages
43
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?
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,067
Office Version
365
Platform
Windows
Use an Update Query.
See here: https://support.office.com/en-us/ar...ry-9dddc97c-f17d-43f4-a729-35e5ee1e0514#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:

CLCoop

New Member
Joined
May 30, 2018
Messages
43
FieldMonthnum: Month ([Date])FY: IIf([monthnum]<10,Year([DATE]),Year([DATE])+1)DateFiscalYearREVISED_ALLOTMENTS
TableMaster_DataMaster_DataMaster_Data
SortYESYesYes
Show
Criteria[CURRENT_ALLOTMENT]+[GS_INCREASE]-[GS_DECREASE]+[SB_INCREASE]-[SB_DECREASE]+[LEASING_INCREASE]-[LEASING_DECREASE]
or:[FY]

<tbody>
</tbody>

Nothing happens but asks me what I want FY to be when you run the query.
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
52,067
Office Version
365
Platform
Windows
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.
 

CLCoop

New Member
Joined
May 30, 2018
Messages
43
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.
 

Forum statistics

Threads
1,089,320
Messages
5,407,563
Members
403,152
Latest member
Junaid Azhar

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top