Suggestions Needed

  • Thread starter Thread starter Heather
  • Start date Start date
H

Heather

I need to create a report that represents a rolling four
week total. So I have data that has start dates. Then
what I do is make the start dates end on a Saturday ( I
used DateAdd("d",7-Weekday([SCHSTART]),[SCHSTART])) works
fine. were I am stuck is on the design on how to roll the
weeks. So If I have 3/20/04, 3/27/04, 4/3/04, 4/10/04
the next week it needs to be 3/27/04, 4/3/04, 4/10/04,
4/17/04. The final report would look like this;

Employee 3/27/04, 4/3/04, 4/10/04, 4/17/04
John 100 50 60 70
Mary 99 15 15 15

Any suggestions would be really Appreciated.. THANKS
 
Using the where clause

WHERE DateAdd("d",7-Weekday(SCHSTART),SCHSTART) > DateAdd("d",7-Weekday(date()),date())-2

should only give you the last 4 weeks worth of data

Hope this help
Rowan
 
Back
Top