Total noob needs a helping hand?

[Naz]

Hiya folks,
I'm trying to build a required training DB for the employees at work. I have exactly 2 days of experience "playing" with Access. Here is what I have so far.

I have an employees table which includes fields for employee #(PK), first name, last name, address, department ID, and Team ID.

I have a Department table which includes Dept ID(PK) and Dept Description

I have a Teams table which includes Team ID(PK) and Dept ID

I have a Courses table which includes Course #(PK), Description, format, frequency, and whether or not it is a regulatory requirement (yes/no)

Here is what I want to be able to do.
See employees within a department and within their respective teams. This works

See the regulatory required courses for an employee and by department. I'm lost and have no clue

Have an easy way to see what employees need or have completed a certain course by course number. Lost again

Pull up a department and/or an employee and have a report of what courses are recommended for a certain year. ie. some course frequencies are 36 months so they may not need them this year. Totally lost.



If I could please get some advice on these things I might be able to figure out the rest.

Thanks so much,
Naz

[jbarrum]

The first thing you need to decide is how you define required courses. Is that defined by what the course is (so it will be requried by all employees). Or will the requriement be based on what department or team that employee is in?

These things make a huge difference in designing your table structure.

[Naz]

Hi Jbar,
Thanks for the reply. Required courses are determined by Department. The only reason the teams are in there at all is for scheduling purposes. The teams work swing shift so they are only on dayshift during the week once in a month. So if I have a course, # 89777, Environmental awareness, required for Operations, FGD, and Lab staff. I want to be able to query that course number and pull up a list of employees required to take that training. I also want to be able to query a department (such as Lab) and see all the courses that dept. needs for the year. I hope I'm explaining it well?

Thanks again,
Naz

[jbarrum]

In this case what you will need in addition to your [department] table and [courses] table is a third table [ReqCourses] that defines what courses are required for each department.

[Naz]

So that would look like

Dept_ID(PK)|Course#|Course#|Course#|



or how? I'm sorry if this is a really dumb question but like I said in my OP the first time I opened Access was yesterday and I've been playing and reading every since. Thanks.

[jbarrum]

Not exactly. You could have a ReqCourse_ID as the primary key but this would not prevent someone from adding the same course back to that same dapartment more the once.

I would use a combination of of deptID and CourseNum as your PK so that you do not end up with any duplicate records. Basically your table will have two primary keys. This will prevent somone from assinging the same course to the same dept more then once

[Naz]

Ok so lets say that Lab is required to attend 50 training courses for this year. I need to name the first field Dept_ID and make it a primary key. Then I need to add 50 more fields for the course numbers? I guess I'm not seeing how this will work. Some departments are required to take more than a hundred classes in a year so how will I make them PKs as well?

[jbarrum]

Naz,

I have attached a small DB tha I put together to help you get started. This is only a starting point, as I am not fully aware of exactly what informtion you need to store or how you need to see or report on that information. However, take a look at teh table relationships and you will get a better understanding of what I was talking about.