Hello, I am working on a database to analyze weekly purchase results. With the goal being to determine the response rates for each promotion source that was used.

I have a table that contains PromoName, PromoCode, PromoQuantity, PromoSource and PromoDropDate. In another table I have imported 8 weeks worth of results and use the fields PromoCode and DatePurchased. An additional field called TransactionValue is auto assigned to the number value of 1.

I was able to create a query that looked at all the records with the same PromoDropDate and PromoSource field and then summed the values in the TransactionValue.

I would like to be able to see the results by week for 10 weeks but am not sure how to proceed. For example if the PromoDropDate is 1/10/08 I could look at the results based on DatePurchased being between 1/10/08 and 1/16/08, and then for 9 week periods after that.

I welcome any and all suggestions on the best way to approach this. thanks.

Use format([datefield], "WW") to convert the date to a week number

then create a group by query to group and count your results...

Is that what you are looking for?

...what I really want to do is have a calculated field called Week1Results that displays by PromoSource and DropDate. In this case PromoSource is mailing list names. I want the calculation in the Week1Results field to count the number of records that have a join date BETWEEN [DropDate] AND ([dropdate] + 7). I think this may be a question of syntax, which I am not familiar with yet.

Please help.

You lost me some place between what and yet...

I dont understand how you get and error, format(Datefield,"WW") should work, tho maybe you have to do it like so: format(Datefield;"WW")

Then add: "Week" & Format.. & "Results"

Then use this field in a crosstab query... that should do it I think... but again I feel a bit lost...

What I do not understand is where I apply the: format(Datefield,"WW")

Does this get entered as an expression in criteria? That is wher I had placed it. Doing that, when I tried to run the query I received an error.

What I do not understand is where I apply the: format(Datefield,"WW")

Does this get entered as an expression in criteria? That is wher I had placed it. Doing that, when I tried to run the query I received an error.

If you're doing it in the query. Create a new field and call it whatever you want. e.g. PromoDate:Format([datefield],"WW")