Master Schedule to fill Schedule table for detail records

[dbar10]

I have been working on this problem for a couple of weeks but I keep hitting a brick wall. I have checked out several modules for Calendars but I cannot figure out how to rewrite them to do what I need. I hope you can help.
I am building a scheduling piece for Home Care services.
I have a Master schedule that sets out a schedule for each client, assigning a worker to each day of the week. The master table is below:
[MasterSchedMain]

MasterSchedID AutoNumber PK
ClientID Number Linked to Client Table
AuthID Number linked to Auth Table
JobID Number Linked to Job
ProgramID Number Linked to Program
WorkerID Number Linked to Worker table
StartDate Date/Time Short Date Start of period
ExpireDate Date/Time Short Date End of period
Day1Start Date/Time Med.Time Day1 = Sat.
Day1End Date/Time med.Time
Day1Worker Number WorkerID for this day
Day2Start Date/Time med. Time Day2 = Sun
Day2End Date/Time med.Time
Day2Worker Number WorkerID
Day3Start Date/Time med.Time Day3=Mon
Day3End Date/Time med.Time
Day3Worker Number WorkerID
Day4Start Date/Time med.Time Day4=Tues
Day4End Date/Time med.Time
Day4Worker Number WorkerID
Day5Start Date/Time med.Time Day5=Wed
Day5End Date/Time med.Time
Day5Worker Number WorkerID
Day6Start Date/Time med.Time Day6=Thu
Day6End Date/Time med.Time
Day6Worker Number WorkerID
Day7Start Date/Time med.Time Day7=Fri
Day7End Date/Time med.Time
Day7Worker WorkerID

From the above table I need to populate the the table below for the time period of the [StartDate] to the [ExpireDate]or 8 weeks whichever is less. Also, if the master Schedule is updated with a new worker on any particular day, I need to update the fields populated in lower table with new info.

[ScheduleTbl]

SchedID AutoNumber PK
ClientID Number linked to Client table
WorkerID Number Linked to Worker table
AuthID Number linked to Authorization table
Date Date/Time Short Date Date of Service
Day Text Day of Service
Start Date/Time Med Time Start time of shift
End Date/Time Med Time End time of shift
Verified Yes/No Used when verifying time cards
Cancel yes/No Cancelling shift
ReasonID Number Linked to Reasontbl
NoNeed Yes/No NoNeed to fill shift
NoNeedID Number Linked to NoNeedtbl

I hope you can understand the above. Like I said I can't get my head around how to get this accomplished. any help you can give or point me in the right direction, I would appreciate. thanks

 

[lameid]

I am thinking you need another table, 2 functions and an append query.

The third table Contains a list of days so that you can use it to get the dates. You can write a function to populate it (this would be an additional function to my solution). What you do with this table is to add it to a query with your first table and not join them. Then you use criteria to get the appopriate days based on start Date and expire date. This is where the first function comes in. It needs to take the two dates and return the expire date or the 8 days later part. Use this as part of your criteria.

The second function will take 8 parameters. A date and 7 times. It should determine the day of the week and then return the appropriate time based on that. You will use this function twice in your query. Once for start time and Once for end time.

If your data was normalized so your first table was 2, you would not need the function. Really the times should be in a related table with the day of the week, time and foreign key to your first table. Then you could use criteria based on your date to retrieve the appropriate day.

That is of course the overview. If you need more help than that, let us know.

 

[PHV]

Have a look here:

http://r937.com/relational.html

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

 

[dbar10]

Thanks for the tips, I normalized the tables and now I need your help. This is what I have now:

[MasterSchedMain]

MasterSchedID AutoNumber PK
ClientID Number Linked to Client Table
AuthID Number linked to Auth Table
JobID Number Linked to Job
ProgramID Number Linked to Program
StartDate Date/Time Short Date Start of period
ExpireDate Date/Time Short Date End of period
Starttime Date/Time Med.Time Start Shift
Endtime Date/Time med. Time End Shift

[SchDaySched]

DaySchedID AutoNumber PK
MasterSchedID Number ForeignID
Day Number Lookup from [SchedDay]
Worker Number Lookup ID from Worker table
Assign Date/Time Short Date Date worker assigned
UnAssign Date/Time Short Date Date worker unssigned
FutureWorker Number Lookup ID from Worker table
AssignFuture Date/Time Short date New worker assigned
UnAssignFuture Date/Time Short Date Fut. Worker unassigned

[SchedDay]
DayID AutoNumber PK
Day Text 'I have DayID 1 = Sat, DayID 2 = Sun
DayID 3=MOn 4=Tue 5=Wed 6=Thu 7=Fri


