Help with query

[dancat]

Hi I am running an update query to update 2 fields in a table, field 1 is an average of 2 of the other fields in table and the 2nd field is to update the week number number field.

This seems to working ok, but I would like week 1 to begin on monday 27th February 2012 not on the 1st january.

Is there away to achive this?

I have attached a screenshot of the query

The sql view of the query is

UPDATE tblMainData SET tblMainData.TtlAvg = ([totalin]+[totalout])/2, tblMainData.WeekNo = DatePart("ww",[rkddate]);

Thanks in advance for any help on this

 

[jdraw]

It is considered bad practice to store a calculated value in a table. The preferred/recommended approach is to store only raw facts. If you need to calculate values, do so in a query or queries.

If there are some "unique requirements" for storing the calculation, please describe them. I'm sure others will offer comments and options.

 

[dancat]

Hi still fairly new to access and best practices etc, so can only work with the limited knowledge I have i am afraid

But the end result of this is sum calculatations of the averages from the weeknumber field

see attachements for final query and results it produces

 

[dancat]

Decided to take a different approach to this using a week beginning field instead of week number, this way it eliminates having to change when the week numbers begin.

Thanks again for the help