Update Query is updating multiple fields with duplicate data

[Nochor]

Hey all, I'm running an update query that's based on a select query (that runs some calculations). The update query is updating ALL the rows that should be updated with the information in the select query with data from the final row in the select query, and not on a per ID basis as I think I have it set-up to do. The data looks correct in the update query, but again it's not coming out right.



Here's the SQL for what I've written so far:

UPDATE [Customer_Data Query], Customer_Data INNER JOIN Baseline ON Customer_Data.ID = Baseline.ID SET Baseline.[Unit Hours] = [Customer_Data]![Dur_Days]*[Customer_Data]![Dur_Hours]*[Customer_Data]![Number_Units], Baseline.Availability = [Customer_Data]![Perceived_Avail], Baseline.[Hours Available] = [Customer_Data]![Dur_Days]*[Customer_Data]![Dur_Hours]*[Customer_Data]![Number_Units]*[Customer_Data]![Perceived_Avail], Baseline.[Down Hours] = [Customer_Data Query]![DownHrs], Baseline.MT = [Customer_Data]![MT_TR_OR_BF_Hours], Baseline.[Number Events] = [Customer_Data Query]![Event], Baseline.Rework = [Customer_Data]![React_Rework], Baseline.Utilization = [Customer_Data]![Utilization], Baseline.[Maintenance Cost] = [Customer_Data Query]![Maintenance Cost], Baseline.[PM Cost] = [Customer_Data Query]![PM Cost], Baseline.[CBM Cost] = [Customer_Data Query]![CBM Cost], Baseline.[Reactive Cost] = [Customer_Data Query]![Reactive Cost], Baseline.[Per Action PM] = [Customer_Data]![Sched_PM_Duration], Baseline.[Per Action CBM PM PM] = [Customer_Data]![Sched_CBM_Duration], Baseline.[Per Action CBM PM Field] = [Customer_Data]![Sched_CBM_Duration], Baseline.[Per Action Undetectable Response] = [Customer_Data]![Undet_Resp_Time], Baseline.[Per Action Undetectable Trouble] = [Customer_Data]![Undet_Troublshoot_Time], Baseline.[Per Action Undetectable Repair Field Actual] = [Customer_Data]![Undet_Field_Repair_Time], Baseline.[Per Action Undetectable Repair Field Log] = [Customer_Data]![Undet_Repair_Logistics_Time], Baseline.[Per Action Undetectable Repair Shop] = [Customer_Data]![Undet_Shop_Repair_Time], Baseline.[Per Action Detectable Response] = [Customer_Data]![Det_Response_Time], Baseline.[Per Action Detectable Trouble] = [Customer_Data]![Det_Troubleshoot_Time], Baseline.[Per Action Detectable Repair Field Actual] = [Customer_Data]![Det_Field_Repair_Time], Baseline.[Per Action Detectable Repair Field Log] = [Customer_Data]![Det_Repair_Logistics_Time], Baseline.[Per Action Detectable Repair Shop] = [Customer_Data]![Det_Shop_Repair_Time], Baseline.[Number Scheduled Events] = [Customer_Data Query]![NumEvent], Baseline.[Number PM Events] = [Customer_Data Query]![PMEVENTS], Baseline.[Number CBMPM Events] = [Customer_Data Query]![CBMPMEvent], Baseline.[Number Event CBMPM Field] = [Customer_Data Query]![EventCBMPMField], Baseline.[Number Event CBM PM PM Field] = [Customer_Data Query]![EventCBMOMPM], Baseline.[Number Events Reactive] = [Customer_Data Query]![NumEventReact], Baseline.[Number Events Undetectable Total] = [Customer_Data Query]![NumEventUndetFR], Baseline.[Number Events Undetectable] = [Customer_Data Query]![NumEventUndet], Baseline.[Number Events Undetectable False] = [Customer_Data Query]![NumEventUndetFalse], Baseline.[Number Events Undetectable Real] = [Customer_Data Query]![NumEventReal], Baseline.[Number Events Undetectable Response] = [Customer_Data Query]![NumEventResp], Baseline.[Number Events Undetectable Trouble] = [Customer_Data Query]![NumEventTrouble], Baseline.[Number Events Undetectable Repairs Field Actual] = [Customer_Data Query]![NumEventRepFielAct], Baseline.[Number Events Undetectable Repairs Field Logistic] = [Customer_Data Query]![NumEventRepLog], Baseline.[Number Events Undetectable Repairs Shop] = [Customer_Data Query]![NumEventRepShop], Baseline.[Number Events Detectable] = [Customer_Data Query]![NumEventDet], Baseline.[Number Events Detectable False] = [Customer_Data Query]![NumEventDetFalse], Baseline.[Number Events Detectable Real] = [Customer_Data Query]![NumEventDetReal], Baseline.[Number Events Detectable Response] = [Customer_Data Query]![NumEventDetResp], Baseline.[Number Events Detectable Trouble] = [Customer_Data Query]![NumEventDetTrouble], Baseline.[Number Events Detectable Repairs Field Actual] = [Customer_Data Query]![NumEventDetRepFieldAct], Baseline.[Number Events Detectable Repairs Field Logistic] = [Customer_Data Query]![NumEventDetRepFieldLog], Baseline.[Number Events Detectable Repairs Shop] = [Customer_Data Query]![NumEventDetShop], Baseline.[Number Events Undetectable Rework] = [Customer_Data Query]![NumEventUndetRework], Baseline.[Number Events Detectable Rework] = [Customer_Data Query]![NumDetRework], Baseline.[Time Scheduled] = [Customer_Data Query]![TotalTime], Baseline.[Time PM] = [Customer_Data Query]![TotalTimePM], Baseline.[Time CBM PM] = [Customer_Data Query]![TimeCBMPM], Baseline.[Time CBM PM PM] = [Customer_Data Query]![TimePM], Baseline.[Time CBM PM Field] = [Customer_Data Query]![TimeField], Baseline.[Time React] = [Customer_Data Query]![TimeReact], Baseline.[Time Reactive] = [Customer_Data Query]![TimeReactive], Baseline.[Time Undetectable] = [Customer_Data Query]![TimeUndetect], Baseline.[Time Undetectable False] = [Customer_Data Query]![TimeUndetectFalse], Baseline.[Time Undetectable Real] = [Customer_Data Query]![TimeUndetectReal], Baseline.[Time Undetectable Response] = [Customer_Data Query]![TimeUndetResp], Baseline.[Time Undetectable Trouble] = [Customer_Data Query]![TimeUndetTrbl], Baseline.[Time Undetectable Repairs Field Actual] = [Customer_Data Query]![TimeUndetRepFielAct], Baseline.[Time Undetectable Repairs Field Logistic] = [Customer_Data Query]![TimeUndetRepFielLog], Baseline.[Time Undetectable Repairs Shop] = [Customer_Data Query]![TimeUndetRepShop], Baseline.[Time Detectable] = [Customer_Data Query]![timedet], Baseline.[Time Detectable False] = [Customer_Data Query]![TimeDetFalse], Baseline.[Time Detectable Real] = [Customer_Data Query]![TimeDetReal], Baseline.[Time Detectable Response] = [Customer_Data Query]![TimeDetResp], Baseline.[Time Detectable Trouble] = [Customer_Data Query]![TimeDetTrouble], Baseline.[Time Detectable Repairs Field Actual] = [Customer_Data Query]![TimeDetRepFieldAct], Baseline.[Time Detectable Repairs Field Logistic] = [Customer_Data Query]![TimeDetRepFieldLog], Baseline.[Time Detectable Repairs Shop] = [Customer_Data Query]![TimeDetShop]

