Update query driving me nuts!

[melaniecarr23]

I thought I had it all figured out, but apparently not. I want to do an update query for table plan_calculations but no rows are showing up:

UPDATE costs, today, plans INNER JOIN plan_calculations ON plans.plan_id = plan_calculations.plan_id SET plan_calculations.plan_wks = DateDiff("ww",[start_date],[end_date]), plan_calculations.cur_yr_wks = DateDiff("ww",[start_date],"12-31"), plan_calculations.next_yr_wks = DateDiff("ww","12-31",[end_date]), plan_calculations.f1visits = [3xwks]*3, plan_calculations.f2visits = [2xwks]*2, plan_calculations.f3visits = [1xwks]*1, plan_calculations.planv = [f1visits]+[f2visits]+[f3visits], plan_calculations.curyv = IIf([cur_yr_wks]<([3xwks]+1),[cur_yr_wks]*3,IIf([cur_yr_wks]<([3xwks]+[2xwks]+1),[f1visits]+(([cur_yr_wks]-[3xwks])*2),IIf([cur_yr_wks]<([plan_wks]+1),[f1visits]+[f2visits]+([cur_yr_wks]-[3xwks]-[2xwks]),[planv])))
WHERE (((costs.cost_id)=[costid]) AND ((plans.plan_id)=[plan_to_calculate]));

Any help would be so greatly appreciated!

 

[rac2]

UPDATE costs, today, plans
INNER JOIN plan_calculations ON plans.plan_id = plan_calculations.plan_id

SET plan_calculations.plan_wks = DateDiff("ww",[start_date],[end_date]),

plan_calculations.cur_yr_wks = DateDiff("ww",[start_date],"12-31"),

plan_calculations.next_yr_wks = DateDiff("ww","12-31",[end_date]),

plan_calculations.f1visits = [3xwks]*3, plan_calculations.f2visits = [2xwks]*2, plan_calculations.f3visits = [1xwks]*1, plan_calculations.planv = [f1visits]+[f2visits]+[f3visits], 

plan_calculations.curyv = 
   IIf([cur_yr_wks]<([3xwks]+1),[cur_yr_wks]*3,
         IIf([cur_yr_wks]<([3xwks]+[2xwks]+1),[f1visits]+(([cur_yr_wks]-[3xwks])*2),
              IIf([cur_yr_wks]<([plan_wks]+1),[f1visits]+[f2visits]+([cur_yr_wks]-[3xwks]-[2xwks]),[planv])
         )
   )
WHERE (((costs.cost_id)=[costid]) 
  AND ((plans.plan_id)=[plan_to_calculate]));

Maybe you need to JOIN the tables costs and today to the other tables. Unless they have only one row for given costid and plan_id.

One technique for sorting out UPDATE issues is to write a SELECT statement with the same JOINs and WHERE clause and see what rows are retrieved.

SELECT plan_calculations.plan_wks , DateDiff("ww",[start_date],[end_date]),

plan_calculations.cur_yr_wks , DateDiff("ww",[start_date],"12-31"),

plan_calculations.next_yr_wks , DateDiff("ww","12-31",[end_date]),

plan_calculations.f1visits , [3xwks]*3, plan_calculations.f2visits , [2xwks]*2, plan_calculations.f3visits , [1xwks]*1, plan_calculations.planv , [f1visits]+[f2visits]+[f3visits], 

plan_calculations.curyv , 
   IIf([cur_yr_wks]<([3xwks]+1),[cur_yr_wks]*3,
         IIf([cur_yr_wks]<([3xwks]+[2xwks]+1),[f1visits]+(([cur_yr_wks]-[3xwks])*2),
              IIf([cur_yr_wks]<([plan_wks]+1),[f1visits]+[f2visits]+([cur_yr_wks]-[3xwks]-[2xwks]),[planv])
         )
   )


FROM costs, today, plans
INNER JOIN plan_calculations ON plans.plan_id = plan_calculations.plan_id


WHERE (((costs.cost_id)=[costid]) 
  AND ((plans.plan_id)=[plan_to_calculate]));

 

[melaniecarr23]

Okay, so the problem lies in the costid and planid associations somewhere, I just don't understand WHY? I have the following tables

plans (user enters basic plan information from a form)
plan_calculations (queries append a record and update this table with more advanced plan data)
costs (there are two records for costs, medicare, and cash)
today (includes the current date, month, and a field for the plan_id to be calculated [named plan_to_calculate])

plan_calculations contains the field costid, which would pull the appropriate field from the costs table.

plans and plan_calculations have a plan_id, which are related. There will be only 1 unique plan ID in each table

What am I missing that is not making this work?

 

[lespaul]

I think in order for the query to be updatable you have to have both related fields in the query. So for your CostID, you would need the one from plan_calculations AND Costs in order to create an updatable query. Do you have all the related fields in the query? PlanID from both Plans & Plan_Calculations, etc.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual