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.