Hi there,
I am working on a query where i need to calculate sickness. I have got a complicated one here. So here it goes

select sum(sickness)/headcount.totalheads as [Total Sickness heads] from billing headcount;
And then i group it weekly monthly etc.
The problem is the headcount changes every week and if i give a screen to increase or decrease the heads then it will change the past figures.
I want a way were any change to the table would not affect the previous figures If did say a static monthly report.

So for eg manager A had 10 heads in his team for week 1 and 5 sickness
Total sickness heads would be 0.5.
in week 2 if he had 12 heads and no sickness the previous weeks 0.5 figure shouldnt change.
Presently the headcounts are looked on a weekly basis.
Any ideas welcome. cheers.

I may not be completely understanding what you are attempting to do, but, if you have a date field or even a Week field that is populated for each of the reporting time frames, you can just restrict your query to show the count for a specific week or time frame not the entire table.

HTH

Hello I am reply after a long time now. There are some changes to the query however the requirement is what you have updated mr. b. Could give an example on restricting a query. That would be very helpful thanks in advance.

All that is required to restrict a query is, in the QBE window, add some criteria to the derired field on the criteria line.

One way to do this is to recognize that your aggregate query (the sum of something divided by something else) applies to a limited time-frame. So... include in your query a date-part function that determines a week number or something like that as a query-computed field. Then do a GROUP BY on that week number.

Could put some more light Doc man, a eg query would be appreciated.
My present defination for the query is

No of heads*7.5*number of days for that month= Total number of Hours for that month -> I can get this figure in advance for that month
Total Sickness = sum(Total Hours Lost) -> That figure will be updated from the data entry sheet.

Total sickness for reporting = Total number of hours lost/Total nos of hours for that month.

I am gonna sit and work on this query. But I would appreciate if a eg of somekind came in. cheers.

I have got it. I used to queries and merged it into one and used manager's name dyanamically to call data. However i am not getting the months in order.
i get april before february how do i get them in a correct order. I have tried ordering it by ascending order. that doesnt help. any ideas guys.

Convert your months field to show the number of the month and not the name. Then when you display that number in the report, convert it back to show the Name of the month.

Could you give a example query. I used the month format to convert date to show month format((weekdate),mmmm yyy) and used the month(weekdate)&year(weekdate) ascending.

Now I am able to get the data on a ascending order on a datasheet. But If i try to do a pivot chart it goes back to april feb and so on.

Any thoughts mr. b

In your query place the following in a blank Field row:

MonthVal: DatePart("m",[YourTableName]![DateFieldName])

Change "YourTableName" to the name of your table and "DateFieldName" to the name of the field from which you need to extract the month value.

HTH

Thank you so very much that did work and now i am able to get the data in order on the pivot chart. cheers.