summary report

[Joanna_gr]

well here I am, stuck again! I need to create a summary reprt which includes a PersonName and a TotalHours fields (Hours from the beginning of theyear till now). I managed to group People and used =Sum[TotalHours] function to summarize field TotalHours. My problem is that next to each name with the total hours i need to display a sum of hours of the last month. maybe it has to do with the query but this moment I can't thing anything ... Help please!

 

[Andrew Fergus]

Hi Joanna
In your underlying query, you could use something like the following SQL (In your query design, click View -> SQL View) to calculate the total hours since the start of the year, as well as the hours for the last completed month :

SELECT tablename.name, Sum(tablename.hours) AS SumOfhours, Sum(IIf([date]>= DateAdd("m",-1,DateAdd("d",-Day(Date())+1,Date())) And [date] <=DateAdd("d",-Day(Date()),Date()), [hours],0)) AS Last_Month
FROM tablename
WHERE (((tablename.date)>=#01/01/2005#))
GROUP BY tablename.name;

This will calculate the hours for the previous month for each employee and put it into a field called 'Last_Month' which you can then add to your report. If this isn't quite right, post a reply. Note that you will need to manually change the criteria in January 2006 for next year. You might not be able to use this query for both reports (per your previous post) - so you may need one query for each report.
HTH, Andrew.

 

[Joanna_gr]

That works perfect. I did what I wanted to and even more!!! Thanks. A million thanks