Group by Week FBxiii 16.08.2006 13:20:01 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.

Re: Group by Week "Duane Hookom" <DuaneAtNoSpanHookomDotNet> 16.08.2006 13:30:43 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" <FBxiii[ at ]discussions.microsoft.com> wrote in message news:BA7F22A1-45C6-4CA6-AA4F-35DF70ABAF19[ at ]microsoft.com... [Quoted Text] > 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.

Re: Group by Week FBxiii 16.08.2006 13:43:02 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" wrote: [Quoted Text] > 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" <FBxiii[ at ]discussions.microsoft.com> wrote in message > news:BA7F22A1-45C6-4CA6-AA4F-35DF70ABAF19[ at ]microsoft.com... > > 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. > > >

Re: Group by Week "Duane Hookom" <DuaneAtNoSpanHookomDotNet> 16.08.2006 16:45:12 The Monday date is: DateAdd("d", -Weekday([DateField]) + 2, [DateField]) -- Duane Hookom MS Access MVP "FBxiii" <FBxiii[ at ]discussions.microsoft.com> wrote in message news:E4AE1C01-66F5-41A1-99A8-C9E203EC1655[ at ]microsoft.com... [Quoted Text] > 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" wrote: > >> 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" <FBxiii[ at ]discussions.microsoft.com> wrote in message >> news:BA7F22A1-45C6-4CA6-AA4F-35DF70ABAF19[ at ]microsoft.com... >> > 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. >> >> >>