I can never get it the way you said. When I tried it, I only get one
commond
field, only the subject field. Le'ts me explain it step by step
1. I create a table names "People", field PeopleID primary and autonumber,
FirstName text, LastName text
2. I create a second table names "Communication", CommunicationID primary
and autonumber, Subject text, Body text
3. I create the join table, and I name it "JoinTable", PeopleID primary
and
autonumber, and CommunicationID number. It does not make any difference
even
when I remove primary from PeopleID
4. I click on relationship icon then I show all three table, since the 3rd
table is a join table, I put it on the midle than the people table in the
left then communicaton table in the right. I drag the PeopleID field from
the people table to the PeopleID field on the join table, then I drag the
CommunicationID field to the communication table to the CommunicationID on
the join table.
5. Given that I already get data on the people table field, when I open
the
table to type something, only the subject field is common for each people,
not communication. I try couple of times, it does not work.
Ken Snell (MVP) said:
Fairly easy to do. Create a new table as I have outlined. The PeopleID
field
will be the foreign key field for the value in your people table; the
CommID
field will be the foreign key field for the value in your communcations
table.
You then can create relationships between the PeopleID fields in the two
tables, and between the CommID fields in the two tables.
I don't understand your comment about removing the primary key from
communications table and then establishing a relationship with the
PeopleID
field in people table? Why would you do that? Those two fields, as far as
I
can tell, have no relationship with each other? Also, the communications
table needs a primary key, so you should not delete it.
Use the third table (the junction table) to set up the relationships.
You then can use queries to get a list of all the CommID values
associated
with a single PeopleID value; and vice versa; using the third table.
--
Ken Snell
<MS ACCESS MVP>
Jean Dophin said:
I haven't tried your solution, but what I did I removed the primary key
from
the communication table and it works fine. When I drag the personid to
the
communication table, it did create a one to many relationship and I get
exactly what I want. Although I get what I want, but it may not be
correct.
Can you provide me a step by step instruction on how to create a
junction
table to make that work?
:
You need a third table (called a junction table) to represent the
relationship between people and communication:
tblPeopleComm
PeopleID (composite primary key with CommID)
CommID (composite primary key with PeopleID)
Each record in this table represents the assignment of one CommID
value
to a
PeopleID value. (A composite primary key is a multi-field primary
key.)
--
Ken Snell
<MS ACCESS MVP>
I have the following table and I think I need a on to many
relationship.
I
have 2 tables
Table 1: People
Table 2: Communication
People has fields [PeopleID], [First Name], [Last Name]
Communication has field [CommID], [Subject], [Com Body]
Now, for each person in people table, I can have manu subject and
body,
I
keep getting error becuase it only allows me to have one subject in
body.
When I create the relationship, since I have both peopleid and
commid
integer
and autonumber, so I drag peopleid to commid, but in the table view,
hen I
try to input text to the table, they look the way they should be,
however
I
can not have multiple subject and multiple body for one person.
So how can I fix that? I couldn't find a way to create a one to
many
relationship for that. sow how can I create a 1 to infinity for
that?
