Training Tracking DB (1 Viewer)

[RossWindows]

Writer's Block - Training Tracking DB

I have created a db that tracks training courses, classes and attendees.
A course can have many classes and a class can have many attendees.
The database also stores links to course documents and class notes.
I think there is a lot more I can do with this, but I'm having a bit of a writer's block.

Can I borrow some of your creativity?
Check out the sample and keep in mind I cannot change tblEmployees or tblSupervisors but I am open to suggestions on anything else.

Thanks!

 

[yanie]

could u reattach the training.mbd sample together with table and query

 

[RossWindows]

I don't understand. Is it not opening correctly?

 

[Kryst51]

Re: Writer's Block - Training Tracking DB

Wow, I like that! What about a log report? also, add new attendee if a new employee is hired? Or mark an employee as no longer working for the company? that way they still have the training applied, for ISO requirements, if you need them, but can be removed from reports.

I don't know if you already have them, but what about either a tab, or tool tips that tell you to add an attendee to a class, you have to add through classes? I didn't know that until I went to non-attendees, which was a little confusing. If you had a tab of faqs, that could be a great way to teach people how to use it. Like have that tab on there for the first couple of months. Maybe with a place for improvement suggestions as well, that way your users can suggest improvements...etc.

EDIT: Also, in the FAQ you could include how to identify the file path, not everyone knows how to do that.

I will look at it some more later. But this is really great. Would you mind if a) I took your idea or b) used yours at my company? Actually, that wouldn't work because I can't modify the users, so do you mind if I use your idea? I really like it! Excellent job.

 

[Kryst51]

What do you do if you move a document to a different path, or want to remove a doc from the training completely, how is that handled?

I am just throwing questions to think about out there, not knowing the whole scope of this db.

Edit: How do you remove and attendee from a class, say you clicked on someone by mistake?

 

[Kryst51]

I don't understand. Is it not opening correctly?

this person probably wants to use your db and modify it for themselves. Like me!

 

[Kryst51]

Another thought How about sorting options? Currently you automatically sort classes most recent first? What if you did a custom menu to have a sort ascending or descending according to Date/Time.

Also on your date field, where you put start date and end date, you might want to add some kind of input mask, as a user typically won't know how to add the time in the format you suggest, or have a seperate time field.

How about class location, are training's held in the same place? Or can it be like: "the boardroom upstairs", "Training Room 104"...etc.

Delete class option if it gets cancelled, or a check box to mark it cancelled to filter it out of the list of available classes. Then if it's a mistake, it's still in your DB. so a superuser could fix it. And you can track how many classes were cancelled in a month or whatever.

For your attendees, do you want to mark as Actually present or not present?

Do you have a list of trainings a person needs (Certain departments require specific trainings different from other ones)? then a way to tell that they have it or are scheduled for it?)

Edit: Without going into everyclass to search for it? So maybe an employee search and detail tab?

 

[Kryst51]

Hmmm, this could be part of the documents thing, but what about a course syllabus tab, so that a person can review that a head of time, plus an option for the instructor to revise it?

Edit: How a bout a date time picker? that way you can avoid mistyped date and times all aroung, more concerned about the time aspect of it then the date.

 

[RossWindows]

Thanks for the review. I really appreciate it.

So what I've gathered is this:
1. Better employee management is needed
I built this as an add-on to a much MUCH larger existing database which already had an employee table. I may decide to make the employees table a permanent part of this sample in the future.

2. FAQ and/or helping tooltips. Yes, I agree, there is a bit of a learning curve if nobody tells you how it works. I'll see what I can do.

3. Better document management ("what if a document is moved?...") Good question, I'll review and see if I can improve in that area. Or perhaps I can simply include a better instructional area that can deal with this.

4. Custom toolbars to sort lists in various ways other than the default.

5. Add a Class Location field. Great idea! Yes, our company has different training rooms.

6. Better handling of dates (possibly splitting date vs time into seperate fields). I think I have to disagree with this one. Calculating training durations, gaps etc is easier when date and time is combined in my experience. I will instead, add better instructions to assist the users or, like you said, use a date/time picker.

7. Deleting... whatever.
Anywhere you see a continuous form with record selectors, chances are you can select the record and use the delete key to delete it. I will also add this to the help section as most users wouldn't know this just by looking.

8. Marking attendees as present or not-present.
This is simply assumed by whether or not a related record exists in tblTrainingAttendees; no extra fields needed in my opinion.