I've also tried to force the update to the proper row by adding a criteria based on ID.

Just in case it helps to have the select query, here it is:

SELECT Customer_Data.ID, Customer_Data.Data_Set_Version, Customer_Data.Number_Units, Customer_Data.Perceived_Avail, Customer_Data.MTTR_MTBF, Customer_Data.MT_TR_OR_BF_Hours, Customer_Data.Utilization, Customer_Data.Percent_Scheduled, Customer_Data.Sched_Percent_of_PM, Customer_Data.Sched_PM_Duration, Customer_Data.Sched_CBM_Duration, Customer_Data.Sched_CBM_From_PM, Customer_Data.React_Detect, Customer_Data.React_Rework, Customer_Data.React_False_Alarms, Customer_Data.React_Shop, Customer_Data.Dur_Days, Customer_Data.Dur_Hours, Customer_Data.Misc_Tons_Moved, Customer_Data.Misc_StandbyDelay_Per_Event, Customer_Data.Misc_Cycle_Interupt, Customer_Data.Misc_Cost_Per_Op_Hour, Customer_Data.Misc_Maint_Jobs_Per_Event, Customer_Data.Undet_Resp_Time, Customer_Data.Undet_Troublshoot_Time, Customer_Data.Undet_Repair_Logistics_Time, Customer_Data.Undet_Field_Repair_Time, Customer_Data.Undet_Shop_Repair_Time, Customer_Data.Det_Response_Time, Customer_Data.Det_Troubleshoot_Time, Customer_Data.Det_Repair_Logistics_Time, Customer_Data.Det_Field_Repair_Time, Customer_Data.Det_Shop_Repair_Time, [Dur_Days]*[Dur_Hours]*[Number_Units] AS UnitHrs, [UnitHrs]*[Perceived_Avail] AS HoursAvail, [UnitHrs]*(1-[Perceived_Avail]) AS DownHrs, [Percent_Scheduled]*[DownHrs]/([Sched_Percent_of_PM]*[Sched_PM_Duration]+(1-[Sched_Percent_of_PM])*([Sched_CBM_From_PM]*[Sched_CBM_Duration]+(1-[Sched_CBM_From_PM])*[Sched_CBM_Duration])) AS NumEvent, [Sched_Percent_of_PM]*[Percent_Scheduled]*[DownHrs]/([Sched_Percent_of_PM]*[Sched_PM_Duration]+(1-[Sched_Percent_of_PM])*([Sched_CBM_From_PM]*[Sched_CBM_Duration]+(1-[Sched_CBM_From_PM])*[Sched_CBM_Duration])) AS PMEVENTS, (1-[Sched_Percent_of_PM])*[Sched_CBM_From_PM]*[NumEvent]+(1-[Sched_Percent_of_PM])*(1-[Sched_CBM_From_PM])*[NumEvent] AS CBMPMEvent, (1-[Sched_Percent_of_PM])*(1-[Sched_CBM_From_PM])*[NumEvent] AS EventCBMPMField, (1-[Sched_Percent_of_PM])*([Sched_CBM_From_PM])*[NumEvent] AS EventCBMOMPM, IIf([MTTR_MTBF]='MTTR',[DownHrs]/[MT_TR_OR_BF_Hours],[HoursAvail]*[Utilization]/[MT_TR_OR_BF_Hours]) AS Event, ([Event]-([PMEvents]+[CBMPMEvent]))*(1-[React_Detect]) AS NumEventUndet, [HoursAvail]*[Utilization]*[Misc_Cost_Per_Op_Hour] AS [Maintenance Cost], [Maintenance Cost]/([PMEVENTS]+3*((1-[React_False_Alarms])*[NumEventUndet]+(1-[React_False_Alarms])*(([Event]-([PMEvents]+[CBMPMEvent]))*[React_Detect]))+[CBMPMEvent]*[Misc_Maint_Jobs_Per_Event]) AS [PM Cost], [PM Cost]*[Misc_Maint_Jobs_Per_Event] AS [CBM Cost], [PM Cost]*3 AS [Reactive Cost], [NumEventUndet]+[NumEventDet] AS NumEventReact, [NumEventUndet]*[React_False_Alarms] AS NumEventUndetFalse, [NumEventUndet]*(1-[React_False_Alarms]) AS NumEventReal, [NumEventUndetFalse]+[NumEventReal] AS NumEventUndetFR, ([React_False_Alarms])*[NumEventDet] AS NumEventDetFalse, [NumEventReal]*[React_Rework] AS NumEventUndetRework, [NumEventUndetFalse]+[NumEventReal] AS NumEventResp, [NumEventUndetFalse]+[NumEventReal] AS NumEventTrouble, (1-[React_Shop])*[NumEventReal] AS NumEventRepFielAct, [NumEventRepFielAct] AS NumEventRepLog, [NumEventReal]*[React_Shop] AS NumEventRepShop, ([Event]-([PMEvents]+[CBMPMEvent]))*([React_Detect]) AS NumEventDet, (1-[React_False_Alarms])*[NumEventDet] AS NumEventDetReal, [React_Shop]*[NumEventDetReal] AS NumEventDetShop, [NumEventDetReal]*[React_Rework] AS NumDetRework, [NumEventDetFalse]+[NumEventDetReal] AS NumEventDetResp, [NumEventDetFalse]+[NumEventDetReal] AS NumEventDetTrouble, [NumEventDetReal]*(1-[React_Shop]) AS NumEventDetRepFieldAct, [NumEventDetRepFieldAct] AS NumEventDetRepFieldLog, [DownHrs]*[Percent_Scheduled] AS TotalTime, (1-[Sched_Percent_of_PM])*[Sched_CBM_From_PM]*[NumEvent]*[Sched_CBM_Duration] AS TimePM, (1-[Sched_Percent_of_PM])*(1-[Sched_CBM_From_PM])*[NumEvent]*[Sched_CBM_Duration] AS TimeField, [TimePM]+[TimeField] AS TimeCBMPM, (1-[Percent_Scheduled])*[DownHrs] AS TimeReactive, [React_False_Alarms]*[NumEventUndet]*([Undet_Resp_Time]+[Undet_Troublshoot_Time]) AS TimeUndetectFalse, ((1-[React_Shop])*(1-[React_False_Alarms])*[NumEventUndet])*([Undet_Resp_Time]+[Undet_Troublshoot_Time]+[Undet_Field_Repair_Time]+[Undet_Repair_Logistics_Time])+(([React_Shop]*(1-[React_False_Alarms])*[NumEventUndet])*([Undet_Resp_Time]+[Undet_Troublshoot_Time]+[Undet_Shop_Repair_Time])) AS TimeUndetectReal, [TimeUndetectFalse]+[TimeUndetectReal] AS TimeUndetect, [NumEventUndetFR]*[Undet_Resp_Time] AS TimeUndetResp, [NumEventUndetFR]*[Undet_Troublshoot_Time] AS TimeUndetTrbl, (1-[React_Shop])*[NumEventReal]*[Undet_Field_Repair_Time] AS TimeUndetRepFielAct, (1-[React_Shop])*[NumEventReal]*[Undet_Repair_Logistics_Time] AS TimeUndetRepFielLog, [NumEventRepShop]*[Undet_Shop_Repair_Time] AS TimeUndetRepShop, [NumEventDet]*[React_False_Alarms]*([Det_Response_Time]+[Det_Troubleshoot_Time]) AS TimeDetFalse, [NumEventDetReal]*([Det_Response_Time]+[Det_Troubleshoot_Time])+[NumEventDetRepFieldAct]*([Det_Field_Repair_Time]+[Det_Repair_Logistics_Time])+[NumEventDetShop]*[Det_Shop_Repair_Time] AS TimeDetReal, [NumEventDetResp]*[Det_Response_Time] AS TimeDetResp, [NumEventDetTrouble]*[Det_Troubleshoot_Time] AS TimeDetTrouble, [NumEventDetRepFieldAct]*[Det_Field_Repair_Time] AS TimeDetRepFieldAct, [NumEventDetRepFieldAct]*[Det_Repair_Logistics_Time] AS TimeDetRepFieldLog, [NumEventDetShop]*[Det_Shop_Repair_Time] AS TimeDetShop, [PMEvents]*[Sched_PM_Duration] AS TotalTimePM, [TimeDetFalse]+[TimeDetReal] AS TimeDet, [TimeUndetect]+[TimeDet] AS TimeReact
FROM Customer_Data;


