Normalization of DB Tables

[sribblett]

From the totals I removed the "Non-Credit" and all expired training. Expired training is based on the duration field in the course table. It calculates based on date taken, duration, and the current date.

I added a yes no field to the sites table to mark the site as "waivered" or not

I am still uncertain about the review period, and what you want to do with that.


Lets say a person is waivered for CN (10/1/18 —9/30/20) and for HF (2/3/2020 - 2/3/2022).
Does that mean the total credit hours for CN purposes should only included training that occured between (10/1/18 —9/30/20) and then for HF only count records between (2/3/2020 - 2/3/2022)?

Or does a review come up and if the person has 30 hours they can be assigned to the site regardless of when the training occured (as long as not expired)?

yes to the first part you stated. If staff is waivered for CN site 10.1.18 - 9.30.20 ALL wiavered staff must have trainings completed within that review period. And if staff is ALSO waivered to another site l(Bush site 9.5.18 - 9.4.20); which is what I'm working on these reviews right now. If staff is waivered to both they MUST have 30 within the review period of both. I'm so sorry if I don't explain this well enough. I just do it and it's hard for me to explain.

 

[sribblett]

yes to the first part you stated. If staff is waivered for CN site 10.1.18 - 9.30.20 ALL wiavered staff must have trainings completed within that review period. And if staff is ALSO waivered to another site l(Bush site 9.5.18 - 9.4.20); which is what I'm working on these reviews right now. If staff is waivered to both they MUST have 30 within the review period of both. I'm so sorry if I don't explain this well enough. I just do it and it's hard for me to explain.

What I would like to do is be able to run a query or report to show all who are waivered to a site and show all 9 OCFS categories and the total. Will this be possible?

 

[MajP]

Does this make sense? You can show All (non expired) credit hours. Or select from their sites and view the hours that fall into the review period. In this example I assigned ECCC and HF to user 1

 

[MajP]

If that logic / accounting above makes sense then this can be made into a report or query.

 

[sribblett]

Does this make sense? You can show All (non expired) credit hours. Or select from their sites and view the hours that fall into the review period. In this example I assigned ECCC and HF to user 1View attachment 85123View attachment 85124

I love it!!! this will work for all employee separately for a report? Once I get all information into the database i will be able to see more of what I need. Each tome i open the database the first staff person pops up - I have to exit out of it to click on employee form. Why is that. It's ok, but wondering why it wasn't just the Main DB where I select Table / Query / Form?

 

[sribblett]

I love it!!! this will work for all employee separately for a report? Once I get all information into the database i will be able to see more of what I need. Each tome i open the database the first staff person pops up - I have to exit out of it to click on employee form. Why is that. It's ok, but wondering why it wasn't just the Main DB where I select Table / Query / Form?

I'm not sure how I can change the dates for the licensing periods or add different sites as needed. Can a list be made of all waivered sites in the box and change the review dates as needed.? BR - 9.30.20-9.30.22 / BU - 9.5.20 - 9.4.22 / CN - 10.1.20-9.30.22 / GOW - 9.18.19-9.17.21 are waivered sites and review schedule can this all be added in the box you created and I click for the one I need training hours to run for?. If not I completely understand. Can these sites and their next review dates then I can change them accordingly. All I have to do is click on the site I need the trainings dates to fall into, correct? I will need directions on how to change the periods of time when I need to.

 

[MajP]

I added some buttons on top of the form to get you to other forms to add/edit the sites, courses, etc. So you can change the review period once that occurs.

Each time I open the database the first staff person pops up - I have to exit out of it to click on employee form. Why is that. It's ok, but wondering why it wasn't just the Main DB where I select Table / Query / Form?

In the File-Options-Current Database
You can set a lot of properties. One thing is the default form to open when the DB opens. I picked this form. Not sure if that is your question.

 

[sribblett]

I added some buttons on top of the form to get you to other forms to add/edit the sites, courses, etc. So you can change the review period once that occurs.

In the File-Options-Current Database
You can set a lot of properties. One thing is the default form to open when the DB opens. I picked this form. Not sure if that is your question.

Yes... That's exactly what I needed to know.

 

[sribblett]

I added some buttons on top of the form to get you to other forms to add/edit the sites, courses, etc. So you can change the review period once that occurs.

In the File-Options-Current Database
You can set a lot of properties. One thing is the default form to open when the DB opens. I picked this form. Not sure if that is your question.

This looks exactly the way I wanted to be able to get my reports. Thank You so much!!! You have put so much time into this and I truly appreciate it. I think it's good now for me to enter all staff's information. If I run into any issues, may I ask you and you can let me know how to do it. Now the DB is constructed I think I'll be able to take your guidance and do what I need to do. But I'm thinking this really looks good and I'm hoping I won't need you to help me anymore -- you have done so much already.

 

[sribblett]