These tables above make up my main Master Schedule form and subform.

Now I need the info from here to populate the table below with days and dates between the [Assign] date from [SchDaySched] table and the ExpireDate from [MasterSchedMain]

[ScheduleTbl]

SchedID AutoNumber PK
ClientID Number FK
WorkerID Number FK
AuthID Number FK
Date Date/Time Short date Date of service
Day Text Day of Service
Start Date/Time Med Time Start of Shift
End Date/Time Med Time End of Shift

I learn so much from you folks and I really appreciate the help.

 

[lameid]

You need another table that has all days and specifies the day (DayID) from SchedDay.

Then you join your existing two tables together and join the new table on dayID. Finally you use criteria to limit the days from the new table based on the date field in MasterSchedMain. You can append based on this.

 

[dbar10]

I'm sorry lameid. I don't understand. I already have the table SchedDay that just has the 7 days of the week and DayID as PK. I also use it for the combolist in SchDaySched. Is this what you're talking about? Or do I just need another table like SchedDay?

Thanks for your response

 

[lameid]

You need another table that has everyday in it... For example:

Table
Calendar

Day_ID CalDay
3 6/8/2009
4 6/9/2009
5 6/10/2009
6 6/11/2009
7 6/12/2009
8 6/13/2009

 

[dbar10]

Oh Boy, this will be fun

 

[lameid]

Just use some code like this....

I'm not sure the quick way to get the numbers you used for days without looking in help but I'm betting you have some idea.

Sub PopulateCalendar (Start As date, End As Date)
    Dim RS as DAO.Recordset

    Currentdb.openrecordset(Select * From Calendar)
    Do Loop
         RS.AddNew
         RS!CalDay = Start
         RS!Day_ID = 'Not sure without looking I think you used some date constants 
         'and it should be realatively easy to get to them?
         RS.update
         Start = Start + 1 
   Until Start = End
End Sub

 

[dbar10]

lameid, before you got that code to me I had already made a table in excel with the CalDayID and CalDay. ran it from 1/1/2000 to 12/31/2030. Then imported into access table "SchedCalDaytbl". I then followed your instructions(I think) and the following is the SQL for the querie before turning into append. I am getting no records returned. I have several dummy schedules in.

SELECT MasterSchedMain.ClientID, SchDaySched.Worker, MasterSchedMain.AuthID, SchedCalDaytbl.Cal, SchDaySched.Day, MasterSchedMain.Starttime, MasterSchedMain.Endtime, MasterSchedMain.StartDate, MasterSchedMain.ExpireDate
FROM MasterSchedMain INNER JOIN (SchedCalDaytbl INNER JOIN SchDaySched ON SchedCalDaytbl.[Day ID] = SchDaySched.Day) ON MasterSchedMain.MasterSchedID = SchDaySched.MasterSchedID
WHERE (((SchedCalDaytbl.Cal)>=[StartDate] And (SchedCalDaytbl.Cal)<=[ExpireDate]));


Where did I go wrong?

 

[lameid]

Nothing looks really wrong at a glance although I would write it this way...

FROM MasterSchedMain INNER JOIN SchDaySched ON MasterSchedMain.MasterSchedID = SchDaySched.MasterSchedID
INNER JOIN SchedCalDaytbl ON SchedCalDaytbl.[Day ID] = SchDaySched.Day
WHERE SchedCalDaytbl.Cal Between MasterSchedMain.StartDate And MasterSchedMain.ExpireDate;

The catch here is that it assumes Expiredate that the dates are populated... You could use the NZ function if you have them as null or flip your criteria...

Where MasterSchedMain.StartDate <= SchedCalDaytbl.Cal AND (MasterSchedMain.ExpireDate is null or MasterSchedMain.ExpireDate <=SchedCalDaytbl.Cal)

 

[dbar10]

I am now getting arror on the following query:

SELECT MasterSchedMain.ClientID, SchDaySched.Worker, MasterSchedMain.AuthID, SchedCalDaytbl.Cal, SchDaySched.Day, MasterSchedMain.Starttime, MasterSchedMain.Endtime, MasterSchedMain.StartDate, MasterSchedMain.ExpireDate
FROM MasterSchedMain INNER JOIN SchDaySched ON MasterSchedMain.MasterSchedID = SchDaySched.MasterSchedID
INNER JOIN SchedCalDaytbl ON SchedCalDaytbl.[Day ID] = SchDaySched.Day
WHERE (((MasterSchedMain.StartDate)<=[SchedCalDaytbl].[Cal]) AND ((MasterSchedMain.ExpireDate) Is Null Or (MasterSchedMain.ExpireDate)<=[SchedCalDaytbl].[Cal]));


