Solved

Access 2007 - relationship

Posted on 2011-02-17
5
304 Views
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.
0
Comment
Question by:jegajothy
5 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 167 total points
ID: 34919657
you need to create a junction table

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



see this demo

http://office.microsoft.com/en-us/access-help/demo-set-the-relationship-between-two-tables-HA010254901.aspx?pid=CH100739911033
0
 

Expert Comment

by:senatort77
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
0
 

Author Comment

by:jegajothy
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
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 333 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.
http://support.microsoft.com/kb/304466
http://www.databaseprimer.com/relationship.html

A typical One to many is like this:
tblCustomers
CustID(PK)
CustName
CuatCountry
...ect

tblOrders
OrderID(PK)
CustID(Fk)
OrderDate
Shipper
...etc

In other words, One customer can have many Orders.


A typical Many to Many would be like this:
tblClasses
ClassID(PK)
ClassName
ClassLocation

tbStudents
StudnetID(PK)
StudentName
StudentPhone
...etc

tblStudentClasses
StudentClassID(PK)
StudnetID(FK)
ClassID(FK)

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.

;-)

JeffCoachman
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 333 total points
ID: 34962208
...in 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:
http://databases.about.com/od/specificproducts/a/normalization.htm
http://www.phlonx.com/resources/nf3/
...et al

You also have issues with not having a Standardized naming convention for your tables or fields.:
http://en.wikipedia.org/wiki/Leszynski_naming_convention
http://www.acc-technology.com/namconv.htm

http://www.xoc.net/standards/rvbanc.asp
...et al

JeffCoachman

0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now