Database Help Needed Please (1 Viewer)

DGagnon819

Registered User.
Local time
Today, 04:30
Joined
Jun 17, 2006
Messages
13
Dear Experts,
I am a Access rookie. While browsing these forums I've noticed that the people here seem to be very knowlegable about database design and normalization. I'm writing because I need expert instruction on a database I'm working on.

I've posted a couple times concerning my issue but so far I've not been able to solve my problem. You see, my database is to be used to track employee training events. It should be able to record events which pertain to every employee and also events which pertain to individual job specialties. This is so I can produce reports which show all events which are mandatory or are specialty related whether the employee has completed them or not.

I one time I believed I had the foundation but now I'm uncertain. To produce the report I mentioned above, I've tried every SQL join I know of without success. This had led me to question my relationships and the tables themselves. It doesn't seem like this should be hard to solve but it has beaten me so far.

The Tables are as follows:

TrainingEventFrequency
FreqCode (PK)
CodeDescription
Days

TrainingEventInfo
TrainingEventName (PK)
FreqCode
EventCode

CompletedTrainingRecord (Don't know what to make PK)
TrainingEventName
DateCompleted
ClockNumber
EventCode

EmployeeInfo
ClockNumber (PK)
FirstName
MiddleInit
LastName
JobCode
JobTitle
PositionCode
PositionTitle
DepartmentID
SectionName
WorkPhone
EMailAddress
DepartmentManager
SubContractor
ProjectMandatoryCode

JobCodes
JobCode (PK)

Department
DepartmentID (PK)


When a employee is entered into the database, a default value is entered into the ProjectMandatoryCode field. I was hoping to use this to link the Project Mandatory events to all employees. Using the Job Code, I need to link events specific to their specialty.

When an event is entered into the TrainingEventInfo table, I use a form which has an option group and combo box controls. When the user selects the 'Project Mandatory Training' option, a default value is entered against the event. This value is the same as the one entered into the EmployeeInfo table when a record is created. When the user selects the other option, 'Position Related Training', a combo box is enabled which is populated from a query of the JobCodes table. My idea was that when the event was entered into the table that it would have a code that either identified everyone or just a specific specialty. Hopefully this would allow me to later produce a training plan based off the combined events (Project Mandatory and Position Related).

Once the employee information is entered and the training event information is entered then it would be possible to match an event against an employee. I've developed a form for this purpose. It works essentially the same way as the form used to initially enter the training event into the system with some differences. Instead of the user typing in the Training Event Name, they can select from a combo box which is sorted by an option group control. If the user selects 'Program Mandatory Training' then a default value (the same value entered when the employee record is created and when the event is entered into the system). If the event is Position Related Training then the user selects the appropriate option and a combo box is enabled which allows them to select the appropriate Job Code. The employee information is selected from a combo box, the date is a calendar control. Basically, I've tried to make it as full proof as my meager skills allow.

I've looked at my tables, and their relationships, until I couldn't focus my eyes anymore. :eek: Has anyone else encountered a problem like mine? Or, is my problem self-inflicted?

If you've made it this far then I hope I've made some sense to you. I've attached a .doc with the relationships for reference. Thank you in advance,
Dale Gagnon
 

Attachments

  • Test(2).zip
    38.8 KB · Views: 285

Len Boorman

Back in gainfull employme
Local time
Today, 08:30
Joined
Mar 23, 2000
Messages
1,930
Okay a few pointers
1) You do not have the links set with Referential Integrity enforced
2) You need a link from Employee Info.ClockNumber to CompletedTrainingRecord .ClockNumber
3) You need a link fromTrainingEventInfo.TrainingEventName to CompletedTrainingRecord.TrainingEventName
4)EventCode in CompletedTrainingInfo is redundant
5)CompletedTrainingRecord should have a primary key, suggest TrainingEventName and ClosckNumber as a combined key providing that a person only goes on a training event once

Make sure you set the links in the relationship diagram and that they are set with referential integrity enforced.

Hope this helps

Len
 

DGagnon819

Registered User.
Local time
Today, 04:30
Joined
Jun 17, 2006
Messages
13
Thanks Len

Dear Len,
As I sat at my desk I made almost every change you suggested without even seeing your post. Seeing that you've recommended most of the same things gives me confidence.

I even made the combined key in the CompletedTrainingRecord as I couldn't see another way of joining the tables without duplicating data. You're right about the EventCode being redundant. Speaking of unique codes, I incorporated the ones I made for major types of training into the JobCodes table. To me this makes sense since now all entered training can now be set against a specific code. I've attached the updated structure for reference.

I'm currently working on the forms and controls. I hope to make them intuitive and easy to use. make good progress in the next few days. Thank you again for your help,
Dale Gagnon
 

Attachments

  • Test(3).zip
    38.2 KB · Views: 281

Users who are viewing this thread

Top Bottom