Error:

Syntax error (missing operator) in query expression 'MasterSchedMain.MasterSchedID = SchDaySched.MasterSchedID INNER JOIN SchedCalDaytbl ON SchedCalDaytbl.[DayID] = SchDaySched.Day'.

I don't see anything wrong. Do you?

 

[lameid]

I didn't see it until I played with a query in design view. Access SQL syntax is more idiosyncratic than I thought... I just added parenthesis in red below.

SELECT MasterSchedMain.ClientID, SchDaySched.Worker, MasterSchedMain.AuthID, SchedCalDaytbl.Cal, SchDaySched.Day, MasterSchedMain.Starttime, MasterSchedMain.Endtime, MasterSchedMain.StartDate, MasterSchedMain.ExpireDate
FROM [red]([/red]MasterSchedMain INNER JOIN SchDaySched ON MasterSchedMain.MasterSchedID = SchDaySched.MasterSchedID[red])[/red]
INNER JOIN SchedCalDaytbl ON SchedCalDaytbl.[Day ID] = SchDaySched.Day
WHERE (((MasterSchedMain.StartDate)<=[SchedCalDaytbl].[Cal]) AND ((MasterSchedMain.ExpireDate) Is Null Or (MasterSchedMain.ExpireDate)<=[SchedCalDaytbl].[Cal]));

The Query designer puts in so many extraneous parenthesis I tend to take them out when I am putting SQL in code or posting. Apparently Access needs that pair.

 

[dbar10]

Aaargh!!!! lameid, that got rid of the error message, thanks, but I am still not getting any results when the query runs. Is there anything else I can do?

 

[lameid]

Cal is the date field where you have all your days right?

 

[dbar10]

I think I see the problem, In my SchedCalDaytbl, I used a series of numbers from 1 to 11310 something. I just realized that I have to number those to coincide with the DaysID 1 - 7 right? I will work on that.

 

[dbar10]

OK may Days ID is rotating on 1-7 to coincide with the days and dates. Cal is the dates from 1/1/2000 to 12/31/2030.
PK is Cal. I have joined Days ID to Day of SchDaySched.

No query is returning nothing, no blank record.

Have I lost my mind? Don't answer that.

SELECT MasterSchedMain.ClientID, SchDaySched.Worker, MasterSchedMain.AuthID, SchedCalDaytbl.Cal, SchDaySched.Day, MasterSchedMain.Starttime, MasterSchedMain.Endtime, MasterSchedMain.StartDate, MasterSchedMain.ExpireDate
FROM MasterSchedMain INNER JOIN (SchedCalDaytbl INNER JOIN SchDaySched ON SchedCalDaytbl.[Day ID] = SchDaySched.Day) ON MasterSchedMain.MasterSchedID = SchDaySched.MasterSchedID
WHERE (((MasterSchedMain.ClientID)=74) AND ((MasterSchedMain.StartDate)<=[SchedCalDaytbl].[Cal]) AND ((MasterSchedMain.ExpireDate) Is Null Or (MasterSchedMain.ExpireDate)<=[SchedCalDaytbl].[Cal]));

 

[dbar10]

I got home, ate some dinner and looked at it again. I found the problem. When I imported the SchedCalDaytbl from Excel, Access formatted the Day ID field as double instead of long integer. I changed that. Then I got results but they were all after the expire date so I changed the query critirea to the following, notice the >= at the expire date.:

SELECT MasterSchedMain.ClientID, SchDaySched.Worker, MasterSchedMain.AuthID, SchedCalDaytbl.Cal, SchDaySched.Day, MasterSchedMain.Starttime, MasterSchedMain.Endtime, MasterSchedMain.StartDate, MasterSchedMain.ExpireDate
FROM MasterSchedMain INNER JOIN (SchedCalDaytbl INNER JOIN SchDaySched ON SchedCalDaytbl.[Day ID] = SchDaySched.Day) ON MasterSchedMain.MasterSchedID = SchDaySched.MasterSchedID
WHERE (((MasterSchedMain.StartDate)<=[SchedCalDaytbl].[Cal]) AND ((MasterSchedMain.ExpireDate) Is Null Or (MasterSchedMain.ExpireDate)>=[SchedCalDaytbl].[Cal]));

Amazing what happens when we back off the problem for a little bit. Thank you very much for all your help. I just couldn't get the light bulb to go off, you mad all the difference. God bless you!!

 

[lameid]

I'm glad you caught that... I can't believe I made that typo and missed it. Oh well, it is always the little things that are missed.