I have start_date and end_date and need to track all scheduled dates 1

[caburky]

I have a start date, end date, and length (hours) for a course and need to track the dates an instructor should be scheduled. As an example if we have a course that starts on July 11 2007, and ends on July 13 2007, I would expect 3 intructors required over the days of the course. This same course may be taught again July 20, 2007 ending July 21, 2007. I would like to be able to run a report that will show the entire instructor schedule by date for the course.

There will be multiple courses being taught during this period and need to display the same scenario for each record.

Data
Course Start_Date End_Date Length
MM004 7/11 7/13 24
MM004 7/20 7/21 16

An example of the report output they are requesting:
CourseName 7/9 7/10 7/11 7/12 7/13 ....7/20 7/21
MM004 1 1 1 1 1

I'm NEW!![/color red] to VBA and would greatly appreciate any help!

 

[PHV]

I'm NEW!! to VBA
I don't see any VBA issue here but probably simply SQL.
With the help of an integers or days table for enumerating the scheduled dates you may play with a Crosstab query.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[caburky]

Thanks PHV, I appreciate you checking this out for me.

I had originally thought of using the crosstab, but I had no idea how to enumerate the schedule dates. It was then suggested to me to possibly use a select case to determine a MondayDate and then nest statements within that for the length of course. It sounded pretty complicated to me, and thus the post.

Again, I'm new to Access is this worth a post on the SQL forum? Or am I way off base with what I'm attempting?

Appreciate your time!

 

[maxhugen]

This is a case where a temp table could be useful. A function would go through your (CourseSchedule) table, and add entries to the temp table for each day that it runs. This table would then be used as the basis of a crosstab query.

Here's an example. Note that you will need to change the name I've used for your table (CourseSchedule) to its actual name.

Step 1

Create a temp table:

[tt]table CourseDay
Course
CourseDay[/tt]

Step 2

Create a function to populate the temp table:

Public Function CreateCourseDays()
On Error GoTo err_CreateCourseDays
Dim db as DAO.database
Dim rsCourse as DAO.recordset
Dim rsTemp as DAO.recordset
Dim DayToAdd as Date
Dim s$

    [green]' clear the temp table[/green]
    s="DELETE * FROM CourseSchedule"
    db.Execute s

    [green]' get the course data[/green]
    Set rsCourse = db.OpenRecordset("CourseSchedule")
    
    [green]' open the temp table[/green]
    Set rsTemp = db.OpenRecordset("CourseDay")

        [green]' loop through the course records[/green]
        While not rsCourse.EOF

            DayToAdd = rsCourse!Start_Date
            While DayToAdd <= rsCourse!End_Date
                [green]' add a record for every day of the course[/green]
                rsTemp.AddNew
                    rsTemp!Course=rsCourse!Course
                    rsTemp!CourseDay=DayToAdd
                rsTemp.Update
                DayToAdd = DayToAdd+1
            Wend

        wend

    ' close the recordsets
    rsTemp.close
    rsCourse.close

exit_CreateCourseDays:
    Set db=nothing: set rsCourse=nothing: set rsTemp=nothing
    exit function
err_CreateCourseDays:
    msgbox err & ": " & err.description
    resume exit_CreateCourseDays
End function

Step 3

Create a crosstab query for your report, using the temp table:

[tt]TRANSFORM Sum(1) AS RV
SELECT CourseDay.Course
FROM CourseDay
GROUP BY CourseDay.Course
PIVOT Format([CourseDay],"m/dd");[/tt]

Step 4

Run the function before running the report. Here, I've added a button 'cmdPreview' to a form. It's OnClick event will look like:

Private Sub cmdPreview_Click()
On Error GoTo err_cmdPreview_Click

    [green]' run the function that populates the temp table[/green]
    call CreateCourseDays()

    [green]' then run the report[/green]
    doCmd.OpenReport "rptCourseSchedule", acPreview

exit_cmdPreview_Click:
    Exit Sub
err_cmdPreview_Click:
    msgbox err & ": " & err.description
    Resume Exit_cmdPreview_Click
End Sub

Note: I've typed in the above freehand... there may be a typo etc.

HTH

