Monthly statistics

[Cereldine]

I will try to explain my problem as best i can and would appreciate any thoughts other people have on it, it is surely similar in some degree to someone elses previous work!

I need to produce management information on a monthly basis, one example of this type of work are an employees one to ones.

table121 contains following fields, ID scheduledDate CompletedDate Completed(yes/no)

My report/query needs to group records by the month (which i do through formatting date fields to display mm/yy), count the number of scheduled one2ones, count the number of completed one2ones, display a %.

I have played around and got this to work using querys with grouping and sums.

My problem is if the schedule date and the completed date are in different months then all of the statistics become out of sync, particualy when there are more appraisals taking place than scheduled.
Any ideas?

 

[Matt Greatorex]

Would it be an option to check if the end date was within, say, 30 days of the start date, as opposed to looking for them both to be in the same calendar month?

Alternatively, to cover both options, you could say 'in the same calendar month' OR 'within 30 days of each other'.

 

[Cereldine]

Yeah it has to be within the same month, perhaps i should be looking at fufilling both criteria however, will investigate now