How do I create a weighted calculated field according to week?

[waters130]

I am trying to create a calculated field in Access that needs to have the following criteria.

I am doing a weighted average of sales for a forecast.

The previous 4 weeks sales qtys need to have a weight of 50%
The 4 weeks previous to that need to have a weight of 30%
and the The 4 weeks previous to that need to have a weight of 20%.

Is is possible to do this?

Thanks,

 

[SkipVought]

Select iif([DateField]>=Date()-28,.5,iif([DateField]>=Date()-28*2,.3,iif([DateField]>=Date()-28*3,.2,0)))*[Sales Qty]

Skip,

_[red][/red]

 

[waters130]

That helps give me a good idea of what to do, the criteria for the other other weights (other than the .5) need to look at weeks 5-8 and 9-12 not just the current date -28 days, how would I do that?

 

[SkipVought]

Did you not look at the nested iif statements...

Date()-28*2

Date()-28*3

Skip,

_[red][/red]

 

[waters130]

Thanks Skip, I appreciate all your help.

 

[waters130]

I didn't look close enough at the nested iif statements.