GROUP BY

[RedHeadedMonster via AccessMonster.com]

I have a query where I am looking at forecasted vs. actual hours charged.

Hours are forecasted by months. And charged weekly. Both are pulled into
Access from other programs.

Heres the thing. The dates are pulled in as: 2/15/2008. Since I want to
look at it on a monthly basis, I've changed the format to Month Year (mmm
yyyy). When you look at the resulting query results, even with the Group by
there are still 4 entries for February, i.e., 4 entries that say Feb 2008
matching the full date. What can I do to make it see that they are all
February and I want a sum for just the month?

Heres the SQL for the current query:

SELECT DISTINCT [tblHours].Date, [tblHours].Name, [tblHours].Network, Sum(
[tblHours].Actuals) AS SumOfActuals, Sum([tblHours].Forecast) AS
SumOfForecast
FROM [tblHours]
GROUP BY [tblHours].Date, [tblHours].Name, [tblHours].Network
ORDER BY [tblHours].Name, [tblHours].Network;

Thanx
RHM

 

[Amy Blankenship]

I have a query where I am looking at forecasted vs. actual hours charged.

Hours are forecasted by months. And charged weekly. Both are pulled into
Access from other programs.

Heres the thing. The dates are pulled in as: 2/15/2008. Since I want to
look at it on a monthly basis, I've changed the format to Month Year (mmm
yyyy). When you look at the resulting query results, even with the Group
by
there are still 4 entries for February, i.e., 4 entries that say Feb 2008
matching the full date. What can I do to make it see that they are all
February and I want a sum for just the month?

Heres the SQL for the current query:

SELECT DISTINCT [tblHours].Date, [tblHours].Name, [tblHours].Network, Sum(
[tblHours].Actuals) AS SumOfActuals, Sum([tblHours].Forecast) AS
SumOfForecast
FROM [tblHours]
GROUP BY [tblHours].Date, [tblHours].Name, [tblHours].Network
ORDER BY [tblHours].Name, [tblHours].Network;

What about

GROUP BY Format([tblHours].Date, "mmmyyyy"), [tblHours].Name,
[tblHours].Network

 

[Marshall Barton]

I have a query where I am looking at forecasted vs. actual hours charged.

Hours are forecasted by months. And charged weekly. Both are pulled into
Access from other programs.

Heres the thing. The dates are pulled in as: 2/15/2008. Since I want to
look at it on a monthly basis, I've changed the format to Month Year (mmm
yyyy). When you look at the resulting query results, even with the Group by
there are still 4 entries for February, i.e., 4 entries that say Feb 2008
matching the full date. What can I do to make it see that they are all
February and I want a sum for just the month?

Heres the SQL for the current query:

SELECT DISTINCT [tblHours].Date, [tblHours].Name, [tblHours].Network, Sum(
[tblHours].Actuals) AS SumOfActuals, Sum([tblHours].Forecast) AS
SumOfForecast
FROM [tblHours]
GROUP BY [tblHours].Date, [tblHours].Name, [tblHours].Network
ORDER BY [tblHours].Name, [tblHours].Network;

You need to convert the dates to the same day in its month,
not just suppress the day of month in the date's display.

SELECT DateSerial(Year([Date]), Month([Date]), 1) As Date1,
[Name], Network,
Sum(Actuals) AS SumOfActuals,
Sum(Forecast) AS SumOfForecast
FROM tblHours
GROUP BY DateSerial(Year([Date]), Month([Date]), 1),
[Name], Network
ORDER BY [Name], Network

Your use of the reserved words Name and Date as field names
will cause problems sooner of later so you should change
those names before you have too many places that need to be
fixed.

 

[RedHeadedMonster via AccessMonster.com]

Thanx Marshall!
Worked like a charm.
I'll change the reserved words. Like I said it was brought in from other
programs. I didnt even think about them being reserved.

Have a nice Day!

RHM

I have a query where I am looking at forecasted vs. actual hours charged.

[quoted text clipped - 16 lines]

GROUP BY [tblHours].Date, [tblHours].Name, [tblHours].Network
ORDER BY [tblHours].Name, [tblHours].Network;

You need to convert the dates to the same day in its month,
not just suppress the day of month in the date's display.

SELECT DateSerial(Year([Date]), Month([Date]), 1) As Date1,
[Name], Network,
Sum(Actuals) AS SumOfActuals,
Sum(Forecast) AS SumOfForecast
FROM tblHours
GROUP BY DateSerial(Year([Date]), Month([Date]), 1),
[Name], Network
ORDER BY [Name], Network

Your use of the reserved words Name and Date as field names
will cause problems sooner of later so you should change
those names before you have too many places that need to be
fixed.