Question My First Database...*sigh*

[Slayne]

Hello anyone,
I am using Access 2003. I'm trying to create a database to keep track of all the Task Training for the Safety Dept. I have created my tables, but for some reason can't get past the relationship part. I can't grasp this. Please look over the attachment when you get time and tell me where I'm wrong.


Thanks in advance for the help!

 

[Guus2005]

from your picture i can see that every auditor is an employee and every employee an auditor (1:1 relationship)
An employee can issue a task (1:N) and a task can have multiple records in the completed audits table (1:N).

You have to ask yourself is this what i want? This could be the right setup in your specific case. I don't think so but it is possible.

To be certain, more information is needed.

HTH

 

[Slayne]

Thank you for the reply.

It sounds confusing but yes, I think this is what I need because every Auditor is an Employee but not every Employee is an Auditor. Also any Auditor can audit any Employee including other Auditors at any time.

Should I stick with Excel for this and use filters?

 

[Guus2005]

You can use Excel to do it but with a little more effort you could use Access. You have to start by creating a normalised database design.
If you're not sure how that works, visit wikipedia.
Put your design here and we'll discuss it.

HTH

 

[Slayne]

Would someone take a look at this attachment and see if I'm on the right track yet, please? No eye rolling please. I looked up several places regarding normalised database designs. I thought I understood most of it while reading. Then I get confused when creating my own.


Thanks again.

Oops, I suppose none of you are mindreaders. Here's what I need the database to do:

All Auditors are Employees, but not all Employees are Auditors. All Employees including the Auditors are eligible for an audit at any given time by any Auditor. What I'm wanting to do is keep track of:
1. What tasks were created, when and by whom (Auditors get extra points for any new tasks that they create once approved)
2. When audits were done, by which auditor, and which employee was audited
3. Each Auditor has to complete so many of these either per week, per quarter, per month, or per year...depending on the Auditor. I have to keep track of that.

I would like to be able to later be able to go back and see how many each Auditor has completed at any given time. I would also like to be able to see if the Auditors are auditing the same Employee over and over or if they are continuously using the same tasks for the audit repeatedly.

 

[Chrisopia]

Look up database normalising for college level ICT.

It tells you all about relationships. Access prefers 1:1 or 1:many for data to flow.

For example 1 teacher can have many pupils, but 1 pupil can have many teachers (Many:Many relationship). So to normalise this you would create an intermediate table called Class.
[Teacher]---<[Class]>----[Pupil]

Your employees table needs an Audit ID to link to or vice versa, or create an intermediate table, depending on the use of the tables.
Think through and plan ahead.

Getting the relationships right can help you save time in the long run. It minimises the risk of repeated data and human errors... if that makes sense.

 

[Guus2005]

Here are some pointers/questions you might want to ask yourself when creating a database

Is the auditors name different from the employees name?
Is it possible to have incomplete audits in the "complete audits" table?
Is it possible to have a task which is not in the "All Tasks" table?
You might want to consider a different name.

About names: don't use quotes, spaces or slashes in fieldnames, dont use plural names for tables. Keep them simple and to the point. Quotes, spaces and slashes might get in the way when writing queries or code.

The tables you added like YearlyQuarters and Departments are reference tables and can be used when creating queries. Reports is a table used by your frontend when you need to fill a combobox with all available reports.

I am not sure what the AuditsReq/Quarter field does.

HTH