This looks exactly the way I wanted to be able to get my reports. Thank You so much!!! You have put so much time into this and I truly appreciate it. I think it's good now for me to enter all staff's information. If I run into any issues, may I ask you and you can let me know how to do it. Now the DB is constructed I think I'll be able to take your guidance and do what I need to do. But I'm thinking this really looks good and I'm hoping I won't need you to help me anymore -- you have done so much already.

So far so good. I've got all staff in the database. Works nice. However, what is T_Classes_COS used for and SubFrmClasses_COS?

 

[MajP]

I added a report and button to get to that report. See if this makes sense. Right now if their home site is a waivered site then for it to show up in the report you have to also add it to the assigned site list.

 

[MajP]

However, what is T_Classes_COS used for and SubFrmClasses_COS?

These are the classes assigned to a course of study. The subfrmclasses COS is on the Course of Study tab. This is complicated because it is a 1 to many to many. So a Staff could have 2 courses of study (like two different degrees or schools). Each COS has different classes. So on that tab whichever COS you are clicked into in the first subform, you can add/edit classes in second subform for that COS.

 

[MajP]

I've got all staff in the database.

BTW. To use your data in the newest DB, you can import your your staff table into the newest DB I sent and delete the existing staff table . No need to copy and paste or create new.

 

[sribblett]

These are the classes assigned to a course of study. The subfrmclasses COS is on the Course of Study tab. This is complicated because it is a 1 to many to many. So a Staff could have 2 courses of study (like two different degrees or schools). Each COS has different classes. So on that tab whichever COS you are clicked into in the first subform, you can add/edit classes in second subform for that COS.

OK....Thanks!!! In The SubFrmWaiveredSites - I tried to add HF since it wasn't there. But I got an error msg stating "You cannot add or change a record because a related record is required in T_Employees"

In the SubFromEmpCreditHours I changed the order of the 9 OCFS Categories. However, in the F_TrainingCourses - where do I go or how do i change the order of the 9 OCFS Categories in the correct order in the drop down box when selecting?

 

[sribblett]

These are the classes assigned to a course of study. The subfrmclasses COS is on the Course of Study tab. This is complicated because it is a 1 to many to many. So a Staff could have 2 courses of study (like two different degrees or schools). Each COS has different classes. So on that tab whichever COS you are clicked into in the first subform, you can add/edit classes in second subform for that COS.

TY..... That was dumb of me. COS makes sense it means Course of Study.

 

[sribblett]

If that logic / accounting above makes sense then this can be made into a report or query.
[/QUOTE

TY..... That was dumb of me. COS makes sense it means Course of Study.

Yes..... I did that anyways. Home site is just the home site. I always clicked the waivered site that was their home site. So we both thought the same on that. TY

 

[MajP]

In The SubFrmWaiveredSites - I tried to add HF since it wasn't there. But I got an error msg stating "You cannot add or change a record because a related record is required in T_Employees"

You cannot go directly to that subform. That subform is on the tab Assigned Sites. This is where you assign sites to a staff member. The reason you get the error is that it needs a related key from the staff member. You can add edit sites on the sites form and mark them as waivered. You assign sites to people on the Assigned Sites tab. You cannot go to any of the subforms directly because almost all of them have a relationship to a staff in the main form.

In the SubFromEmpCreditHours I changed the order of the 9 OCFS Categories. However, in the F_TrainingCourses - where do I go or how do i change the order of the 9 OCFS Categories in the correct order in the drop down box when selecting

In the pull down rowsource you need to change it to
SELECT T_Credits_OCFS.CreditID, T_Credits_OCFS.CreditName FROM T_Credits_OCFS ORDER BY T_Credits_OCFS.CreditID;

 

[sribblett]

You cannot go directly to that subform. That subform is on the tab Assigned Sites. This is where you assign sites to a staff member. The reason you get the error is that it needs a related key from the staff member. You can add edit sites on the sites form and mark them as waivered. You assign sites to people on the Assigned Sites tab. You cannot go to any of the subforms directly because almost all of them have a relationship to a staff in the main form.


In the pull down rowsource you need to change it to
SELECT T_Credits_OCFS.CreditID, T_Credits_OCFS.CreditName FROM T_Credits_OCFS ORDER BY T_Credits_OCFS.CreditID;

That does make sense not changing in the subforms. TY I'm really going to try not to bother you too much. I think I've bothered you enough in the last few weeks. TY so much!!!!

 

[MajP]

Here is one more update. There was a mistake in the filter and list for the credits by site. This did not update when changing staff. Also the review date was not populated. This has the correct credit sort you mentioned. Again you should be able to import your tables to this one.

 

[MajP]

TY I'm really going to try not to bother you too much. I think I've bothered you enough in the last few weeks. TY so much!!!!

No problem. However, like I said you have some pretty complicated data relations. This looks simple and straight forward, but it is not. I think this would have challenged most people. So understanding this may take a lot of time. So no issue if you need to come back for explanation.

Just in the calculation of allowable staff credits per waivered site there are three different many to many relationships. That is enough to give anyone a headache.