Newbie question: Sum by months?

[JMF]

I have a simple time monitoring table. I work on three tasks every day, so
the table is just:

TheDate T1 T2 T3
------- -- -- --
6/23/06 1 3 4
.... etc.

So today I worked one hour on T1, 3 hours on T2, etc.

And so forth, every day.

Now I have several months worth of this.

I want to generate a report that says more or less:


Month T1 T2 T3
----- -- -- --
MARCH 23 13 22
APRIL 12 43 11
....

That is, it simply sums the number of hours in each month for each task.

I am convinced that this can't be a difficult task in Access, it must be its
"bread and butter" -- but as a newbie I don't know where to begin. Any quick
pointers?

Thanks,

John

 

[John Spencer]

You need to base your report on a Totals query.

SELECT Year(TheDate) as the Year
, Month(TheDate) as MonthNumber
, Format(TheDate,"mmmm") as MonthName
, Sum(T1) as T1Total
, Sum(T2) as T2Total
, Sum()T3) as T3Total
FROM [Your Table Name]
GROUP BY Year(TheDate), Month(TheDate), Format(TheDate,"mmmm")


In the Access query grid
Add your fields
Select View: Totals from the menu
Change Group by under T1, T2, and T3 to Sum
Edit TheDate to Format([TheDate],"mmmm")
Add TheDate again and edit it to Year(TheDate)
Add TheDate again and edit it to Month(TheDate)

Use that query as the basis of your report.

 

[M Skabialka]

You are better off keeping the data in more simple form, e.g.
Task EmployeeName DateWorked HoursWorked
T1 JMF 6/23/06 1
T2 JMF 6/23/06 3
T3 JMF 6/23/06 4
T1 JMF 6/24/06 5
T3 JMF 6/24/06 2

rather than list every task in one row in the table. Aside from everything
else, if you ever work on a new task you will have to add a new field to
your table and all your forms and reports.

 

[JMF]

Many thanks, John. Just the kind of direction I was hoping to get.

You need to base your report on a Totals query.

SELECT Year(TheDate) as the Year
, Month(TheDate) as MonthNumber
, Format(TheDate,"mmmm") as MonthName
, Sum(T1) as T1Total
, Sum(T2) as T2Total
, Sum()T3) as T3Total
FROM [Your Table Name]
GROUP BY Year(TheDate), Month(TheDate), Format(TheDate,"mmmm")


In the Access query grid
Add your fields
Select View: Totals from the menu
Change Group by under T1, T2, and T3 to Sum
Edit TheDate to Format([TheDate],"mmmm")
Add TheDate again and edit it to Year(TheDate)
Add TheDate again and edit it to Month(TheDate)

Use that query as the basis of your report.

I have a simple time monitoring table. I work on three tasks every day, so
the table is just:

TheDate T1 T2 T3
------- -- -- --
6/23/06 1 3 4
... etc.

So today I worked one hour on T1, 3 hours on T2, etc.

And so forth, every day.

Now I have several months worth of this.

I want to generate a report that says more or less:


Month T1 T2 T3
----- -- -- --
MARCH 23 13 22
APRIL 12 43 11
...

That is, it simply sums the number of hours in each month for each task.

I am convinced that this can't be a difficult task in Access, it must be
its "bread and butter" -- but as a newbie I don't know where to begin.
Any quick pointers?

Thanks,

John

 

[JMF]

You are absolutely right. In fact, my long-term view is to migrate to that
format. I have it in this format right now because I'm importing it from an
Excel table and wanted to at least learn how to deal with that before
getting more ambitious and migrating to the more flexible format.

And by the way, the reason I'm importing from Excel is because I still
haven't figured out a more flexible way to enter data directly in Access.
That is, more flexible than Excel, with its instant updates and sums and
totals etc. for a single month. Because I like that way of entering data on
a day-to-day basis. I figure that when I've understood all the possibilities
of Access, especially for advanced data entry through forms and the like,
I'll do that complete migration into Access and the flexible structure you
describe below.

Thanks,

John

 

[Marshall Barton]

I have a simple time monitoring table. I work on three tasks every day, so
the table is just:

TheDate T1 T2 T3
------- -- -- --
6/23/06 1 3 4
... etc.

So today I worked one hour on T1, 3 hours on T2, etc.

And so forth, every day.

Now I have several months worth of this.

I want to generate a report that says more or less:


Month T1 T2 T3
----- -- -- --
MARCH 23 13 22
APRIL 12 43 11
...

That is, it simply sums the number of hours in each month for each task.

I am convinced that this can't be a difficult task in Access, it must be its
"bread and butter" -- but as a newbie I don't know where to begin. Any quick
pointers?

Base your report on a Totals type query:

SELECT DateSerial(Year(thedate),Month(thedate),1) As
TheMonth, Sum(t1) As t1Total, Sum(t2) As t2Total, Sum(t3) As
t3Total
FROM thetable
GROUP BY DateSerial(Year(thedate),Month(thedate),1)

 

[JMF]

Base your report on a Totals type query:

SELECT DateSerial(Year(thedate),Month(thedate),1) As
TheMonth, Sum(t1) As t1Total, Sum(t2) As t2Total, Sum(t3) As
t3Total
FROM thetable
GROUP BY DateSerial(Year(thedate),Month(thedate),1)

Thanks, Marsh. I think the lesson is clear: study the Totals query type,
it's the one I'm looking for.

John