Relationships in Access2007

[kgoo]

I have been trying to pull together an agency database. One agency can have
many agents; one agent can have many roles. I would like to keep the agency
(company information) separate and tie the company to the agent by a unique
agency code.

I created 4 Tables:
Agent Table - Agent ID (autonumber) is primary key
Company or Agency Table - Agency code is p.k.
Roles Table - Role ID (autonumber) is p.k.
AgentRoles Table - AgentRoleID (autonumber) is p.k. and includes AgentID,
Agency Code and RoleID.

I tried creating the relationship by tying all tables directly to the
AgentRoles Table, however, when I go into the query or form I can not update
either.

What am I doing incorrectly?

Ultimately I would like the form to have the agent's name; company; agency
code and different roles.
ABC agency - Agency code 123456
Donna Sue is president, treasurer and organizer
Jamie Eason is secretary & accountant.
How can I create a drop down next to each of their names to identify each
role in case we need to change each role.


Thanks

 

[Allen Browne]

So you have companies (agencies) who have agents (persons) operating in
different roles. From the structure you suggest, I take it that these are
many-to-many relations, e.g.:
- one agency can have many agents, but one agent can also work for many
agencies.
- one agent can have multiple roles at one agency, and many agents can have
the same role at an agency.

If that's the kind of thing you are moddeling then the structure you have
should work. To create the relationships, you would open the Relationships
window (Database Design tab of the ribbon in Access 2007, or Tools menu in
previous versions), and drag:
- Agent.AgentID to AgentRole.AgentID
- Agency.AgencyCode to AgentRole.AgencyCode
- Role.RoleID to AgentRole.RoleID.

Now you won't be able to manage these all in one form. You will need one
form for entering new agencies, and another for entering new agents. The
Agent form will have a subformbased onthe AgentRole table. In the subform,
you may be able to use a combo box for selecting which agency the person
works for (unless you have many thousands of agencies), and another combo
for selecting the role.

Similarly, the Agency form could have a subform based on the AgentRole
table. This subform would show the person who works for the agency, and
their role.

With those relationships in place, you should have not trouble editing the
records in the subforms.