Day of week totals

[ddog171]

I have a table that has a start day and end day. I would like to get a total for all events occuring on the days of the week (mon, tues, etc.). The problem I am having is when the start day and end day are not the same I need to count that event for all the days in between also.

Example

(Start Day)/(End Day)
(Sat 1/1/2005)/(Sat 1/1/2005)
(Sat 1/1/2005)/(Sun 1/2/2005)
(Sat 1/1/2005)/(Sat 1/8/2005)
(Sun 1/2/2005)/(Mon 1/3/2005)
(Mon 1/3/2005)/(Mon 1/17/2005)
(Thurs 1/6/2005)/(Mon 1/10/2005)
(Thurs 1/6/2005)/(Thurs 1/6/2005)

Results:
Day/num events
Sun/5
Mon/3
Tue/2
Wed/2
Thu/4
Fri/3
Sat/5

Any help you can provide would be greatly appreciated.

 

[namliam]

Brrr... this one is a nice one...

Uhm...

Do events allways take <= 1 week? or do you need to allow for an event to take more than 1 week?

I think you need to do a little function in VBA to calculate the number of days with the period between Start and end dates...
Then use some Union queries to find out the number of days and stuff...

 

[raskew]

Hi -

Interesting problem. Think I may have a solution, but was unable to duplicate your sample totals, either programatically or manually. If that was just a sample to illustrate what you were after, this may do it, else some adjustment will be required.

Specially want to thank Sergeant and Cosmos75 for their help in getting me back on the right track when I ran up against a brick-wall on how to address the variables programatically.
See Here: http://www.access-programmers.co.uk/forums/showthread.php?t=110041

Here's the code. Store it in a new module, modify references to sDate and eDate to agree with your field names, and save it.

To call, from the debug (immediate) window:

Call DayCount("your table name") <enter>

Public Sub Daycount(ptable As String)
're: http://www.access-programmers.co.uk/forums/showthread.php?t=109896
Dim db      As Database
Dim rs      As Recordset
Dim dteHold As Date
Dim n       As Integer
Dim intHold As Integer
Dim strMsg  As String
Dim strHold As String
Dim aDow(6) As Variant

   Set db = CurrentDb
   Set rs = db.OpenRecordset(ptable)
   ' count occurences
   Do While Not rs.EOF
      dteHold = rs!sDate
      Do While dteHold <= rs!edate
         intHold = WeekDay(dteHold)
         aDow(intHold - 1) = aDow(intHold - 1) + 1
         dteHold = dteHold + 1
      Loop
      rs.MoveNext
   Loop
   
   ' prepare output
   For n = 0 To 6
      strHold = Choose(n + 1, "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat")
      strMsg = strMsg & strHold & " - " & vbTab & aDow(n) & vbCrLf
   Next n
   rs.Close
   db.Close
   Set db = Nothing
   
   ' display output
   MsgBox strMsg, vbOKOnly, "Totals are: "
End Sub

HTH - Bob

 

[ddog171]

Thanks

That was just a sample of what should come back. The events can take place over any number of days. I will give the program a try and let you know how it worked. Thanks again for your help and input.

 

[Gamezy]

As I posted in the VBA code forums, I too am having this problem. I need to calculate the ammount of days a person is working, if they are working say Monday and Wednesday, I need to calculate the ammount of these between two dates and then minus holidays from the known dates. Surprised this is so difficult to do in access!