9. Can you steal my idea? just kidding
Of course. The way I look at it; If I wanted to keep it to myself, I wouldn't have posted it here!

 

[Kryst51]

8. Marking attendees as present or not-present.
This is simply assumed by whether or not a related record exists in tblTrainingAttendees; no extra fields needed in my opinion.

9. Can you steal my idea? just kidding
Of course. The way I look at it; If I wanted to keep it to myself, I wouldn't have posted it here!

8. I guess, I am looking at it from a Quality, ISO standpoint of a new employee may require training, be scheduled to take it, but be sick that day and miss it, so need to be scheduled to a new class. Also job descriptions usually include a list of "training needed."

9. Thanks! I'd rather ask than not, to be on the safe side.

Keep up the good work, it is excellent!

 

[HiTechCoach]

I don't understand. Is it not opening correctly?

This may be because you have the database "locked down" so it is difficult to see the underlying design.

When posting to a form like this, it is probably better to "unlock" your database. You left no way for someone to easily look at the design.

 

[yanie]

i really like your database...creative design...if u dont mind,,can u share ur database with us...

tq

 

[RainLover]

I would like to suggest a different Naming convention for your primary and foregin keys.

Currently all Primary keys share the same name. Could be a bit confusing if the database grows.

Try naming it after the Table.

So for tblTrainingClasses the Primary key should be TrainingClassesID.

Then the Foregin key should be the same name as the Primary key.

Hope this helps.

 

[RossWindows]

Great suggestion RainLover.

I am currently revamping the design. Please stay tuned for an updated copy in the near future.

Changes and improvements will include:
1. Primary & Foreign Keys will follow a naming convention consisting of a 3 or 4 character prefix. E.g. tblEmployees' primary key will be named EmpID and will also appear the same way when used as a foreign key.

2. Table structure will be changed and expanded to handle multiple training locations and sublocations therin. E.g. A company may do most of their training on site in a single room, multiple rooms, or even at a different facility. I will be adding two tables:
tblLocations and tblSubLocations. tblClasses will be joined to tblSubLocations

3. tblEmployees will be modified so the database can be used by various industries that may or may not already have an employees table they can link to.

4. Other tables being added: tblGroups, tblGroupCourses (junction) and tblGroupEmployees (junction). These tables will allow the administrator to predefine what classes are applicable or required to certain groups of employees.

For the next few days I will be primarily focused on verifying table relationships (making sure it all works in the real world), then I will focus on the changes described in post #9

Thanks for your suggestions. Keep 'em coming!

 

[RainLover]

That looks like a pretty good design.

If you want to improve,

Take clsInstructor out of tblClasses and place this in a separate table of say tblInstructors.

Also in tblLocations you could remove locCity and locZip into another table. locState would also belong in a table of its own.

This is Normalisation taken to the next degree.

Look forward to your next update.

 

[RossWindows]

I started evaluating the work involved in incorporating seperate tables for city, state and zip. I feel like it might be out of the scope of this project. It would require updates any time a new zip code is created, not to mention the overhead (forms, queries etc.) needed to facilitate an admin making these changes. What are your thoughts? Pros and Cons?

Also, creating a seperate table for instructors might simply mirror that of tblEmployees, which makes me question whether or not to make it a seperate table or keep the instructor selection based on records in the employees table. Your thoughts?

 

[RainLover]

If the Instructor is also an employee (Not an outside contractor) then use the employee table. But in tblClasses clsInstructor should be a foregin key to the employee table.

With regard to Zip Codes you need a table like this.

tblZipCodes

zipID as Autonumber
StateID as Long and Foregin key to tblStates
City as Text
Zip as Text (or Maybe Long, not sure what the zips are in your country)

Another Table for States

tblStates
StateID as Autonumber
State as Text.

As far as overhead is concerned you could populate the State table quite easilly and it won't change.

As far as the Cities is concerned then I would not advise populating this table until such time as you need a new city.

If a user types in a new City that is not listed in the city table then there is an event called "Not on List". By using this event you can build the contents of the table as and when you need it. A bit anoying for the user in the beginning but it will build quickly and only use the cities that are required.

The big advantage is that you have intregity in your Data. Like New York City will always be "New York City" and not "NY City" or some other varition. Also the Zip will be correct every time. If an incorrect Zip is entered it can be changed in one place and all entries are fixed.

If you search "Not on List" there are examples of code out there that handle this event.