ChildCarePlan

[silentwolf]

Hi guys,

just wondering if someone had already something similar to get me on the right track with following.

I create a small Database for my little sis she works with kids in a ChildCareCentre.

The Datastructur is attached what I got at present.

Tables:
Employees,
Employment,
EmploymentPlan,
Plan,
Weekdays,
Children,
ChildCare,

Table Infos:
Employees: Employee Data
Employment: Each Employee could have one or more Employments meaning he or she could change the weeklyhours for a given periode.
EmploymentPlan: An Employee could be working on Monday, and Friday from 7:00 to 12:00, on Tuesday from 7:00 to 13:00 and on Wednesday and Thursday from 8:00 to 13:00 for example.
I am getting that Times from the Plan Table

The same applies to Children.
A Child might be in Care on Monday and Tuesday from 7:00 to 12:00 on Wednesday from 7:00 to 13:00.
That might change in some other periods.

Maybe from 1/6/2022 to 31/10/22 Monday and Thuesday from 7:00 to 13:00 and Wednesday from 7:00 to 15:00
and the same kid is from 1/11/22 till the 30/10/2022. Only on Wednesday from 7:00 to 15:00.

In the Plan Table there is a boolean field HasLunch includet.
so if the Plan is from 7:00 to 12:00 there is no lunch.
All other times longer then 12:00 are with lunch.

So I guess so far the Structure is able to sort that out ok.

Now the things I am not sure about:
First I like to build a Calendar for a period of time to see how many kids having lunch at a given periode.

Create a Calendar to see how many Employees are available for that given periode to look after the kids.
Meaning that there are always incomming request for new kids or request if a child could be in a different periode attend different times.

So depanding on how many kids having lunch I need to plan times where the employees can take their own lunch.

So the kids have lunch between 12:00 to 13:00.
If there are more kids on wednesday for example the Employee need to take their lunch at 11:00 to 12: or from 13:00 to 14:00.

I got a table Breaks where different times are entered where someone could take lunchbreaks.
But I am not sure what table I would still need to be able to manage those breaks correctly.

So I was thinking of insert Data Into tblDates I called it for a given periode.

So would I need to create as said a Calendar and from each Day that I can adjust lunchbreaks for the Employees who are at that day working.

Hope that makes sense?

Maybe someone could help me for gettint that achived )

Many thanks

 

[ebs17]

This is a more complex task. The ability to abstract is quickly overwhelmed, so it would make sense to provide a sample database with a few but meaningful data sets in order to try out your own ideas.

For tasks, I look in the relationship picture, in which tables I can find the data I need. For the first task (calendar for children) that would be ChildCare with ChildID and the date limits and Plan with HasLunch. Of course, you have to take the intermediate tables with you for the link. In connection with the calendar table, it should be possible to create the desired calendar, which also contains the days of the week.

I would proceed in the same way to determine the available employees. But this is where my personal abstraction starts to have problems and I would have to look and try something on a real example.

 

[silentwolf]

Hi Eberhard,

thanks for your reply!

I have attached a file inkluding a small Form.

Would be nice if you could take a look at it for me!

Many thanks

Albert

 

[ebs17]

SELECT ChildCare.ChildCareID, ChildCare.DateFrom, ChildCare.DateTo, CarePlanDays.WeekDayID
FROM ChildCare INNER JOIN CarePlanDays ON ChildCare.ChildCareID = CarePlanDays.ChildCareID;

This is already the end because the second table does not contain any data. Of course, I don't see any development where this is going and what should be included.

 

[silentwolf]

Hmm Sorry not entered that part of the table.
Just thought I get that db to you quickly.

Of course, I don't see any development where this is going and what should be included.

Not sure what you mean by that?
The issue I have is that I am not sure how this suppose to be set up correctly.

If in that case should I have an update Query to send dates to a sperate not linked table as tblDates?
But then I am not exactly sure how that Query need to look like.

This is what I tried.

SELECT ChildCare.ChildCareID, ChildCare.DateFrom, ChildCare.DateTo, CarePlanDays.WeekDayID, WeekDays.DayOfWeek, Children.FirstName, Children.LastName
FROM WeekDays INNER JOIN (Children INNER JOIN (ChildCare INNER JOIN CarePlanDays ON ChildCare.ChildCareID = CarePlanDays.ChildCareID) ON Children.ChildID = ChildCare.ChildID) ON WeekDays.WeekDayID = CarePlanDays.WeekDayID;

Or including the Plan

SELECT ChildCare.ChildCareID, ChildCare.DateFrom, ChildCare.DateTo, CarePlanDays.WeekDayID, WeekDays.DayOfWeek, Children.FirstName, Children.LastName, Plan.StartTime, Plan.EndTime, Plan.HasLunch
FROM Plan INNER JOIN (WeekDays INNER JOIN (Children INNER JOIN (ChildCare INNER JOIN CarePlanDays ON ChildCare.ChildCareID = CarePlanDays.ChildCareID) ON Children.ChildID = ChildCare.ChildID) ON WeekDays.WeekDayID = CarePlanDays.WeekDayID) ON Plan.PlanID = CarePlanDays.PlanID;

Have included the DB with some Data for the Children.
Maybe that is better now and also with another form for the Children.

Should I create an INSERT INTO Query for the Children and for the Employees and send those Days to a sperate Table?

 

