Easy question about using group function on date.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dan - 27 Jul 2007 16:06 GMT

I have a spreadsheet that has multiple dates in the feild, I would like to
just group the data together by month and do a count and a sum on two ther
feilds of data. I have tried to input MM in the format box which than shows
me just the month number but when I apply the group functions it still
recognizes the day because it doesn't group all of the month numbers
together. How do I fix this?

Reply to this Message

Graham R Seach - 27 Jul 2007 16:12 GMT

Dan,

Since you're talking about a spreadsheet, you might get more joy asking your
question in an Excel newsgrup. This one os for Microsoft Access, the
database management system.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

>I have a spreadsheet that has multiple dates in the feild, I would like to
> just group the data together by month and do a count and a sum on two ther
[quoted text clipped - 3 lines]
> recognizes the day because it doesn't group all of the month numbers
> together. How do I fix this?

Reply to this Message

Dan - 27 Jul 2007 16:28 GMT

Sorry I guess I should have explained in more detail. I am trying to run a
query in acces from a link table. I don't want to change any of the data in
my excel spreadsheet but want to manipulate it to what I need in access. So I
am asking for help on how to do this in my Query in access.

> Dan,
>
[quoted text clipped - 14 lines]
> > recognizes the day because it doesn't group all of the month numbers
> > together. How do I fix this?

Reply to this Message

John W. Vinson - 27 Jul 2007 19:48 GMT

>I have a spreadsheet that has multiple dates in the feild, I would like to
>just group the data together by month and do a count and a sum on two ther
>feilds of data. I have tried to input MM in the format box which than shows
>me just the month number but when I apply the group functions it still
>recognizes the day because it doesn't group all of the month numbers
>together. How do I fix this?

What you'll actually need to do is add a new column to the query. In a vacant
Field cell type either

MonthNo: Month([datefield])

if all of the dates are in one year, or

YearAndMonth: Format([datefield], "yyyymm")

if they span multiple years. Group By this field.

John W. Vinson [MVP]

Reply to this Message