If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Relationships in Access2007
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 |
#2
|
|||
|
|||
Relationships in Access2007
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. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "kgoo" wrote in message ... 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. |
Thread Tools | |
Display Modes | |
|
|