Normalization of DB Tables (1 Viewer)

sribblett

Member
Local time
Today, 13:03
Joined
Aug 24, 2020
Messages
89
This has the fixed query so the Required tab shows totals for all credit areas including 0 and status of required training.
I really like that. Any free time, I play around with it some. Trying to grasp the whole thing so when I start entering all data from old DB into the new final version DB I'll know what I'm doing. Boy, I'm working you overtime on this - I do so appreciate this. I would've NEVER gotten all this on my own- that is for sure!!
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:03
Joined
May 21, 2018
Messages
8,463
See if this makes sense for tracking the expired training
Exp.png
. I added an expiration (in years) field to the training courses table. All required are 2 years, 1st aid is 1, and TSG is 3. Then if you do that you can calculate expired training. I show this on the required and totals tab.
 

Attachments

  • MajP_TrainingDB V10.accdb
    1.8 MB · Views: 155

sribblett

Member
Local time
Today, 13:03
Joined
Aug 24, 2020
Messages
89
See if this makes sense for tracking the expired training View attachment 85076 . I added an expiration (in years) field to the training courses table. All required are 2 years, 1st aid is 1, and TSG is 3. Then if you do that you can calculate expired training. I show this on the required and totals tab.
I believe that will work. For the required trainings. However, for the period of time for all training hours due to licensing periods all trainings completed need to be within a specific time. If this is possible. CN review schedule is starting 10.1.20 ends 9.30.22 every 2 yrs; Bush- starts 9.5.20 end 9.4.22 every 2 yrs; ECCC-starts 9.17.19 ends 9.16.21; HF- starts 2.3.20 end 2.2.22 every 2 yrs; Gow-starts 9.18.19 ends 9.17.21 every 2 yrs. I hope this makes more sense than how I explained it last night. Every staff who is waived at any one of these sites all their completed training must fall within the time periods of that site's review period. Hope tis helps more.
 

sribblett

Member
Local time
Today, 13:03
Joined
Aug 24, 2020
Messages
89
See if this makes sense for tracking the expired training View attachment 85076 . I added an expiration (in years) field to the training courses table. All required are 2 years, 1st aid is 1, and TSG is 3. Then if you do that you can calculate expired training. I show this on the required and totals tab.
I tried to figure out in design view for the table for the 9 OCFS categories. I couldn't. Because (I really hate to be picky) but at time a trainer only puts #1 or #6 for a category. OCFS has a standard numeric way for their categories to run. I was going to change it myself but couldn't figure how to do it. The order OCFS has their categories are #1-Principles of Early Childhood; #2-Nutrition & Health Needs for Infants and Children; #3-Child Day Care Program Development; #4-Safety & Security Procedures; #5-Business Records &Maintenance & Management; #6-Child Abuse & Maltreatment Identification & Prevention; #7-Statutes & Regulations Pertaining to Child Day Care; #8-Statutes & Regulations Pertaining to Child Abuse & Maltreatment; #9-Shaken Baby. Without too much trouble would you be able to put those categories in the right order. With the last one being No credit hours. If you tell me what to do I am perfectly ok with me changing it; but I'm at a loss right now while it's still be perfected.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:03
Joined
May 21, 2018
Messages
8,463
The sub form that shows the totals per credit is based on the query "qryCreditTotals". It was sorted by Credit name and it should be sorted by credit ID so they appear in the order entered. In the sub form subfrmEmpCreditHours you will just have to move both the field and the Labels in the correct order you want.
xtab.jpg
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:03
Joined
May 21, 2018
Messages
8,463
CN review schedule is starting 10.1.20 ends 9.30.22 every 2 yrs; Bush- starts 9.5.20 end 9.4.22 every 2 yrs; ECCC-starts 9.17.19 ends 9.16.21; HF- starts 2.3.20 end 2.2.22 every 2 yrs; Gow-starts 9.18.19 ends 9.17.21 every 2 yrs. I hope this makes more sense than how I explained it last night. Every staff who is waived at any one of these sites all their completed training must fall within the time periods of that site's review period. Hope tis helps more.

