Adding Tables to Database

[KristenD]

So just working this out for a minute I would have as follows:

tblTraining
TrainingID <PK>
EmpID <FK>
TrainingType
TrainingDate
TrainingPeriod
Comments

THEN:
tblRenewal
RenewalID <PK>
TrainingType <FK>
ExpirationDate (calculated from Training date and Training Period)

This table would ONLY be populated though IF both TrainingDate and TrainingPeriod were filled out.

Now another question. Since 7 of these trainings that have expiration dates they all vary in time. For example CPR is 3 years but Flagger Training is 1 year. I would be able to manipulate that TrainingPeriod field to whatever time period I need?

 

[RainLover]

Getting there.

In tblRenewal you need to record the EmployeeID as well as the Training Type.

 

[Bryan]

Now to make things more complicated.

Date-Expired is wrong. It should be a Text field called "Valid-For", "Length" or "Period"

You enter the length of time the Training is valid for. Like CPR may be 6 Months.

While I don't disagree with Rain's approach of calculating an expiration date instead of specifying it, my preference, FWIW, would be to specify the expiration instead of calculating it..

I think you have to consider how your certifications are awarded and expire to see if a calculated field makes sense.

For example, does the certification always expire a specified number of dates from when the training ends? Are there any certifications that require a test that might be taken on a day other than the last day of training? Do some certifications need to be approved and thus start at a later day than the last day of training? Do some certifications expire at the end of a month even though the training may have been taken during the month?

If any of those possibly apply, and you use a calculated expiration date, you will find yourself doing a manual calculation of days to expiration just to get Access to make its calculation properly.

My .02 . . . .

 

[RainLover]

Bryan

My point is Technical.

Your point is Practical, but not normalised.

Both have their advantages depending on circumstance.

On the other hand one could do both. There is no problem with too much information. It is better than not enough.

 

[KristenD]

Ok, all the trainings are different as well as the licensing. So CPR expires in 3 years, no test. First aid expires in 2 years, no test. Backflow Prevention and Installation is 2 years but requires you to pass a test. The licensing for plumbing in MD only requires a test if it goes past a certain point and only if it is a Master plumbing license. But New Hampshire does not. New Jersey has a PE certification that expires in 5 years but requires a test. So depending on the training/license would depend on the when it expires. Would it be easier to have a drop down box because all of them are always within a certain amount of years of when the certification was received?

 

[RainLover]

Kirsten

This is up to you. I really don't know what would be best in your situation.

Perhaps Two Fields.

One for Length of time and the other for an expiry date.

Once the Database is in use you will discover which is best and you can go back and fix it then. Besides there will be other things that you will have to fine tune.

 

[KristenD]

Thank you!! I appreciate all the advice. The first part of the database I designed had 3 versions before I got it where it worked the way I wanted. I would imagine it's going to be the same way with the second part!

 

[RainLover]

It takes time to gain the experience to forsee all the possible issues.

So you simply design and learn as you go.

 

[gemma-the-husky]

Kristen

your first post - not sure if it has been addressed in this

I will be adding tables but I'm not sure if I should just add one BIG table or split them up. There will be a lot of blank spots in the tables as not all employees will have all trainings and only certain employees will hold certain licenses due to where the job is.

none of this should be "optional". the normalisation process should guide you to the appropriate data structure. if you find yourself having separate fields in a table for different licenses, you have probably gone the wrong way. lots of nulls generally indicate analysis issues.

tables should generally be long and thing - this structure is more the way to go

employeelicensestable

employeeid
licencetype
licenceobtaineddate
etc etc

each employee has several rows, one for each licence, rather than a single row for each employee with different columns for the licence details

 

[KristenD]

I have been looking at the licenses, certification, and trainings and I think this is what the tables should look like

tblStateLicensing
LicensingID <PK>
EmployeeID <FK>
State
Trade
Category
LicenseNumber
ExpirationDate
MedGasLicense Yes/No
MedGasLicenseNumber
MedGasExpDate

