help

[Guest]

I have a couple of question.

1. Does it matter if you have the same primary key in different tables, or is it better to have an autonumber as the primary key instead?

2. I have a database set up with the following tables: Employees, Wages, Resignation, Training, and Training list. Employees includes these fields (Employee ID (pk), Name, Address, Phone, Date Hired, yes/no box for separated. My training list table has one field called Trainings, which is the primary key. Should I set another field to autonumber for that? The training table has the fields (EmployeeID, Training, Date Taken, Yes/no for if it was late, Previous due date, and Date turned in. The last two fields are only used if the person didn't renew their training by their previous expiration. I have an expiration field on the Training form that calculates the expiration date depending on each training, since they all have different lengths they are good for.

My problem is trying to figure out how to run a query that will pull up who hasn't had a training. Since the Training only has the field "training", which on the form lets you select from a drop down list to choose, I cannot get a query to run and show which employee hasn't had a class. I want to be able to run a query that will show the employee name and list any classes that they don't have a date in for. Since the training field can be several different items, I'm having a heck of a time trying to figure this out and I'm sure it's simple. Is there a better way of setting this up.

I'd also like to make query that will show who has a class ready to expire in a coming amount of time.

 

[Lynn Trapp]

Answers inline below

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

I have a couple of question.

1. Does it matter if you have the same primary key in different tables,

or is it better to have an autonumber as the primary key instead?
If you are wondering if Employee ID should be the primary key in both the
Employees table and the Training table, the answer is NO. That would create
a one-to-one relationship and a given employee would only be able to take
one course. Employee ID should be a foreign key in the Training table. An
autonumber field is not necessarily the best choice for a primary key --
especially if it is the only part of the primary key. Doing that can mask
the existence of duplicate records and you want to avoid that.

2. I have a database set up with the following tables: Employees, Wages,

Resignation, Training, and Training list. Employees includes these fields
(Employee ID (pk), Name, Address, Phone, Date Hired, yes/no box for
separated. My training list table has one field called Trainings, which is
the primary key. Should I set another field to autonumber for that? The
training table has the fields (EmployeeID, Training, Date Taken, Yes/no for
if it was late, Previous due date, and Date turned in. The last two fields
are only used if the person didn't renew their training by their previous
expiration. I have an expiration field on the Training form that calculates
the expiration date depending on each training, since they all have
different lengths they are good for.
If all the records in the TrainingList table are unique, then the one field
will probably be sufficient for your purposes, at least as you have
described them here. You might consider making Employee ID, Training, and
Date Taken the composite primary key for the training table.

My problem is trying to figure out how to run a query that will pull up

who hasn't had a training. Since the Training only has the field
"training", which on the form lets you select from a drop down list to
choose, I cannot get a query to run and show which employee hasn't had a
class. I want to be able to run a query that will show the employee name
and list any classes that they don't have a date in for. Since the training
field can be several different items, I'm having a heck of a time trying to
figure this out and I'm sure it's simple. Is there a better way of setting
this up.
You'll need to create an outer join on the TrainingList table.

 

[Guest]

Ok I've heard of an outer join...but what exactly is it. I know it has to do with the relationships. About the primary key, I don't have the employeeID as the primary key on the Training table since I realized that would only allow for one training per person, I was referring to if I made another table where I would only have one thing per employee ID, if it was ok to use it as the primary key there also.
Thanks

 

[Lynn Trapp]

An outer join is a join that allows you to retrieve ALL the records from
TableA and only those records in TableB that equal the join field in TableA.
In the scenario you describe, you still wouldn't want to use an autonumber
for the other table because there would be no way to keep the 2 in sync.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Ok I've heard of an outer join...but what exactly is it. I know it has to

do with the relationships. About the primary key, I don't have the
employeeID as the primary key on the Training table since I realized that
would only allow for one training per person, I was referring to if I made
another table where I would only have one thing per employee ID, if it was
ok to use it as the primary key there also.