Update query not joined properly 1

[melaniecarr23]

I have an update query that will update if the fields have already been updated previously, but won't if it's a new record.

Does this not update fields in the plan_calculations table as long as the plan_calculations.costid = costs.cost_id AND plan_calculations.pid = today.plan_to_calculate?

Here is an example of my query:
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,
WHERE (((costs.cost_id)=[costid]) AND ((plan_calculations.plan_id)=[plan_to_calculate]));

I figure it has something to do with my joins, but I can't figure out what.

Can someone please help me make this work?

Thanks.

 

[lespaul]

you have to explicitly post the new record before it "really" exists. If it isn't posted there's nothing to update.

Leslie

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

 

[melaniecarr23]

So, when I do my append query I should populate each of the other fields in the table with 0s? This way, when I run the update query it will have something to update. That's IT? I could seriously kick myself. Thank you so much.

 

[lespaul]

depends, are you doing this in code? If so, after you run the APPEND query you have to post the record. If you search for Me.Dirty in the forum you can find an example of how to do it.


Leslie

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