Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Access 2007 - relationship

Posted on 2011-02-17
Medium Priority
Last Modified: 2012-05-11
I am at a loss in setting up the following design.
My OS is windows 7.
I have a main table called tblContacts, and one of the fields is called 'Responsible for'.

There is another table called tblResponsible4' which has only 2 fields, 1 is an ID and the other called Resp4.

One contact can be responsible for more than 1 item.  But  there is only 1 field on the main table called 'Responsible for' to capture the data which is entered via Form using a combo box.   Would I need to duplicate the record and then enter the new data for the 'Resp 4'
eg : Mr. A is responsible for 'Food' and 'Drinks'
or even more than 2 or just only 1 item.
Hope someone can send me a simple design of  how this is achieved. thank u.
Question by:jegajothy
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 668 total points
ID: 34919657
you need to create a junction table

tblContacts          contactResp            tblResponsible4
ID            1 ------M   ContactID
                                RespID      M ------ ID

see this demo

Expert Comment

ID: 34920270
make relaen tion between the 2 tables in sort to give the person id to the items
ex: the ID 4 mr a to food and drinks

Author Comment

ID: 34921344
Can someone please take a look and fix the attached table.  It has only one record in the Committee table and in the main table 105.  I just cannot understand the logic or how to do it. thank u. email-database.accdb
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 1332 total points
ID: 34962090
1. <I have a main table called tblContacts, and one of the fields is called 'Responsible for'.>
Not in the DB you posted...

2. Even so, the SeniorsClub table is missing a primary key...
Hence you cannot set up a true 1-->M

3. It is difficult to tell form the DB you posted, if the relationship needs to One to Many or many to many.
<One contact can be responsible for more than 1 item.>
...note that your sample does not contain an Items table either...
causing further confusion...

<I just cannot understand the logic or how to do it.>
Then please study up on relationships *before* building and relating your tables.

A typical One to many is like this:


In other words, One customer can have many Orders.

A typical Many to Many would be like this:



In this case one Class can have many Students *AND* one Student can have many Classes.

So again, it is not clear what your actual structure *should* be.

So study what we have all posted here, and post back with your revised Question.


LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 1332 total points
ID: 34962208 a nutshell, the tables in the DB you posted (especially the SeniorsClub table) do not appear to be properly "Normalized".
So you may want to research this first: al

You also have issues with not having a Standardized naming convention for your tables or fields.: al



Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question