In the sites table I can add review Period Start and Review Period End. This would be the active period of review. Assume you record a bunch of training courses for an individual. Lets assume they are waived for Bush and ECC. Do you need to show training and count training for Bush (training that occured between 9.5.20 and 9.4.22) and somewhere else show ECC eligible training that occured between 9.17.19 and 9.16.21? This is doable based on the table structure. Maybe you can draw out how you want to present this information. What happens to old training outside of these periods? Does it just become expired for that site.
 

sribblett

Member
Local time
Today, 13:03
Joined
Aug 24, 2020
Messages
89
In the sites table I can add review Period Start and Review Period End. This would be the active period of review. Assume you record a bunch of training courses for an individual. Lets assume they are waived for Bush and ECC. Do you need to show training and count training for Bush (training that occured between 9.5.20 and 9.4.22) and somewhere else show ECC eligible training that occured between 9.17.19 and 9.16.21? This is doable based on the table structure. Maybe you can draw out how you want to present this information. What happens to old training outside of these periods? Does it just become expired for that site.
What I did with my excel spread sheet was what i was the first site that is set for review I would delete all trainings that was taken before before the review period started. I had to go to each staff and do it one by one. It was very cumbersome. Once I deleted all those trainings for each staff the total hours would show what they had left. Then I would know how many hours each staff would need to get to their 30 hours that's required. And TY for letting me know how to change the order of the categories.
 

sribblett

Member
Local time
Today, 13:03
Joined
Aug 24, 2020
Messages
89
What I did with my excel spread sheet was what i was the first site that is set for review I would delete all trainings that was taken before before the review period started. I had to go to each staff and do it one by one. It was very cumbersome. Once I deleted all those trainings for each staff the total hours would show what they had left. Then I would know how many hours each staff would need to get to their 30 hours that's required. And TY for letting me know how to change the order of the categories.
It worked. Thank You!!!
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:03
Joined
May 21, 2018
Messages
8,463
I will add the ActiveReviewStart and ActiveReviewEnd date fields to the site table. Then you can have a query that shows total credit hours that fall in the review periods for each waivered site for each staff. Will that work? Is the home site always a waivered site?
 

sribblett

Member
Local time
Today, 13:03
Joined
Aug 24, 2020
Messages
89
I will add the ActiveReviewStart and ActiveReviewEnd date fields to the site table. Then you can have a query that shows total credit hours that fall in the review periods for each waivered site for each staff. Will that work? Is the home site always a waivered site?
No ...all home sites are not waivered sites. All the waivered sites are only one I stated in my previous email. There will be one more added, because we opened another classroom at another but I do not know the name or what period it will be because the licensing period will not start until we pass the initital insepection. Right now the only site I have that are waivered are: Br (Brocton); BU(Bush); CN (Connections North); ECCC (Early Care Community Center); GOW(Gowanda); HF(Holy Family)
 

sribblett

Member
Local time
Today, 13:03
Joined
Aug 24, 2020
Messages
89
No ...all home sites are not waivered sites. All the waivered sites are only one I stated in my previous email. There will be one more added, because we opened another classroom at another but I do not know the name or what period it will be because the licensing period will not start until we pass the initital insepection. Right now the only site I have that are waivered are: Br (Brocton); BU(Bush); CN (Connections North); ECCC (Early Care Community Center); GOW(Gowanda); HF(Holy Family)
Also, one more thing I am hoping can be done. I love the subfolder showing totals of training hours. Is there a way of adding a Total (adding) all their hours and put it right under the total hours for the 9 categories that are showing.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:03
Joined
Feb 19, 2002
Messages
42,971
The "menu" structure doesn't make sense to me. Since the form is about an Employee, items like Training Courses and Waivered Sites do not belong on the same form.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:03
Joined
May 21, 2018
Messages
8,463
Maybe I misunderstood what a waivered site is and its relationship to a staff member. I was under the impression a staff membered was waivered to a set of sites. So the wavering was unique to the individual. I am not sure what that means, but I assumed it meant something like approved to work at that site. If that is not the case I have the table and form wrong. If a site can be waivered on not waivered. Then Need a field in the site table to say that site is waivered (yes no). What does it mean when you assign a person to a site?
 

sribblett

