Probably a "cut and dry" one

[ShannonMarie]

I am building a db for a small fire department. On the "participation" table there are fields for the year, rescues, fires, total calls, and a few more for trainings and meetings. The idea that I need to meet is a list box with all years that the employee has been there and the amount of calls for each year. Seems like this should go in a combo box together. Would it be best to create a history table and leave this out of the participation table? Thanks in advanced for any offered solutions, as I am still learning.

 

[Jack Cowley]

Hmmm. I think that your table should really be a query, sorta. Something like 'Total Calls' would be, I assume, a total of rescues, fires and/or other incidents. Since this is something that changes day to day you would calculate the total, not store it. What I just said does not address the issue of meetings, trainings, etc. but I suspect that your database my not be properly normalized unless this is not a database that is used on a daily basis.

Without knowing a thing about your database I would suggest that a couple of your tables might looking something like this:

tblFirefighters
FirefighterID (PK)
LastName
FirstName
...Other fields...

tlbIncidents
IncidentID (PK)
FirefighterID (FK)
IncidentDate
TypeOfIncident (training, fire, cat in a tree, etc..)

That is just a beginning as there are many other considerations, but maybe this will give you some ideas about normalization and how you might keep track of your data.

hth,
Jack

 

[ShannonMarie]

A little more information

I had not yet considered that the fields "rescues", "fires", and "total calls" are calculated fields. Yes they will need updated when the record keeper enters who went on a call. The idea is a history table so that he can look up years past in comparison to the present year. He would like a list box with the history information with the ID and name of the fireman. He wants a query to pull the top three employees for each category yearly for recognition purposes. I was thinking when year = year + 1 to have the query run and to automatically archive the yearly info for each employee. (Thats the idea anyway) I also need to somehow list the trainings and meetings in a drop down for each employee. How do you add those values without adding additional rows to the table? I have redone and redone much of what I am attempting, so it has been trial and error the whole way through. I am grateful for your assistance, it helps me soooo much more than Advil! Thanks--again-- for any help that you may be able to offer. I can't complain too much, experience is the best teacher right? P.S. I worked hard to ensure that the db is as close the 3NF as possible. I left "state" in the main table, as it is for a small personnel load.(although it is nondependant on EmpID) My instructor OKed the normalization before I could begin development. However she is (as much as I love her) very reserved about helping with any design questions. She believes very much in trial and error. I do too, but not to the extent that she does. Rebuilding is a necessity at times, but when it is continuous it tears at your confidence.

 

[Jack Cowley]

My suggestion to you is to get your tables set up before you worry about queries and how you are going to display the data. You will need to be sure that your tables are normalized and that your structure is correct. Once you have your tables normalized and properly related then the queries and the rest will come much easier. Normalization and table structure is the heart of a relational database and probably the hardest part to clearly understand.

You asked, "I also need to somehow list the trainings and meetings in a drop down for each employee. How do you add those values without adding additional rows to the table?" Now I don't know if meetings and trainings can be considered a single table as I don't know what you want, but the structure below would work if it would be appropriate to have these items combined rather then in a single tables.

tblMeetingsTrainings
MeetingsTrainingsID (PK)
InstructorID (FK)
Date
Subject
...other fields...

tblMeetingsTrainingsAttended
MeetingsAttendedID (PK)
MeetingsTrainingsID (FK)
FirefigherID (FK)

tblFirefighters
FirefigherID (PK)
LastName
FirstName
...other fields...

tblInstructors
InstructorID (PK)
LastName
FirstName
...other fields

Please do not take this setup as gospel, but it should be close. And consider that a firefigher could also be an instructor so that is one more wrinkle to add to the mix, which I haven't done. If Pat Hartman happens to stop by she can set us both straight on my suggested table setup for that part of your database.

I hope this helps and if you have questions about normalization you will find a simple but reasonably good tutorial here.

hth,
Jack

PS. After posting this I saw that you edited your post so please disregard my suggestion about normalization as I did not realize that you have an instructor, stingy or not....

 

[ShannonMarie]

If you care to take a look, here they are

These are the tables that I have. I thought that I was ready to begin "Archive" or "History" tables. But I have had to make quite a few changes. So I am going to need to change form sources somehow. That is where I'm at currently. That is what made me begin the questions of how to best do so. Thank you so much for your help.