Count for week ending.. in different fields

[froggiebeckie]

Morning All!

Boss-Man has a db (humongous) that (along with a zillion other things) tracks which specific tasks have been completed, who has done it and when did they do it.
All tasks will eventually be completed by all employees.

He's got a query that returns all of the dates on which each task has been completed, using each TASK name as a field.

The short version below shows the TASK1 was completed on 1/1/07, 2/7/06 and 2/17/07, TASK2 was completed on 01/05/07,by 2 people on 2/15/07 and again on 02/28/07 and so on.

TASK1 ***** TASK 2 ***** TASK 3 ***** TASK 4
1/1/2007 *** 1/5/2007 *** 1/13/2007 *** 1/2/2007
2/7/2006 *** 2/15/2007 *** 1/7/2006 *** 1/15/2007
********** 2/15/2007
2/17/2007 ******
********** 2/28/2007 ********* 2/28/2006

What he would like to do is be able to show how many tasks were completed in any given week, such as:

WEEK ENDING # TASKS
01/06/07 3
01/13/07 1
02/17/07 2

Looks to me like you'd first have to identify the possible WeekEnding dates and then try to total across all the fields, but I've not got a clue how you'd do that.

Is there an easy way to "query the query", work with Week Ending on Saturday, and get the info he's looking for?

Thanks,
BeckieO

 

[namliam]

date() - weekday(Date(),1) + 7 + 1

Will retrieve the end date for this week. i.e. Januari 21st. Remove the +1 to have the week end on saturday.
Substitute Date() for your date field and you should be done.

Good luck!