Max Hugen
Australia

 

[maxhugen]

oops... one typo spotted already.

In the function CreateCourseDays() I omitted to set the db variable. The function should be:

Public Function CreateCourseDays()
On Error GoTo err_CreateCourseDays
Dim db as DAO.database
Dim rsCourse as DAO.recordset
Dim rsTemp as DAO.recordset
Dim DayToAdd as Date
Dim s$

    ' set a reference to the database
    [blue]Set db = CurrentDb[/blue]

    ' clear the temp table
    s="DELETE * FROM CourseSchedule"
    db.Execute s

    ' get the course data
    Set rsCourse = db.OpenRecordset("CourseSchedule")
    
    ' open the temp table
    Set rsTemp = db.OpenRecordset("CourseDay")

        ' loop through the course records
        While not rsCourse.EOF

            DayToAdd = rsCourse!Start_Date
            While DayToAdd <= rsCourse!End_Date
                ' add a record for every day of the course
                rsTemp.AddNew
                    rsTemp!Course=rsCourse!Course
                    rsTemp!CourseDay=DayToAdd
                rsTemp.Update
                DayToAdd = DayToAdd+1
            Wend

        wend

    ' close the recordsets
    rsTemp.close
    rsCourse.close

exit_CreateCourseDays:
    Set db=nothing: set rsCourse=nothing: set rsTemp=nothing
    exit function
err_CreateCourseDays:
    msgbox err & ": " & err.description
    resume exit_CreateCourseDays
End function

Max Hugen
Australia

 

[caburky]

Max,
First of all, thank you for putting so much effort into your response. I really appreciated you adding the lines of comment to your code so I can begin to understand.

I had to make one change in the from statement: CourseSchedule to CourseDay. Then it ran, and ran, and ran. When I interrupted I found two things: the function continually repeats the course never moving to the next record, in other words with the data below it is just repeating MM004, 7/11, 7/12, 7/13 never moving to MM004 7/20, AD011, etc. However, when I stopped the function the crosstab and report did work correctly.

Here's an example of the records:
Data
Course Start_Date End_Date Length
MM004 7/11 7/13 24
MM004 7/20 7/21 16
AD011 7/16 7/16 8
CD265 7/12 7/13 16

 

[maxhugen]

I think I better stop writing code 'freehand'! Forgot to add a MoveNext instruction, so the code is an endless loop.

While programming and debugging I'd catch that in a flash... sorry!

The following is tested this time:

Public Function CreateCourseDays()
On Error GoTo err_CreateCourseDays
Dim db As DAO.Database
Dim rsCourse As DAO.Recordset
Dim rsTemp As DAO.Recordset
Dim DayToAdd As Date
Dim s$

    ' set a reference to the database
    Set db = CurrentDb

    ' clear the temp table
    s = "DELETE * FROM CourseDay"
    db.Execute s

    ' get the course data
    Set rsCourse = db.OpenRecordset("CourseSchedule")
    
    ' open the temp table
    Set rsTemp = db.OpenRecordset("CourseDay")

        ' loop through the course records
        While Not rsCourse.EOF

            DayToAdd = rsCourse!Start_Date
            While DayToAdd <= rsCourse!End_Date
                ' add a record for every day of the course
                rsTemp.AddNew
                    rsTemp!Course = rsCourse!Course
                    rsTemp!CourseDay = DayToAdd
                rsTemp.Update
                DayToAdd = DayToAdd + 1
            Wend
            [blue]rsCourse.MoveNext[/blue]
        Wend

    ' close the recordsets
    rsTemp.Close
    rsCourse.Close

exit_CreateCourseDays:
    Set db = Nothing: Set rsCourse = Nothing: Set rsTemp = Nothing
    Exit Function
err_CreateCourseDays:
    MsgBox Err & ": " & Err.Description
    Resume exit_CreateCourseDays
End Function

Max Hugen
Australia

 

[caburky]

Max,
I'm at a loss for words...I don't know how to thank you. This is perfect!

 

[maxhugen]

caburky, you're welcome. Best way to show your appreciation is to drop in on the forum occasionally, and try to help someone else with an answer.

Max Hugen
Australia