Complex update query

[ritchie_h]

Hi I am a teacher trying to develop a access database programme to create lesson plans record and forecast etc...for myself and department.

I think some of it a bit over my head but i am trying my best but this part got me stuck any help would be much appreciated.

I have a form 'frmCreateLessonPlans'

To which I have the query parameters to generate the query for all lessons for the date period specified and insert it in the 'tblLessonPlanMain'.

For example in prose

I would like to insert all the lessons for Information Technology(IT) from February 1, 2016 to March 18th 2016.

For the Teacher Ritchie Hosein, subject Department: Maths, for Class: form 4 group 4.

Therefore the query should be able to generate a list of dates within the
range

when the teacher has IT from the table (tblsubjecttimetable) which is on Monday at 6 and 7 periods and Fridays 1 to 3 periods within that date range and then provide view for the user to confirm and then append into the lesson plans main table.

Output example:

see picture 1: append query results

Attached is also the database for how you think i can go about this.

any help would be much appreciated. thanks. The database is open for anyone else to use. I zip it because it was a bit to big.

 

[sneuberg]

Just wanted to let you to know that I'm looking at this. I don't see any problem generated the dates within the range. Using the WeekDay function and a little math you can calculate the next Monday (any week day) from a given date so you could do that in a loop until you got to the end of the range. If you want to do something for holidays you will need someway to identify those dates.

As you currently have this set up the code would that would append the records would be copying a lot of data from related tables into the tblLessonPlanMain rather than just the key as is normally done. For example the fields AcademecYearID AcademicStart, AdademicEnd, TermiName, StartTermDate, etc are all define by the TermID. Why are these additional fields in the tblLessonPlanMain? You can always get that information with a join.

 

[sneuberg]

Here's some code you might find useful. First a function to get the next weekday.

Private Function NextWeekDay(WkDay As Long, dte As Date) As Date
  
'vbSunday    1   Sunday (default used)
'vbMonday    2   Monday
'vbTuesday   3   Tuesday
'vbWednesday 4   Wednesday
'vbThursday  5   Thursday
'vbFriday    6   Friday
'vbSaturday  7   Saturday
  
NextWeekDay = dte + (WkDay - WeekDay(dte))

End Function

And here's a subroutine that uses it

Private Sub AppendRange(StartDate As Date, EndDate As Date)

Dim NextMonday As Date
Dim NextFriday As Date

NextMonday = NextWeekDay(vbMonday, StartDate)
NextFriday = NextWeekDay(vbFriday, StartDate)
'call procedure to append records here
Debug.Print "Monday: " & NextMonday & " Friday: " & NextFriday
Do While NextFriday + 7 <= EndDate
    NextMonday = NextMonday + 7
    NextFriday = NextFriday + 7
    'call procedure to append records here
    Debug.Print "Monday: " & NextMonday & " Friday: " & NextFriday
Loop

End Sub

To test this you would call it like:

Public Sub Test()

AppendRange #2/1/2016#, #3/18/2016#

End Sub