Help needed to resolve a query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Robert Gillard - 18 Apr 2005 20:44 GMT

I have a table with four fields:-
RefNo - autonumber
Project RefNo - The customers ref. no
TimeWorked - Although called "time" it is in amounts of .25, so 1hour 25mins
is input to the nearest quarter and becomes 1.50. (so it is a simple number
field.)
DateWorked - Date above work carried out.

So over a two month period the table may look like (assuming we just have
one project no 17)

1 17 2.50 01/3/2005
2 17 1.00 25/03/2005
3 17 1.00 29/03/2005
4 17 3.50 05/04/2005
5 17 2.00 18/04/2005

What I need to have after querying the table is the total time per project
per month and the last date the project was worked on in each month. So with
the above example the resulting query should give

17 4.50 29/03/2005
17 5.50 18/03/2005

In reality of course the table will contain details of over 40 projects
worked on per month.

Could anybody point me in the right direction with this please.

Bob

Reply to this Message

dan artuso - 18 Apr 2005 21:39 GMT

Hi,
Try something like this:
SELECT ProjectRefNo, Sum(TimeWorked), Max(DateWorked)
FROM tblTest
Group BY ProjectRefNo,Month(DateWorked);

Substitute your table name for tblTest

Signature

HTH
Dan Artuso, Access MVP

>I have a table with four fields:-
> RefNo - autonumber
[quoted text clipped - 29 lines]
>
> Bob

Reply to this Message

Ed Warren - 18 Apr 2005 21:48 GMT

try this query (open the query and select sql view and paste this in, change
"Table1" to your table name)

SELECT Table1.ProjectRefNo, Sum(Table1.TimeWorked) AS TotalTimeWorked,
Max(Table1.DateWorked) AS LastDateWorked
FROM Table1
GROUP BY Table1.ProjectRefNo, Month([dateWorked]), Year([dateWorked])
ORDER BY Max(Table1.DateWorked);

note: the Group by includes Month([dateWorked]), this will group by the
month, then to handle the case where you have more than one year (dec -jan)
you need the year as a group.

Ed Warren.

>I have a table with four fields:-
> RefNo - autonumber
[quoted text clipped - 29 lines]
>
> Bob

Reply to this Message

Robert Gillard - 18 Apr 2005 23:34 GMT

Ed,
Thank you perfect solution, just what I needed.

Bob

> try this query (open the query and select sql view and paste this in, change
> "Table1" to your table name)
[quoted text clipped - 44 lines]
> >
> > Bob

Reply to this Message