Thanks in advance for the help!

[rpeare]

I'm not even going to try to rebuild a database based on your SQL statements, but why are you recalculating anything in a table? Calculated values should be handled in queries or VBA without the need to involve update queries. Is there a specific reason you need to update the table? Can you provide a sample of your data and the queries you're trying to run that's providing a 'bad' answer.

Just make a copy of your database, take out everything that isn't directly related to your problem, put in some junk data that can duplicate your problem, please convert it to a version of Access prior to 2010 (2003 will likely get you the widest audience) then zip and upload the file to this site.

[Nochor]

DB.zip

I'm not even going to try to rebuild a database based on your SQL statements, but why are you recalculating anything in a table? Calculated values should be handled in queries or VBA without the need to involve update queries. Is there a specific reason you need to update the table? Can you provide a sample of your data and the queries you're trying to run that's providing a 'bad' answer.

Just make a copy of your database, take out everything that isn't directly related to your problem, put in some junk data that can duplicate your problem, please convert it to a version of Access prior to 2010 (2003 will likely get you the widest audience) then zip and upload the file to this site.

I've tried to strip out the crap that MS puts into the DB, but can't seem to find the way to remove the hidden tables so as to be able to save it as a prior version of access . As such I've taken the file and saved it in its 2010 format zipped and uploaded it. If someone guide me in removing the superfluous tables that MS puts in there, I'll remove those and upload an earlier format....

[rpeare]

Apologies but I can not read 2010 files. Perhaps someone else can help.