Group by date (month & week) in query

  • Thread starter Thread starter Cam
  • Start date Start date
C

Cam

Hello,

I have a query with the following data.
completion date, part category, avgLT, countOrder

Completion date in format of m/dd/yyyy and there are over thousand of records.

I would like to format it so that the completion date is grouped by month
(add up all the avgLT and countOrder fields in that month and then another by
week. Is there a way to do that in query? Thanks
 
Cam,

How do you want the week defined? ...Mon-Fri? ...Mon-Sun? ...Sun-Sat?
....week of the year?

What do you want in this "Week" column? ...Just a date (if so, date of
which day of the week)? ...Week 1, Week 2? ...Week of {Date} (if so, date
of which day)? ...Week # of 52?

To group by months, you can do something like this:
SELECT DateSerial(Year([Completion Date]),Month([Completion Date]),1) as
FirstDayOfMonth, Sum([avgLT]) as [avgLT], Sum([countOrder]) as [countOrder]
FROM [YourTableName]
GROUP BY DateSerial(Year([Completion Date]),Month([Completion Date]),1)

(did not test this, just typed this in from memory. Hopefully it will work)

Make sure the change [YourTableName] to the name of your table.

This query creates a new column called FirstDayOfMonth. It then sets this
new column to the first day of the month of the date in "Completion Date".
Then it groups the FirstDayOfMonth column so there is only one record for
the first day of the month, and it counts avgLT and countOrder.


HTH,

Conan
 
Hello Conan,

THe week is defined as Monday through Sunday.

Conan Kelly said:
Cam,

How do you want the week defined? ...Mon-Fri? ...Mon-Sun? ...Sun-Sat?
....week of the year?

What do you want in this "Week" column? ...Just a date (if so, date of
which day of the week)? ...Week 1, Week 2? ...Week of {Date} (if so, date
of which day)? ...Week # of 52?

To group by months, you can do something like this:
SELECT DateSerial(Year([Completion Date]),Month([Completion Date]),1) as
FirstDayOfMonth, Sum([avgLT]) as [avgLT], Sum([countOrder]) as [countOrder]
FROM [YourTableName]
GROUP BY DateSerial(Year([Completion Date]),Month([Completion Date]),1)

(did not test this, just typed this in from memory. Hopefully it will work)

Make sure the change [YourTableName] to the name of your table.

This query creates a new column called FirstDayOfMonth. It then sets this
new column to the first day of the month of the date in "Completion Date".
Then it groups the FirstDayOfMonth column so there is only one record for
the first day of the month, and it counts avgLT and countOrder.


HTH,

Conan





Cam said:
Hello,

I have a query with the following data.
completion date, part category, avgLT, countOrder

Completion date in format of m/dd/yyyy and there are over thousand of
records.

I would like to format it so that the completion date is grouped by month
(add up all the avgLT and countOrder fields in that month and then another
by
week. Is there a way to do that in query? Thanks
Click to expand...
Click to expand...
 
Cam,

Sorry for taking so long to get back to you.

Try this:

SELECT IIf(Weekday([Completion Date])>1 And Weekday([Completion
Date]<8),DateAdd("d",(-Weekday([Completion Date]))+2,[Completion
Date]),DateAdd("d",-6,[Completion Date])) as [WeekOf], Sum([avgLT]) as
[avgLT], Sum([countOrder]) as [countOrder]
FROM [YourTableName]
GROUP BY IIf(Weekday([Completion Date])>1 And Weekday([Completion
Date]<8),DateAdd("d",(-Weekday([Completion Date]))+2,[Completion
Date]),DateAdd("d",-6,[Completion Date]))


(NOTE that extra line breaks have been added my my news reader program.
Everything before "FROM" should be on one line and everything after "GROUB
BY" should be on one line)

I think that this will work for you. I have not tested it.

HTH,

Conan




Cam said:
Hello Conan,

THe week is defined as Monday through Sunday.

Conan Kelly said:
Cam,

How do you want the week defined? ...Mon-Fri? ...Mon-Sun? ...Sun-Sat?
....week of the year?

What do you want in this "Week" column? ...Just a date (if so, date of
which day of the week)? ...Week 1, Week 2? ...Week of {Date} (if so,
date
of which day)? ...Week # of 52?

To group by months, you can do something like this:
SELECT DateSerial(Year([Completion Date]),Month([Completion Date]),1) as
FirstDayOfMonth, Sum([avgLT]) as [avgLT], Sum([countOrder]) as
[countOrder]
FROM [YourTableName]
GROUP BY DateSerial(Year([Completion Date]),Month([Completion Date]),1)

(did not test this, just typed this in from memory. Hopefully it will
work)

Make sure the change [YourTableName] to the name of your table.

This query creates a new column called FirstDayOfMonth. It then sets
this
new column to the first day of the month of the date in "Completion
Date".
Then it groups the FirstDayOfMonth column so there is only one record for
the first day of the month, and it counts avgLT and countOrder.


HTH,

Conan





Cam said:
Hello,

I have a query with the following data.
completion date, part category, avgLT, countOrder

Completion date in format of m/dd/yyyy and there are over thousand of
records.

I would like to format it so that the completion date is grouped by
month
(add up all the avgLT and countOrder fields in that month and then
another
by
week. Is there a way to do that in query? Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top