Help needed to resolve a query

  • Thread starter Thread starter Robert Gillard
  • Start date Start date
R

Robert Gillard

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
 
Hi,
Try something like this:
SELECT ProjectRefNo, Sum(TimeWorked), Max(DateWorked)
FROM tblTest
Group BY ProjectRefNo,Month(DateWorked);

Substitute your table name for tblTest
 
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.
 
Ed,
Thank you perfect solution, just what I needed.

Bob


Ed Warren said:
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.

Robert Gillard said:
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
Click to expand...
Click to expand...
 
Back
Top