Member
Local time
Today, 13:03
Joined
Aug 24, 2020
Messages
89
Maybe I misunderstood what a waivered site is and its relationship to a staff member. I was under the impression a staff membered was waivered to a set of sites. So the wavering was unique to the individual. I am not sure what that means, but I assumed it meant something like approved to work at that site. If that is not the case I have the table and form wrong. If a site can be waivered on not waivered. Then Need a field in the site table to say that site is waivered (yes no). What does it mean when you assign a person to a site?
Home site is where a staff main site is. For me, for instance, my home site is LCAC (not a waivered site) because it's offices. However, I am waivered to a couple of sites like HF and CN when there is a need for me to sub incase too many call off then there are extra staff to fill in to make sure there is enough staff to be in a classroom. The only home site that are not waivered sites are LCAC and MYAB (these sites are just offices where staff normally work from) All other sites (Brocton, Bush, CN, ECCC, Gow, & HF) are where most staff are located. Home site is where their main site is, however, if they are waivered to other sites this means staff may go to those sites to work. You have to be waivered to a site to be able to sub in a classroom. We're not allowed to just have anyone to work where ever they want. This is why I have that tracking of home site and what sites each staff can go to work at..
 

sribblett

Member
Local time
Today, 13:03
Joined
Aug 24, 2020
Messages
89
Home site is where a staff main site is. For me, for instance, my home site is LCAC (not a waivered site) because it's offices. However, I am waivered to a couple of sites like HF and CN when there is a need for me to sub incase too many call off then there are extra staff to fill in to make sure there is enough staff to be in a classroom. The only home site that are not waivered sites are LCAC and MYAB (these sites are just offices where staff normally work from) All other sites (Brocton, Bush, CN, ECCC, Gow, & HF) are where most staff are located. Home site is where their main site is, however, if they are waivered to other sites this means staff may go to those sites to work. You have to be waivered to a site to be able to sub in a classroom. We're not allowed to just have anyone to work where ever they want. This is why I have that tracking of home site and what sites each staff can go to work at..
Maybe this might explain better. Our director, Managers, myself all work out of "an non-waivered" site because they are just office (LCAC and MYAB) All Center Directors/teachers/Teacher Assistants/builidng clerks/nurses/ all have a home site they work from. Where their classroom/office/desk is at; however they may go to another site (if waivered) to help out in a classroom.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:03
Joined
May 21, 2018
Messages
8,463
Can you answer each question very specifically. Please respond using 1..., 2... etc. not a combined paragraph.
1. Is it correct that a site is either waivered or not waivered, and that is not specific to a staff member? It kind of sounds like this is correct.
2. Or is a specific person waivered to work at certain sites?
Currently it assumes the latter where you assign a home site and then additional sites to a specific staff member.
3. Even if 1 is correct, is there a need to assign staff members to multiple sites? Is there meaning behind this assignment?
4. Is it correct that sites have review periods, and training to work at that site only counts if it takes place within the review period?
5. If 4 is correct do you need to see the qualified training just for the home site, or all waivered sites, or all sites?

I added the sum field to the credits subform.
 

Attachments

  • MajP_TrainingDB V11.accdb
    1.8 MB · Views: 144
Last edited:

sribblett

Member
Local time
Today, 13:03
Joined
Aug 24, 2020
Messages
89
Can you answer each question very specifically. Please respond using 1..., 2... etc. not a combined paragraph.
1. Is it correct that a site is either waivered or not waivered, and that is not specific to a staff member? It kind of sounds like this is correct.
2. Or is a specific person waivered to work at certain sites?
Currently it assumes the latter where you assign a home site and then additional sites to a specific staff member.
3. Even if 1 is correct, is there a need to assign staff members to multiple sites? Is there meaning behind this assignment?
4. Is it correct that sites have review periods, and training to work at that site only counts if it takes place within the review period?
5. If 4 is correct do you need to see the qualified training just for the home site, or all waivered sites, or all sites?

