Calculations in update query

[melaniecarr23]

I have a table named PLANS

In this table, there are some blank fields which need to be filled with an update query since they are based on calculations.

One of the fields is this:
plan_wks: DateDiff("ww",[start_date],[end_date])

When I run a select query with the calculation fields, it returns the correct values. However, when I change it to an update query, I get the following error:

'DateDiff("ww",[start_date],[end_date])' is not a valid name. Make sure that it does not include invalid characters or puctuation and that it is not too long.

WHY is this not working in the update query - it's driving me absolutely bonkers!

Thanks,
Melanie

 

[PHV]

First, it's often a bad idea to store derived/calculated values.
Next, can you please post the SQL code you tried ?

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

 

[melaniecarr23]

Here is the SQL view of my query (as a select query). The reason I was trying to do an update query instead was because I am trying to make a plan from information but there are too many calculations to run them. I tried breaking the queries up some, but am not finding the right solution. Someone suggested doing a series of update queries then running my reports/subreports from the table.

SELECT dClient.FirstName, dClient.LastName, DateDiff("ww",[start_date],[end_date]) AS plan_wks, DateDiff("ww",[start_date],"12-31") AS cur_yr_wks, DateDiff("ww","1-1",[end_date]) AS next_yr_wks, [3xwks]*3 AS f1visits, [2xwks]*2 AS f2visits, [1xwks]*1 AS f3visits, [f1visits]+[f2visits]+[f3visits] AS planv, 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]))) AS curyv
FROM dClient INNER JOIN plans ON dClient.ClientId = plans.pid
WHERE (((dClient.FirstName)=["enter first name"]) AND ((dClient.LastName)=["enter last name"]));

 

[lespaul]

if this query returns the information that you need, why not use it as the source?

As PHV stated above, storing calculated values normally breaks normalization rules.

Leslie

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

 

[PHV]

You may try something like this, provided the PLANS table have the plan_wks, cur_yr_wks, ... fields defined.
UPDATE plans INNER JOIN dClient ON plans.pid = dClient.ClientId
SET plan_wks = DateDiff("ww",[start_date],[end_date])
, cur_yr_wks = DateDiff("ww",[start_date],"12-31")
, next_yr_wks = DateDiff("ww","1-1",[end_date])
, f1visits = [3xwks]*3, f2visits = [2xwks]*2, f3visits=[1xwks]
, planv = [f1visits]+[f2visits]+[f3visits]
, 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 dClient.FirstName=["enter first name"] AND dClient.LastName=["enter last name"];

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

 

[melaniecarr23]

That is one of several queries, which sort of build upon each other. Maybe I'm looking at this in a too complex way, but I can't figure out how to simplify it. The problem is when I try to run the queries, I get the too complex problem.

Otherwise I would do that. I know it's not a great idea to populate the fields of a table with calculations, but they are all based on the data entered from the original table so it's not something that would get screwed up from running a query too many times - it would turn out the same over and over.

Any help would be great - even if just a way to get around the too complex part. I know access isn't meant for calculations, but I don't have much of a choice otherwise.

 

[melaniecarr23]

So if I have those fields already defined, even if with a 0 value, they should update based on the calculations I defined based on the plan information, correct?

I tried it, but am getting 0 for the value on each one, and it shouldn't be that way.

Melanie

 

[PHV]

Even plan_wks, cur_yr_wks and next_yr_wks ?
You may have to do the update step by step for the derived values.

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

 

[melaniecarr23]

Yes, I get a 0 value no matter what the field. Sorry it took so long to write back - I got pulled off post to cover the front desk again.

I got an append query to work for the first set of caluclations, but had to break the calculations up into several queries (since some calculated fields are based on others). Still having the calculation issue with the update query. Should I just forget about having the values in plan_calculations and just create a bunch of smaller tables and append instead? What's your opinion, considering the situation?

Thanks!