Crosstab Date by Week

P

PetNetwork

I am accustomed to creating crosstab queries in Crystal Reports and am having
difficulties with obtaining the same group by week result with an Access
Crosstab Query.

Using the datepart function I got the week number and year from the field
[Date_Received]

Week Year A B C
1 2006 20 30 10
2 2006 5 7 3

I am trying to get the following output

Date A B C
1/1/2006 20 30 10
1/8/2006 5 7 3

Where the first Column is the first date of each week of the year.

Can anyone tell me the formula to use to obtain this result?

Thanks.



Currently I have
 
K

KARL DEWEY

This will display the week date. To adjust which day of the week you will
display as the "Week commencing date" change the number in the formula. The
'+2' puts the Monday date as first day of the week.

[Date_Received]-Format([Date_Received],"w")+2
 
Top