[LarryE]

I have been working on your problem and have a different solution that is much less complex and uses fewer tables as below:

I order to meet your requirements, you will need to track each employee and each child each day. Each Employee and child will have a STATUS assigned throuhout the day. For employees, their status can be Active or On Break or On Lunch Break or Absent from the building for some reason. For children, their status can be Active or On Lunch Break. This is the only way ACCESS can track activity for each day. Each new day, Employees and children are signed in and then their status can be changed and tracked during the day. All daily status activity is logged in the DailyTimeCalendar table so you can then create a report of the status of each child and employee is during the day.

I am working on data input forms but so far this design is what I have.

 

[silentwolf]

Hi Larry,

thanks for your reply! Did not believe that I would hear about this again )
I see you did put more infos into each table compared to my shema.
If it is simpler and achive better result than it is fantastic )

Really appreciate that you are so kind to take a look at it!
It is very much appreciated!

Cheers!

 

[LarryE]

Hi Larry,

thanks for your reply! Did not believe that I would hear about this again )
I see you did put more infos into each table compared to my shema.
If it is simpler and achive better result than it is fantastic )

Really appreciate that you are so kind to take a look at it!
It is very much appreciated!

Cheers!

I will attach the ACCESS file after testing it. It is going to be somewhat complicated, but can be accomplished. It may take me a day or two to get back to you.

 

[silentwolf]

I will attach the ACCESS file after testing it. It is going to be somewhat complicated, but can be accomplished. It may take me a day or two to get back to you.

All good Larry as I said it is much appreciated and no stress!!

Cheers

 

[LarryE]

@silentwolf:
I have the model design and forms nearly completed. I changed it from the original one I presented above, but I have a question about what absences. Does an absence mean the child or employee never entered the child care center for that day OR does it mean they entered the center but are absent at some point during the day for some reason?

Here is how it is designed now:

1. Each employee AND each child may have multiple days they came to the center
2. Each day they are there, each child and each employee has several status options
3. You enter details about each child and each employee only once

At the present time, the user would click a checkmark on a form to select each child AND each employee so they can be matched to any date. That means if an employee or a child is NOT matched to a date, then they are automatically absent for that day. You would not need to actually mark their status as ABSENT for that whole day. Here are the daily status opetions for both:

Children can be Active, Lunch or Absent

Employees can be Active, Lunch, Break or Absent

Presently, for each day, users select each employee AND each child to be matched to each day then select status options throughout the day as their status changes.

This is the only way that ACCESS can track each employee and each child for each day, but I still need to know what ABSENT means for employees and for children.

Larry

 

[silentwolf]

Hi Larry,

wow it looks pretty good so far!!

Does an absence mean the child or employee never entered the child care center for that day OR does it mean they entered the center but are absent at some point during the day for some reason?

As I think of it, it should be both.

For Employee:
To be able to track holidays, overtime compensations, sick leave, and so on.
But to record a day for perhabs leaving the care centre for sport activities or theirlike (Employee(s) ->kids) would be also great.
So you could track a day with who was with the kids and whom from the kids where in that group.
Some could stay in the centre some go for icecatting.

For Kids:
Would be the same as above. Who is for lunch on each day meaning taking lunch at the centre.
Kids have a standart plan a duration they are "usualy" in the centre:
Berhaps Monday and Thurstday from: 7:00 to 13_00.
That plan means this kid has lunch in the centre.

But that could change for certain exceptions:
Berhaps the parents ask if they can book next week Wednesday 30/11/2022 for kid "John Youngster" till 15:00 for whatever reason.

Then I need to check, if that Wednesday is ok with the current roster of employees:
The lunch is always from 12:00 to 13:00 in the centre for the kids.
Lunch for the employees is in ther "standart roster" meaning "Michaele Kunds" works certain days with certain times. And in those rosters the lunchbreak is "also standardized. But if I need her to be in the centre from 12:00 to 13:00 and her break is usually on wednesday from 12:00 to 13:00 then I need to tell her to go for lunch either before, from 11:00 top 12:00, or after, from 13:00 to 14:00 so we have enough stuff for the kids
at lunch for that day.

I hope that I did give you a little more inside,
however I do believe that your set up would esialy handle that or not?

Many thanks to you as I said it looks pretty good to me

 

[LarryE]

I can include fields for each employee for hourly pay rates or weekly or monthly salaries if you like as well as overtime compensation. Are employees paid an hourly rate or are they paid by the week or month?

 

[silentwolf]

I can include fields for each employee for hourly pay rates or weekly or monthly salaries if you like as well as overtime compensation. Are employees paid an hourly rate or are they paid by the week or month?

You are funny you are full into it it is nice to see!!

It is in hourly rate(s) based on weekly hours and paid monthly.

Cheers Albert

 

[LarryE]

Albert:
Here is a very preliminary model that I believe will meet your requirements. It tracks daily activity for employees and children and totals hours and employee compensation for each day. You should review the table relationships first. I have input some test employees and some testing hours. When you input the times, it is input in HH:MM AM or HH:MM PM format (For example 08:00 AM for 8:00 in the morning). Please test it and get used to how it works. I have no reports at this time. I will need detailed information about what your sister needs.

Larry

 

[silentwolf]

Hi Larry,

many thanks I will look into it and let you know!

Cheers Albert