Re: help
From: Shanin (Shanin_at_discussions.microsoft.com)
Date: 07/07/04
- Next message: Mark: "Indexing Tables created from the "Make-Table Query""
- Previous message: NoSpamTakeSquareRootOfNumber: "Re: append query overwrites all matching records"
- In reply to: Lynn Trapp: "Re: help"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 7 Jul 2004 06:17:01 -0700
Ok, thanks.
Here is what I have tried thus far. I made a query with the Employee table, Training table, and Training list table. I tried making and outer join from the Employee table and Table list to the Training table but I get the ambiguous outer join message. I dropped the training list table off and just left the employee table and switched to a totals query, putting employee ID as null, and that works on bringing up anyone who has not had a training.
I'm guessing in a new query I'd need to do the same with the training list but put the Training as null?? How do I bring that first query in the second one, just put it in the criteria or in the field name in design view??
"Lynn Trapp" wrote:
> 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.
> > >
> > >
> > >
> > >
>
>
>
- Next message: Mark: "Indexing Tables created from the "Make-Table Query""
- Previous message: NoSpamTakeSquareRootOfNumber: "Re: append query overwrites all matching records"
- In reply to: Lynn Trapp: "Re: help"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
