Relationships/Data Organization

[mixfeat]

Hi there!


I need help with organizing data. The database should contain annual Health & Safety tests results for each employee. Although, I'm not very familiar with MS Access, I thought that building a huge table holding all info wouldn't be the best way to go. Building separate tables for each year doesn't seem like a particularly good idea either, considering that Employee names and info would be the same in all of them. Now, is it possible to have one table with Employees names, departments etc. (e.g. Employees) and several tables containing tests results (e.g. Test2008, Test2009)? How do I link them together? Your help is REALLY appreciated. Thanks!

[orange]

Here's a link with several good references
http://www.rogersaccesslibrary.com/forum/topic238.html

Briefly look at the Entity Relationship Diagramming,
then go back and read the Normalization and Normal Forms.
Then work with your own data and do the Entity Relationship Diagram.

As a first guess, I would suggest
Employee -id,name,address,phone,BirthDate, etc
Test -id, name of test, description, info about the test not employee

EmployeeTest - id, id_ofEmployee, id_ofTest, Date ofTest, resultOfTest

You may also have a Department table depending on your company's size, number of Employees etc.

This structure will allow you to have as many test per employee as you want, since the DateOfTest, EmployeeId,and TestId are readily available. No need for separate tables for each year.....

Good luck

[mixfeat]

@Orange

Thanks, I'll definitely take a look at that.