Re: help

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Lynn Trapp (ltrappNoSpam_at_ltcomputerdesigns.com)
Date: 07/06/04 

Date: Tue, 6 Jul 2004 14:31:51 -0500

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
"Shanin" <Shanin@discussions.microsoft.com> wrote in message
news:329BACEC-1A29-475E-AAB0-621CAC318D24@microsoft.com...
> 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" wrote:
>
> > Answers inline below
> >
> > -- 
> > Lynn Trapp
> > MS Access MVP
> > www.ltcomputerdesigns.com
> > Access Security: www.ltcomputerdesigns.com/Security.htm
> >
> >
> > "Shanin" <Shanin@discussions.microsoft.com> wrote in message
> > news:77864005-9B23-4A07-AF81-CA28F088354E@microsoft.com...
> > > 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.
> >
> >
> >
> >

Relevant Pages

  • Re: New to Access db
    ... The Foreign Key fields in the Subform which arentt linked to the main form ... Add EmpID and Employee to the query. ... AttEventID - Primary key Auto Number ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Understanding Relationships
    ... have pages of things they might track about an employee. ... and for which a history is to be kept MUST be in a related (child) ... primary key of this employee's record in tblEmployee. ... Table 2 [SalaryScale related to Table 3 SalaryScale] ...
    (microsoft.public.access.gettingstarted)
  • Re: Understanding Relationships
    ... have pages of things they might track about an employee. ... and for which a history is to be kept MUST be in a related (child) ... primary key of this employee's record in tblEmployee. ... Table 2 [SalaryScale related to Table 3 SalaryScale] ...
    (microsoft.public.access.gettingstarted)
  • Re: WWW/Internet 2009: 2nd CFP until 21 September x
    ... Firsst_Name char 25 not null, ... Noe let's say that the rules are that you can't put an employee into the table unless you know the First Name and Last Name, and you get assigned to the employee an Employee_Id that's never been used before. ... (Employee_Id integer primary key foreign key references ... There's no nulls allowed in the two table solution, but there's no law that says you can't leave an entire row out. ...
    (comp.databases.theory)
  • Re: help
    ... MS Access MVP ... Does it matter if you have the same primary key in different tables, ... If you are wondering if Employee ID should be the primary key in both the ... > My problem is trying to figure out how to run a query that will pull up ...
    (microsoft.public.access.queries)