Error tracking database design help

[Coleman984]

Well given the success of the last database, that jz helped me with (thanks again). I was hoping to get help with another database that requires charts and such. Which I still have no experience with access. Basically what I'm looking for is a database to track on which day a forklift driver makes an error, the type of error, which shift the driver belongs to.

The charts I wish to have are, individual charts, shift charts (showing everyone for a shift, one each shift) and a total chart showing both shifts. The charts be line graphs with dates being on the bottom (X axis i believe) (dates present even for dates without errors), and the y axis would be for number of errors.

Each chart would have 3 versions:

1 Week
4 Weeks
Total

I have attached 2 spreadsheets to show what I've described here. I managed to create this in excel but it is very cumbersome to manage and breaks easily.

 

[Coleman984]

I attempted to get started on this, but I'm getting confused already. If anyone wants to help I've attached my database that I've started.

 

[Coleman984]

I've kept trying to make this make sense. I feel like its getting closer to being coherent. But I can't get it to work still. I've attached another version of this database. With the attempts I've made to get the db structure correct. With this project I really want to learn this stuff throughly and completely so I can build my own DBs in the future.

If anyone has any input on corrections to my flawed setup. If you would be so kind as to explain the logic behind the changes throughly so that I can understand how to build a DB. What I mean is why a particular fk and/or pk was chosen. I know that fk/pk fields are used to connect tables together. But I do not understand the logic behind the connections. sometimes it is easy. But other times it is confusing. Example a table that stores shift names, how is this table linked to the table that stores employee names? I know this is done through fk/pk but which fields are linked and why?

 

[jdraw]

I have looked at your mdb. I think your table structure and relationships are not correct, but then again I don't know your business rules.

Open the mdb and look under Tools..Relationships.

I have adjusted your tables and relationships as they make sense to me based on:

You have basic tables for
Employees
ErrorTypes
Shifts
Zones (I added some for testing)

An Employee can work many Shifts
An Employee can work in many Zones
An Employee can make many Errors
An Error occurs for an Employee working on a Shift in a Zone
An Error can be made by many Employees
A Zone can be worked in by many Employees

I have not adjusted your Form.
I did alter a few table structures.
I did add some info to your tblEmpErrors.

In my view you have overemphasized the pk/fk in your naming of fields in tables.

My recommendation is to read the first few topics at the site below to understand normal forms and normalization. Also, you should work through the Entity relationship Diagramming with your data.

http://www.rogersaccesslibrary.com/forum/topic238.html

Good luck.