2 subforms linking to a main form

[Guest]

Seems like this should be easy but it's not working for me . . .

I'm creating a db that will contain basic employee address information, the
states in which they've worked, and the companies for which they've worked.
In this case each employee would only have one current address, etc., but
could have worked in multiple states and for multiple companies. I would
like to create one form that allows me to enter all of this info at one time.
So I've created 3 tables:

Name: contains ID (pri key), FirstName, LastName, and other basic address
fields
State: contains State ID (pri key), ID, and State fields
Company: contains Company ID (pri key), ID, and Company fields

The ID fields in the State and Company tables have a 1 to many relationship
with the ID field in the Name table.

I've created a form with FirstName, LastName, State, and Company fields
using the design wizard, however when I open the form to enter data, it is
blank (all of the fields exist when I switch to design view, however). If I
remove the Company from the form, it works as expected with several address
fields and a sub-field for entering multiple states. If I create Company as
a subform of State, the form contains both subforms, however the report will
not work correctly because it thinks that each state has several firms, which
isn't necessarily the case.

How do I correctly link the two independent sub-tables to the main table?

 

[Jeff Boyce]

A first observation -- change the name of your table named "Name". "Name"
is a reserved word in Access, so you will only confuse both Access and
yourself unless you change it. A common table name for information about
persons is ... Person, or tblPerson.

You may need to go back to the drawing board (no, literally!). Shut off
your computer and grab paper and pencil. Your data structure may benefit
from a bit more normalization. Designing the underlying data structure is
something you do with paper and pencil, sketching out possible tables and
relationships.

Based on your description, it sounds like you have persons, states and
companies. So far, so good. But you also described what sounds like
relationships involving persons and states, and persons and companies. Your
current design doesn't have any tables to hold these.

For example, if a person can have worked at multiple companies, and the same
company could have multiple persons who had worked there, you have a
many-to-many relationship. You can only resolve this in Access using a
third table, perhaps something like:

trelPersonCompany
PersonCompanyID
PersonID (from the tblPerson)
CompanyID (from the tblCompany)
DateEmployed (date person started at company)
DateLeft (date person left the company)

A similar situation would apply if you have a many-to-many relationship
between persons and states.