Group by Month

[Liz Hansen]

Hi,

I'm sure this one is an easy one, I just can't figure it out. I have
created a query looking at one table with Dates and Sales figures. The
dates are per Day in the table, but I want the query to group them by Month.

So for instance:

01/01/04 $10
01/02/04 $30
02/01/04 $40

Should be:

Jan-04 $40
Feb-04 $40

Thanks,

Liz

 

[Brendan Reynolds]

SELECT Format$([TestDate],"mmm-yy") AS MonthYear, Sum(tblTest.TestCurr) AS
TotalAmount
FROM tblTest
GROUP BY Format$([TestDate],"mmm-yy");

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Ken Snell [MVP]]

SELECT Format([Day], "mmm-yy") AS MonthGroup,
Sum([AmountField]) AS TotalAmt
FROM TableName
GROUP BY Format([Day], "mmm-yy");

 

[Liz Hansen]

Hi,

This works. Thanks.

However, another problem popped up. If I try to sort my new month grouping
it will sort alphabetically, not by "time"... So Apr-03 would come before
Jan-03.

Is there a way around this?

Thanks,

Liz,

SELECT Format$([TestDate],"mmm-yy") AS MonthYear, Sum(tblTest.TestCurr) AS
TotalAmount
FROM tblTest
GROUP BY Format$([TestDate],"mmm-yy");

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Hi,

I'm sure this one is an easy one, I just can't figure it out. I have
created a query looking at one table with Dates and Sales figures. The
dates are per Day in the table, but I want the query to group them by Month.

So for instance:

01/01/04 $10
01/02/04 $30
02/01/04 $40

Should be:

Jan-04 $40
Feb-04 $40

Thanks,

Liz

 

[Ken Snell [MVP]]

SELECT Format$([TestDate],"mmm-yy") AS MonthYear, Sum(tblTest.TestCurr) AS
TotalAmount
FROM tblTest
GROUP BY Format$([TestDate],"mmm-yy"), [TestDate]
ORDER BY [TestDate];


--

Ken Snell
<MS ACCESS MVP>

Hi,

This works. Thanks.

However, another problem popped up. If I try to sort my new month grouping
it will sort alphabetically, not by "time"... So Apr-03 would come before
Jan-03.

Is there a way around this?

Thanks,

Liz,

SELECT Format$([TestDate],"mmm-yy") AS MonthYear, Sum(tblTest.TestCurr) AS
TotalAmount
FROM tblTest
GROUP BY Format$([TestDate],"mmm-yy");

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Hi,

I'm sure this one is an easy one, I just can't figure it out. I have
created a query looking at one table with Dates and Sales figures. The
dates are per Day in the table, but I want the query to group them by Month.

So for instance:

01/01/04 $10
01/02/04 $30
02/01/04 $40

Should be:

Jan-04 $40
Feb-04 $40

Thanks,

Liz

 

[Michel Walsh]

Hi,

you probably meant

SELECT Format$([TestDate],"mmm-yy") AS MonthYear,
Sum(tblTest.TestCurr) AS TotalAmount
FROM tblTest
GROUP BY Format$([TestDate],"mmm-yy")
ORDER BY LAST(TestDate)


or

SELECT Format$([TestDate],"mmm-yy") AS MonthYear,
Sum(tblTest.TestCurr) AS TotalAmount
FROM tblTest
GROUP BY Format$([TestDate],"mmm-yy"), Format$([TestDate],"yy-mmm")
ORDER BY Format$([TestDate],"yy-mmm")



since we need to either aggregate (first example) or Group (second example)
on the expressions we wish to use, that is a technical requirement of total
queries, that is nice to add TestDate in the Groups, we could then re-use
it, but that would also produce no useful grouping (at least, not "by month"
anymore) if there are more than one single TestDate by desired group
(month-year).

The second example does not break, explode, previously existing groups, and
allow us to re-use the new expression in the sort; the first example,
probably more efficient, decided to keep a random value of TestDate, for
each group, and sort on that value, again, no group modified in itself.


Vanderghast, Access MVP

SELECT Format$([TestDate],"mmm-yy") AS MonthYear, Sum(tblTest.TestCurr) AS
TotalAmount
FROM tblTest
GROUP BY Format$([TestDate],"mmm-yy"), [TestDate]
ORDER BY [TestDate];


--

Ken Snell
<MS ACCESS MVP>

Hi,

This works. Thanks.

However, another problem popped up. If I try to sort my new month grouping
it will sort alphabetically, not by "time"... So Apr-03 would come before
Jan-03.

Is there a way around this?

Thanks,

Liz,

SELECT Format$([TestDate],"mmm-yy") AS MonthYear,

Sum(tblTest.TestCurr)

AS

TotalAmount
FROM tblTest
GROUP BY Format$([TestDate],"mmm-yy");

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Hi,

I'm sure this one is an easy one, I just can't figure it out. I have
created a query looking at one table with Dates and Sales figures. The
dates are per Day in the table, but I want the query to group them by
Month.

So for instance:

01/01/04 $10
01/02/04 $30
02/01/04 $40

Should be:

Jan-04 $40
Feb-04 $40

Thanks,

Liz

 

[Ken Snell [MVP]]

Thanks for the change, Michel.

--

Ken Snell
<MS ACCESS MVP>

Hi,

you probably meant

SELECT Format$([TestDate],"mmm-yy") AS MonthYear,
Sum(tblTest.TestCurr) AS TotalAmount
FROM tblTest
GROUP BY Format$([TestDate],"mmm-yy")
ORDER BY LAST(TestDate)


or

SELECT Format$([TestDate],"mmm-yy") AS MonthYear,
Sum(tblTest.TestCurr) AS TotalAmount
FROM tblTest
GROUP BY Format$([TestDate],"mmm-yy"), Format$([TestDate],"yy-mmm")
ORDER BY Format$([TestDate],"yy-mmm")



since we need to either aggregate (first example) or Group (second example)
on the expressions we wish to use, that is a technical requirement of total
queries, that is nice to add TestDate in the Groups, we could then re-use
it, but that would also produce no useful grouping (at least, not "by month"
anymore) if there are more than one single TestDate by desired group
(month-year).

The second example does not break, explode, previously existing groups, and
allow us to re-use the new expression in the sort; the first example,
probably more efficient, decided to keep a random value of TestDate, for
each group, and sort on that value, again, no group modified in itself.


Vanderghast, Access MVP

SELECT Format$([TestDate],"mmm-yy") AS MonthYear, Sum(tblTest.TestCurr) AS
TotalAmount
FROM tblTest
GROUP BY Format$([TestDate],"mmm-yy"), [TestDate]
ORDER BY [TestDate];


--

Ken Snell
<MS ACCESS MVP>

Hi,

This works. Thanks.

However, another problem popped up. If I try to sort my new month grouping
it will sort alphabetically, not by "time"... So Apr-03 would come before
Jan-03.

Is there a way around this?

Thanks,

Liz,


"Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message
SELECT Format$([TestDate],"mmm-yy") AS MonthYear,

Sum(tblTest.TestCurr)

AS
TotalAmount
FROM tblTest
GROUP BY Format$([TestDate],"mmm-yy");

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it

impossible
for

me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me

with

a