I added the sum field to the credits subform.
1. We have 5 waivered sites. These sites are where our licensor does her reviews and relicensing. 2 of the home sites are not waivered sites- they have nothing to do with OCFS reviews at all.
2. Each staff person has a specific site in which they work from (Home Site). Then staff (not all) may be assigned (waivered sites) to be able to work. When staff has additional sites they can work, we have to have it cleared with our licensor beforehand.
3. Yes .... this very important because staff leave or if they call off we need to know who is allowed to take the place of missing staff if needed. This happens quite often. We are mandatory to have certain number of staff per sim many children who are in attendance. So there is a need for staff to leave their home site to work elsewhere .... only if they are waivered to that site that needs additional. Staff.
4. Yes. All staff who are waivered to a site that is going through review - that 2 yr period- all training hours completed during the review period is counted. Teceryine must have minimum of 30 hrs per review time frame. All sites have different time frames.
5. Just that one site is being reviewed - when a site is being review all staff who is waivered to that site must have their minimum 30 hrs of trainings completed within that site only. This includes ALL STAFF waivered to that sure. (For ex. For myself— my home site is not a waivered site(LCAC), however I am waivered to work at CN which is getting ready for their review. The review period is 10/1/18 —9/30/20. So everyone who is waivered to CN (doesn’t matter where their home site is) must have 30 hours. And all the required trainings must be valid.... none of them cannot be expired.
 

sribblett

Member
Local time
Today, 13:03
Joined
Aug 24, 2020
Messages
89
1. We have 5 waivered sites. These sites are where our licensor does her reviews and relicensing. 2 of the home sites are not waivered sites- they have nothing to do with OCFS reviews at all.
2. Each staff person has a specific site in which they work from (Home Site). Then staff (not all) may be assigned (waivered sites) to be able to work. When staff has additional sites they can work, we have to have it cleared with our licensor beforehand.
3. Yes .... this very important because staff leave or if they call off we need to know who is allowed to take the place of missing staff if needed. This happens quite often. We are mandatory to have certain number of staff per sim many children who are in attendance. So there is a need for staff to leave their home site to work elsewhere .... only if they are waivered to that site that needs additional. Staff.
4. Yes. All staff who are waivered to a site that is going through review - that 2 yr period- all training hours completed during the review period is counted. Teceryine must have minimum of 30 hrs per review time frame. All sites have different time frames.
5. Just that one site is being reviewed - when a site is being review all staff who is waivered to that site must have their minimum 30 hrs of trainings completed within that site only. This includes ALL STAFF waivered to that sure. (For ex. For myself— my home site is not a waivered site(LCAC), however I am waivered to work at CN which is getting ready for their review. The review period is 10/1/18 —9/30/20. So everyone who is waivered to CN (doesn’t matter where their home site is) must have 30 hours. And all the required trainings must be valid.... none of them cannot be expired.
I’m adding .... everyone who’s home site is one of the 5 waivered sites they’re waivered to to that site. It’s just staff who are waivered to LCAC or MYAB who are not waivered to their home site due to these 2 sites are not through OCFS because there aren’t any classrooms. Their just offices where Directors/Coordinators/ Managers/QA/ works from. But we all are waivered to certain sites to be able to work at in case there’s a need!!
 

sribblett

Member
Local time
Today, 13:03
Joined
Aug 24, 2020
Messages
89
I’m adding .... everyone who’s home site is one of the 5 waivered sites they’re waivered to to that site. It’s just staff who are waivered to LCAC or MYAB who are not waivered to their home site due to these 2 sites are not through OCFS because there aren’t any classrooms. Their just offices where Directors/Coordinators/ Managers/QA/ works from. But we all are waivered to certain sites to be able to work at in case there’s a need!!
I need to know (I run a query)this information so I know who all needs all their trainings to gather to hive to OCFS. OCFS had records who all are waivered to all sites so the expect me to send all training information to them for all our waivered staff. I come up with these bits of information hoping to explain what need and why.
 

sribblett

Member
Local time
Today, 13:03
Joined
Aug 24, 2020
Messages
89
Can you answer each question very specifically. Please respond using 1..., 2... etc. not a combined paragraph.
1. Is it correct that a site is either waivered or not waivered, and that is not specific to a staff member? It kind of sounds like this is correct.
2. Or is a specific person waivered to work at certain sites?
Currently it assumes the latter where you assign a home site and then additional sites to a specific staff member.
3. Even if 1 is correct, is there a need to assign staff members to multiple sites? Is there meaning behind this assignment?
4. Is it correct that sites have review periods, and training to work at that site only counts if it takes place within the review period?
5. If 4 is correct do you need to see the qualified training just for the home site, or all waivered sites, or all sites?

I added the sum field to the credits subform.
Thank you so much for the totals. It's exactly where I wanted it to go. Can you make it so the total hours do not include non-credit hours? If fo, I would like it to. If not I can definitely work with it.
 

Users who are viewing this thread

Top Bottom