Query Problems

  • Thread starter Thread starter Mayra
  • Start date Start date
M

Mayra

I have created a summary query that totals the number of
calls for each month but I can't sort it by month because
if I do it sorts it in alphabetical order instead of by
month. Can you help? Could it be because it's a summary?

Month

August 2004
July 2004
June 2004
May 2004
September 2004
 
Hi Mayra,

The problem isn't that it's a Totals query. I suspect that, rather than
a standard Date/Time field in your table, you have a text field
containing the names of the months (hopefully NOT named "Month," as this
is a reserved word in Access...you'll see why in a sec). So of course,
if you sort on this field, Access sorts it alphabetically: April comes
before August; December comes before February, etc.. If you want your
results sorted in order of ascending month (Jan-Dec), you have to use
the month's number, not the text name. The easiest way to do this is to
use the MONTH() function on a Date/Time field:

SELECT MONTH([CallDate]) AS CallMonth, SUM([Calls]) AS NumCalls
FROM YourTable
GROUP BY CallDate
ORDER BY MONTH([CallDate]) ASC;

To reproduce exactly your example data, add a YEAR() function to the
query:

SELECT YEAR([CallDate]) AS ServiceYear, MONTH([CallDate]) AS CallMonth,
SUM([Calls]) AS NumCalls
FROM YourTable
GROUP BY CallDate
ORDER BY YEAR([CallDate]),MONTH([CallDate]) ASC;

This will sort calls by year (earliest to most recent), and then by
months within each year (1-12).

Use the FORMAT() function to display the month by name:

SELECT Year([CallDate]) AS ServiceYear, Format(Month([CallDate]),"mmmm")
AS CallMonth, SUM([Calls]) AS NumCalls
FROM YourTable
GROUP BY CallDate
ORDER BY Year([CallDate]), Format(Month([SampleDate]),"mmmm") ASC;

If your months are already "hard-coded" in your table, you could run a
quick series of Update queries to change the names to numbers.

hth,

LeAnne
 
I still can't seem to get it to work right. Below is an
example of how the query is set up and also an example of
my table. I do have the field in the query named Month
because Access named it that when I pulled up a summary by
month but on the table it is called Date of Activity.

Query:
(Month) (Total Calls) (Study Connected Calls)

August 2004 130 34
July 2004 907 236
June 2004 739 158
May 2004 919 167
September 2004 118 28

Table:
(Date of Activity) (Study Connected) (Study Voice Mail)
5/10/2004 7 6
5/11/2004 2 1
5/12/2004 4 3
5/13/2004 1 1
5/14/2004 5 0
5/17/2004 1 4
5/18/2004 0 0
-----Original Message-----
Hi Mayra,

The problem isn't that it's a Totals query. I suspect that, rather than
a standard Date/Time field in your table, you have a text field
containing the names of the months (hopefully NOT named "Month," as this
is a reserved word in Access...you'll see why in a sec). So of course,
if you sort on this field, Access sorts it alphabetically: April comes
before August; December comes before February, etc.. If you want your
results sorted in order of ascending month (Jan-Dec), you have to use
the month's number, not the text name. The easiest way to do this is to
use the MONTH() function on a Date/Time field:

SELECT MONTH([CallDate]) AS CallMonth, SUM([Calls]) AS NumCalls
FROM YourTable
GROUP BY CallDate
ORDER BY MONTH([CallDate]) ASC;

To reproduce exactly your example data, add a YEAR() function to the
query:

SELECT YEAR([CallDate]) AS ServiceYear, MONTH([CallDate]) AS CallMonth,
SUM([Calls]) AS NumCalls
FROM YourTable
GROUP BY CallDate
ORDER BY YEAR([CallDate]),MONTH([CallDate]) ASC;

This will sort calls by year (earliest to most recent), and then by
months within each year (1-12).

Use the FORMAT() function to display the month by name:

SELECT Year([CallDate]) AS ServiceYear, Format(Month ([CallDate]),"mmmm")
AS CallMonth, SUM([Calls]) AS NumCalls
FROM YourTable
GROUP BY CallDate
ORDER BY Year([CallDate]), Format(Month ([SampleDate]),"mmmm") ASC;

If your months are already "hard-coded" in your table, you could run a
quick series of Update queries to change the names to numbers.

hth,

LeAnne


I have created a summary query that totals the number of
calls for each month but I can't sort it by month because
if I do it sorts it in alphabetical order instead of by
month. Can you help? Could it be because it's a summary?

Month

August 2004
July 2004
June 2004
May 2004
September 2004
Click to expand...
.
Click to expand...
 
Hi Mayra,

What do you mean when say you "can't get it to work right?" Perhaps I'm
misunderstanding your problem. What exactly is it that you want the
query to do? What's it doing now that is "not right?" Please post the
SQL of the offending query.

LeAnne
I still can't seem to get it to work right. Below is an
example of how the query is set up and also an example of
my table. I do have the field in the query named Month
because Access named it that when I pulled up a summary by
month but on the table it is called Date of Activity.

Query:
(Month) (Total Calls) (Study Connected Calls)

August 2004 130 34
July 2004 907 236
June 2004 739 158
May 2004 919 167
September 2004 118 28

Table:
(Date of Activity) (Study Connected) (Study Voice Mail)
5/10/2004 7 6
5/11/2004 2 1
5/12/2004 4 3
5/13/2004 1 1
5/14/2004 5 0
5/17/2004 1 4
5/18/2004 0 0
-----Original Message-----
Hi Mayra,

The problem isn't that it's a Totals query. I suspect that, rather than
a standard Date/Time field in your table, you have a text field
containing the names of the months (hopefully NOT named "Month," as this
is a reserved word in Access...you'll see why in a sec). So of course,
if you sort on this field, Access sorts it alphabetically: April comes
before August; December comes before February, etc.. If you want your
results sorted in order of ascending month (Jan-Dec), you have to use
the month's number, not the text name. The easiest way to do this is to
use the MONTH() function on a Date/Time field:

SELECT MONTH([CallDate]) AS CallMonth, SUM([Calls]) AS NumCalls
FROM YourTable
GROUP BY CallDate
ORDER BY MONTH([CallDate]) ASC;

To reproduce exactly your example data, add a YEAR() function to the
query:

SELECT YEAR([CallDate]) AS ServiceYear, MONTH([CallDate]) AS CallMonth,
SUM([Calls]) AS NumCalls
FROM YourTable
GROUP BY CallDate
ORDER BY YEAR([CallDate]),MONTH([CallDate]) ASC;

This will sort calls by year (earliest to most recent), and then by
months within each year (1-12).

Use the FORMAT() function to display the month by name:

SELECT Year([CallDate]) AS ServiceYear, Format(Month ([CallDate]),"mmmm")
AS CallMonth, SUM([Calls]) AS NumCalls
FROM YourTable
GROUP BY CallDate
ORDER BY Year([CallDate]), Format(Month ([SampleDate]),"mmmm") ASC;

If your months are already "hard-coded" in your table, you could run a
quick series of Update queries to change the names to numbers.

hth,

LeAnne


I have created a summary query that totals the number of
calls for each month but I can't sort it by month because
if I do it sorts it in alphabetical order instead of by
month. Can you help? Could it be because it's a summary?

Month

August 2004
July 2004
June 2004
May 2004
September 2004
Click to expand...
.
Click to expand...
Click to expand...
 
Back
Top