Append Queries/Link Tables

  • Thread starter Thread starter Sharon
  • Start date Start date
S

Sharon

I have a table with our Firm # (primary key is the firm
#) and another table with inventor information (primary
key is an autonumber). I have created a link table with
the primary keys as the firm # and the inventor auto
number. There is more than one inventor for each firm
#. I am trying to append the information from the two
tables to my link table, but am doing something wrong.

Also, how does the Firm # Table know how many inventors
there are. Basically, I need a Queries for Dummies
course.

Any help is appreciated and thanks in advance.
 
Dear Sharon:

What do you mean, "I am trying to append the information from the two
table to my link table." I assume that selected inventors are
associated with each firm. Each firm may have 0 or more inventory,
and each inventor may be associated with 0 or more firms. However, if
every inventor were associated with every firm, then there's no need
for a link table. So, I assume it is anticipated that not every
inventor is associated with every firm.

The problem then would be to provide a method of data entry that
allows users to create and edit a list of inventors for each firm
and/or a method of data entry that allows users to create and edit a
list of firms associated with each inventor.

I would typically program this using a main form that shows a firm and
a subform listing all the inventors for that firm. The inventor would
be shown in a combo box listing all the inventors not already selected
for that firm. This way the user can select additional inventors to
add to the firm's list of inventors.

A simple reverse of this design allows editing a list of firms for
each investor.

Your question about the Firm table "knowing" how many inventors there
are doesn't really make sense. Perhaps you could rephrase this
question in terms of design and programming decisions you are facing.

This newsgroup IS the "Queries for Novices and Above" course. Tuition
is low, and your selection of instructors is very broad (I know I'm
certainly getting a bit broader all the time!)



I have a table with our Firm # (primary key is the firm
#) and another table with inventor information (primary
key is an autonumber). I have created a link table with
the primary keys as the firm # and the inventor auto
number. There is more than one inventor for each firm
#. I am trying to append the information from the two
tables to my link table, but am doing something wrong.

Also, how does the Firm # Table know how many inventors
there are. Basically, I need a Queries for Dummies
course.

Any help is appreciated and thanks in advance.
Click to expand...

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
I am not sure what you are trying to do here but the first thing you should
think of is the relationship between the Firms and the Inventors. As you
already wrote a Firm can have more than one Inventor but you have not
indicated whether an Inventor can be associated with max. of 1 Firm or
possibly many Firms.

If the later (more than 1 Firm) or you want to keep the history of
associations between Firms and Inventors, e.g. an Inventor is associated
with only one Firm at a time but during his working life he will have
different associations at different times, then you have a Many-to-Many
relationship and in this case, you need the Link Table.

OTOH, if an Inventor can only be associated with max.one Firm and you only
want to keep the current association, then you don't need the link Table.
You only need to have a ForeignKey Field in the tblInventor. This in a One
(Firm) to Many (Inventors) relationship.
 
Back
Top