Results 1 to 6 of 6
  1. #1
    Bwilliamson is offline Novice
    Windows XP Access 2007
    Join Date
    May 2011
    Location
    Greenville, SC
    Posts
    8

    Calculated field based off another field

    Good morning,
    I am working on a cost system and could use some direction on a form I am building. The form is built from a query which is the following:



    Code:
     
    SELECT FB.[Acct Assmt WBS], FB.Group, FB.[Current Hrs], FB.[Current Wage], FB.[Current Budget $$], FB.Quantity, FB.[Unit Rate], FB.Desc, FA.UM, FA.[To Date Hours], FA.[To Date Wage], FA.[Actual Amount] AS [To Date $$], IIf(FB.[Current Hrs]=0,0,FA.[To Date Hours]/FB.[Current Hrs]) AS [% Expended Hours], IIf(FA.[To Date Wage]=0,0,FA.[To Date Wage]/FB.[Current Budget $$]) AS [% Expended $$], FB.Master_ID, FB.[Phy%Comp]
    FROM qry_Forecast_Budget_PRE AS FB LEFT JOIN qry_Forecast_Actuals_Pre AS FA ON FB.Master_ID=FA.Master_ID
    WHERE (((FB.Group)="LAB"));
    I need the user to be able to manually enter the value of the Physical % Complete field. After that value is entered I will add another field that calculates a value. This field will be [Earned Hours] and it will calculate FB.[Current Hrs] / 100 * FB.[Phy%Comp]. I can't seem to get this working using values from the query. Can anyone suggest a possible solution to this? I have several calcualted values that I need to work with for more additional fields after this. I think if I break this first one the rest will fall into place.

    Thanks! Brian

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2007
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    What is the problem - error message, wrong result, no result?

    What do you mean by 'After that value is entered I will add another field'? You aren't actually adding another field, you are calculating in a textbox?

    I usually construct expressions in textbox ControlSource by referencing other controls and not the RecordSource fields. So I will name the controls different from the field names but something meaningful.

    = [tbxCurrentHrs] / 100 * [tbxPhyPctComp]

    BTW, for future consideration, advise not to use spaces, special characters (@ # $ % & *), punctuation (underscore is exception) in any names nor use reserved words as a full name. If you do, must enclose in []. Instead of %, use Pct. Access will try to use the [] where appropriate but doesn't always get it, and in VBA coding, you will have to remember them.

  3. #3
    Bwilliamson is offline Novice
    Windows XP Access 2007
    Join Date
    May 2011
    Location
    Greenville, SC
    Posts
    8
    Thanks for replying. The first issue is updateing the value in the PhyPctComp field (changed it to follow correct naming conventions). When that is adjsted on the datasheet, all entries change to the value entered and then the new value is not calculated among the other fileds. They aren't updated because I haven't added any VB code to do so yet. Probably something like me.requery in the after update event.

    Here is the adjusted code of the values that I will be passing between the different fields:
    Code:
    SELECT FB.[Acct Assmt WBS], FB.Group, FB.[Current Hrs], FB.[Current Wage], FB.Quantity, FB.[Unit Rate], FB.Desc, FA.UM, FA.[To Date Hours], FA.[To Date Wage], FA.[Actual Amount] AS [To Date $$], IIf(FB.[Current Hrs]=0,0,FA.[To Date Hours]/FB.[Current Hrs]) AS [% Expended Hours], IIf(FA.[To Date Wage]=0,0,FA.[To Date Wage]/FB.[Current Budget $$]) AS [% Expended $$], FB.Master_ID, FB.PhyPctComp, [PhyPctComp]/100*[current hrs] AS [Earned Hours], [earned hours]/[to date hours] AS [To Date PF], [current Hrs]-[earned hours] AS [To Go PF], FA.[to date hours] AS [ETC MHRS], [etc MHRS] AS [ETC Wage Rate], [To Date $$] AS [ETC $], [To date hours]/[PhyPctComp]*100 AS [Forecasted MHRS], [ETC wage rate]/[forecasted MHRS] AS [Forecasted Wage Rate], [To Date $$]/[PhyPctComp]*100 AS [Forecasted $$], [Current hrs]-[etc wage rate] AS [Varience Hours], [current budget $$]-[forecasted MHRS] AS [Varience $$]
    FROM qry_Forecast_Budget_PRE AS FB LEFT JOIN qry_Forecast_Actuals_Pre AS FA ON FB.Master_ID = FA.Master_ID
    WHERE (((FB.Group)="LAB"));
    The main linchpin is the FB.PhyPctComp field which controls most of the calculations. For some reason it is not seeing each entry as an individual and is changing every one to match the adjusted value. Not sure why it is doing that.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2007
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Guess I am not understanding what you are trying to do. You have a form in continuous or datasheet view? That query you posted is the RecordSource for the form? All calcs are in this query? Are any calcs in textboxes of form? What version of Access? Calculated values are not saved to tables. Saving calculated values is contrary to principles of relational database - save raw data, do calcs in reports. If you must save a calculated value requires code to save the value to RecordSource field: Me!fieldname = expression

    If the field is in a table not part of the form RecordSource then need SQL action query to save the value.

    Just Me.Requery is not enough.

  5. #5
    Bwilliamson is offline Novice
    Windows XP Access 2007
    Join Date
    May 2011
    Location
    Greenville, SC
    Posts
    8
    Sorry for the confusion. I understand. Let's see if I can clear things up a bit. The form is in a datasheet view. The query is the recordsource for the form. My final calcs are in this form. The two querys feeding this have some sum calculations. There are calculations in some textboxes in the form that are dependant on some fields on the same form. Version 2007. I am not saving any calcualted values to my tables.

    Basically what I am trying to do is have the user enter an amount in the PhyPctComp field ( which is a raw field from a table, no calcualtions) and have the fields after it in the query recalculate the totals in them based from the amount entered by the user. They have requested this be in a datasheet view, not a regular form view.

    So say the user inputs a percent complete of 50%, it will then take the totals from other fields and recalulate $ and hours based from how much of the job is complete. Does that make sense?

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2007
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Okay, that might help me. Trying to picture where I have something set up with this kind of calculation dependency.

    Yes, I think then the Me.Requery or even maybe Me.Refresh should work. The trick is to figure out where to put it to trigger the action. First thought is the AfterUpdate event of the textbox bound to PhyPctComp field.

    The issue you might experience with Me.Requery or Me.Refresh in datasheet/continuous view is that the record position will move to the first record.

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

Similar Threads

  1. Make new field based on previous field's answer
    By VictoriaAlbert in forum Access
    Replies: 1
    Last Post: 04-11-2011, 09:54 PM
  2. Replies: 5
    Last Post: 01-20-2011, 11:36 PM
  3. Selecting a corresponding table field based on text field.
    By michaeljohnh in forum Programming
    Replies: 5
    Last Post: 10-08-2010, 10:33 AM
  4. Replies: 4
    Last Post: 01-19-2010, 05:36 AM
  5. Autofill field based on autonumber field
    By frevilla in forum Forms
    Replies: 1
    Last Post: 09-11-2009, 02:50 AM

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