Update Query

[Jyme]

Im trying to update an ODBC linked table with values in a local table in access 97..it doesnt seem to like the from clause..yet if I remove it it asks what tmp_txplan_edit.[client id] is..like it doesnt see it as a table..well heres the statement:

UPDATE TxPlan SET txplan.[Client ID] = tmp_txplan_edit.[Client ID], txplan.[Problem ID] = tmp_txplan_edit.[Problem ID], txplan.RationaleForAdding = tmp_txplan_edit.[RationaleForAdding], txplan.AsEvidencedBy = tmp_txplan_edit.[AsEvidencedBy], txplan.[Staff ID] = tmp_txplan_edit.[Staff ID], txplan.[Problem start date] = tmp_txplan_edit.[Problem start date], txplan.[Problem target date] = tmp_txplan_edit.[Problem target date], txplan.[Problem end date] = tmp_txplan_edit.[Problem end date], txplan.Goal = tmp_txplan_edit.[Goal], txplan.Objective = tmp_txplan_edit.[Objective], txplan.[Base rating] = tmp_txplan_edit.[Base rating], txplan.[Target rating] = tmp_txplan_edit.[Target rating], txplan.[Treatment modality #1] = tmp_txplan_edit.[Treatment modality #1], txplan.Enrollment1 = tmp_txplan_edit.[Enrollment1], txplan.[Treatment modality #2] = tmp_txplan_edit.[Treatment modality #2], txplan.Enrollment2 = tmp_txplan_edit.[Enrollment2], txplan.[Treatment modality #3] = tmp_txplan_edit.[Treatment modality #3], txplan.Enrollment3 = tmp_txplan_edit.[Enrollment3], txplan.[Treatment modality #4] = tmp_txplan_edit.[Treatment modality #4], txplan.Enrollment4 = tmp_txplan_edit.[Enrollment4], txplan.[Treatment modality #5] = tmp_txplan_edit.[Treatment modality #5], txplan.Enrollment5 = tmp_txplan_edit.[Enrollment5], txplan.[InternalStaff#1] = tmp_txplan_edit.[InternalStaff#1], txplan.[InternalStaff#2] = tmp_txplan_edit.[InternalStaff#2], txplan.[InternalStaff#3] = tmp_txplan_edit.[InternalStaff#3], txplan.[InternalStaff#4] = tmp_txplan_edit.[InternalStaff#4], txplan.[InternalStaff#5] = tmp_txplan_edit.[InternalStaff#5], txplan.[ExternalStaff#1] = tmp_txplan_edit.[ExternalStaff#1], txplan.[ExternalStaff#2] = tmp_txplan_edit.[ExternalStaff#2], txplan.[ExternalStaff#3] = tmp_txplan_edit.[ExternalStaff#3], txplan.[ExternalStaff#4] = tmp_txplan_edit.[ExternalStaff#4], txplan.[ExternalStaff#5] = tmp_txplan_edit.[ExternalStaff#5], txplan.[Frequency#1] = tmp_txplan_edit.[Frequency#1], txplan.[Frequency#2] = tmp_txplan_edit.[Frequency#2], txplan.[Frequency#3] = tmp_txplan_edit.[Frequency#3], txplan.[Frequency#4] = tmp_txplan_edit.[Frequency#4], txplan.[Frequency#5] = tmp_txplan_edit.[Frequency#5], txplan.[StartDate#1] = tmp_txplan_edit.[StartDate#1], txplan.[StartDate#2] = tmp_txplan_edit.[StartDate#2], txplan.[StartDate#3] = tmp_txplan_edit.[StartDate#3], txplan.[StartDate#4] = tmp_txplan_edit.[StartDate#4], txplan.[StartDate#5] = tmp_txplan_edit.[StartDate#5], txplan.[TargetDate#1] = tmp_txplan_edit.[TargetDate#1], txplan.[TargetDate#2] = tmp_txplan_edit.[TargetDate#2], txplan.[TargetDate#3] = tmp_txplan_edit.[TargetDate#3], txplan.[TargetDate#4] = tmp_txplan_edit.[TargetDate#4], txplan.[TargetDate#5] = tmp_txplan_edit.[TargetDate#5], txplan.[EndDate#1] = tmp_txplan_edit.[EndDate#1], txplan.[EndDate#2] = tmp_txplan_edit.[EndDate#2], txplan.[EndDate#3] = tmp_txplan_edit.[EndDate#3], txplan.[EndDate#4] = tmp_txplan_edit.[EndDate#4], txplan.[EndDate#5] = tmp_txplan_edit.[EndDate#5], txplan.Interventions = tmp_txplan_edit.[Interventions], txplan.[Intervention#2] = tmp_txplan_edit.[Intervention#2], txplan.[Intervention#3] = tmp_txplan_edit.[Intervention#3], txplan.[Intervention#4] = tmp_txplan_edit.[Intervention#4], txplan.[Intervention#5] = tmp_txplan_edit.[Intervention#5], txplan.[Assessment strengths] = tmp_txplan_edit.[Assessment strengths], txplan.[Assessment weaknesses] = tmp_txplan_edit.[Assessment weaknesses], txplan.[Assessment comments] = tmp_txplan_edit.[Assessment comments], txplan.Status = tmp_txplan_edit.[Status], txplan.PrintThisProblem = tmp_txplan_edit.[PrintThisProblem], txplan.ActiveDate = tmp_txplan_edit.[ActiveDate], txplan.InactiveDate = tmp_txplan_edit.[InactiveDate], txplan.ReferDate = tmp_txplan_edit.[ReferDate], txplan.DeferDate = tmp_txplan_edit.[DeferDate], txplan.AchieveDate = tmp_txplan_edit.[AchieveDate], txplan.StaffIDStamp = tmp_txplan_edit.[StaffIDStamp], txplan.DateStamp = tmp_txplan_edit.[DateStamp], txplan.[TimeStamp] = tmp_txplan_edit.[TimeStamp],
txplan.LastModStaffID = tmp_txplan_edit.[LastModStaffID]
From txplan, tmp_txplan_edit
WHERE txplan.TxPlanProblemID = tmp_txplan_edit.txplanProblemID;

 

[PHV]

UPDATE TxPlan INNER JOIN tmp_txplan_edit ON txplan.TxPlanProblemID = tmp_txplan_edit.txplanProblemID
SET txplan.[Client ID] = tmp_txplan_edit.[Client ID], ..., txplan.LastModStaffID = tmp_txplan_edit.LastModStaffID;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886