Count By week

[joe]

I have a table which has the Order #, name address and the date when the
order was placed.
I need to get a count of orders by week. My week is from Mon through Sun , I
also need the date of the Monday of the week

Example
Order # Name Address Date
1234 John 123 Main street 06/09/08
9889 fdlkjdfklj ljdljflfljdfl 06/10/08

The results I am looking for is like

Week Starting Count
)6/09/08 2


You guys have been of great help, thanks in advance for your help

 

[Wayne-I-M]

SELECT Count(TableName.DateField) AS CountOfDate,
Format(DateAdd("d",-DatePart("w",TableName![DateField],1)+2,TableName![DateField]),"dddd")
& " " &
DateAdd("d",-DatePart("w",TableName![DateField],1)+2,TableName![DateField])
AS WeekStarting
FROM TableName
GROUP BY
Format(DateAdd("d",-DatePart("w",TableName![DateField],1)+2,TableName![DateField]),"dddd")
& " " &
DateAdd("d",-DatePart("w",TableName![DateField],1)+2,TableName![DateField]);


Change TableName and DateField to the real names of the table and the field
containing the dates

This will give

Count 2 (or whatever)
Weekstarting - Monday ##/##/##

If you have problems post back