tblOshaCert
OshaID
EmpID
Osha10Card
OshaTrainDate
OshaNumber
Osha30Card
OshaTrainDate
OshaNumber

tblOtherTraining
TrainingID
EmpID
There are 35 fields here.

But if I add yes/no fields to the trainings, if there is a no in the field I should be able to write something to ignore that field correct?

There is no way around not having null fields. The industry that I am in offers a lot of optional training and depending on the contract there maybe other trainings that are required. For example Langley AFB requires all employees to have Aerial Work Platform Training, but that is only one jobsite out of 20 jobsites. Or if we are working on a public hospital, they may require the foremen and superintendents to have BloodBorne Pathogen training. And if these guys come from other parts of the construction industry or other states, they may have other training that is required there but not at their current job site. Does that make sense?

 

[KristenD]

I am having a tough time trying to get my tables normalized. I'm thinking that I should have an additional table for a look up for all the other trainings and use it that way in a datasheet?

Also some of those trainings do not expire so with the 3rd table should I add a yes/no field that asks if it expires that way the expiration date field can be null if need be. Like I said before, only about 6 or 7 trainings have an expiration date attached to them.

 

[RainLover]

Kirsten

The best way for me to help is to look at the actual Database.

Can you post a copy. Please use Access Version 2003.

 

[KristenD]

Here is the relationship report and the slimmed down version of the database. I have not yet started adding tables to the database as I am trying to get it worked out on paper first. Thank you so much!!

 

[RainLover]

You have a few problems.

tblEmp. You should have two fields or even three for the Name. FirstName, MiddleName and LastName. You can always join them back together as one but to separate is much more difficult.

The fields in your tables should not be Lookups or Value Lists. I suggest you get rid of them. Create separate Tables like you did with tblRating and tblEmpRating.

If you are going to use ID then use it all the time.

In some tables you have used AutoNumber as the Primary Key. I would suggest that you do this with all the tables.

You are using Skill Code in tblRating and tblSkills but these are not being used as your keys. A review of your naming conventions would come in handy.

Have a look at my Sky Drive in my signature. I have a Document on Naming Conventions there.

 

[KristenD]

The reason why I did not do autonumber for the tblEmployee was too keep all records under one ID. I already have this portion of the database published, is there a way that I can go back and redo the tables like you suggested without disrupting the users?

The second half of this database will be used to track licensing, OSHA and other trainings/certifications. The licensing table will have expirations dates and I thought the table should look like this:

tblStateLicensing
LicenseID <PK>
EmployeeID <FK>
State
Trade
CategoryLevel
LicenseNumber
ExpirationDate
MedGasLicense <Yes/No>
MedGasLicenseNumber
MedGasExpDate

And the OSHA table like so since employees may have one or the other or both trainings:

tblOSHA
OSHAID <PK>
EmployeeID <FK>
Osha10 <Yes/No>
Osha10Date
Osha30 <Yes/No>
Osha30Date

Here is where I get stuck. I think I should have a table with all the other trainings and then another table where it joins that table kind of like I have the tblSkills and tblRating and tblEmployeeRating and I am just not sure how to get there. Someone had suggested to do it so I can list all the various trainings like in a datasheet all tied to the EmployeeID but I cannot get how to "link" them so it would tie together the right way.

 

[RainLover]

The reason why I did not do autonumber for the tblEmployee was too keep all records under one ID.

I do not understand this. Each Employee should have a different ID so you can tell them apart.

You are going to make lots of changes to your Database which will require updating the Users copy. So you need to learn how to handle that.

 

[KristenD]

Yes, I'm sorry I should have specified that better. Each ID is specific to one employee. For example John Doe's employee ID number is 1250 as this number identifies John Doe for all programs including the payroll and benefits system.

I installed the Auto FE Updater and from what I read on there any changes that I make will be communicated through that.

 

[RainLover]

I would keep the Current ID but add AutoNumber as the Primary Key.