Group by Week

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi.

I have a dataset that includes Reference Number & Date. The dates range
from 01/12/05 to 31/05/06 and this needs to be broken down by week in the
form of a count of reference numbers, by date.

Is this possible?

Cheers,
Steve.
 
SELECT Format([DateField],"yyyy ww") as YrWk, Count(*) as NumOf
FROM [a datasheet]
GROUP BY Format([DateField],"yyyy ww")
ORDER BY Format([DateField],"yyyy ww");
 
Thanks for the reply.

That works fine. Now, can I get the query to show the date for the Monday
in each week? i.e. "Week Beginning 01/05/06"

Cheers,
Steve.


Duane Hookom said:
SELECT Format([DateField],"yyyy ww") as YrWk, Count(*) as NumOf
FROM [a datasheet]
GROUP BY Format([DateField],"yyyy ww")
ORDER BY Format([DateField],"yyyy ww");


--
Duane Hookom
MS Access MVP

FBxiii said:
Hi.

I have a dataset that includes Reference Number & Date. The dates range
from 01/12/05 to 31/05/06 and this needs to be broken down by week in the
form of a count of reference numbers, by date.

Is this possible?

Cheers,
Steve.
Click to expand...
Click to expand...
 
The Monday date is:
DateAdd("d", -Weekday([DateField]) + 2, [DateField])

--
Duane Hookom
MS Access MVP

FBxiii said:
Thanks for the reply.

That works fine. Now, can I get the query to show the date for the Monday
in each week? i.e. "Week Beginning 01/05/06"

Cheers,
Steve.


Duane Hookom said:
SELECT Format([DateField],"yyyy ww") as YrWk, Count(*) as NumOf
FROM [a datasheet]
GROUP BY Format([DateField],"yyyy ww")
ORDER BY Format([DateField],"yyyy ww");


--
Duane Hookom
MS Access MVP

FBxiii said:
Hi.

I have a dataset that includes Reference Number & Date. The dates
range
from 01/12/05 to 31/05/06 and this needs to be broken down by week in
the
form of a count of reference numbers, by date.

Is this possible?

Cheers,
Steve.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top