Help with Many to Many relationship

[Melissa]

I need to create a relationship between the two tabels below Tables 2 & 3 and
am confused on how to do this correctly. The first table is the one side of
my relationships. What I need to insure is that if you delete a record from
table 2 it will not allow you if there is a record in table 3. Per my
testing, I can delete a record from tbl 2 if there is a record in tbl 1. My 3
tbls are:

Table 1
EmpID (PrimaryKey)
EmployeeName
Supervisor
Group
Password

Table 2
EmpID (PrimaryKey)
DateCompleted (PrimaryKey)
IncomingPhoneCalls
OutgoingPhoneCalls
CorrectedAssessmentLtrs
ManualLtrsComposed



Table 3
DateCompleted (primary)
AuditType(Primary)
EmpID(Primary)
BeginningInventory
DayEndingInventory
WorkedNotCompleted

I have tried to go by what the help document examples and my relationships
are coming back indeterminate?
Any help would be greatly appreciated.

 

[tina]

do you actually have a many-to-many relationship between two of those three
tables? what are the "real life" relationships between the entities?

here's a standard example of a many-to-many relationship: Orders and
Products. one order may include many products AND one product many be
included in many orders. since you can't create a *direct* many-to-many
relationship in Access, you have to create an *indirect* relationship be
building a linking table, which will be on the -many side of a one-to-many
relationship with each of the other two tables, as

Orders one-to-many OrderDetails
Products one-to-many OrderDetails

where the OrderDetails table contains one record for *each* product included
in *each* order.

hth

 

[Pat Hartman\(MVP\)]

If the relationship is listed as indeterminate, the problem is with the
primary keys of the tables. Make sure that all the necessary fields are
included in the primary key. You should also reorder the fields in the
third table so that the first two are the same as the pk of table 2.
Once Jet recognizes the relationship you will be able to select the enforce
RI option. Do not select cascade delete unless you want the deletion of a
record in table 2 to cause related records in table 3 to also be deleted.
With cascade delete unselected, you will not be able to delete a record from
table 2 if it has a related record in table 3.

 

[Melissa]

Thank you for the great information. By looking at my 3 tbls, do you agree
that I need a 4th Linking table to prevent the deletion of records from table
2 & table 3?
And if so, do you have a suggestion as to what fields to use?

 

[Pat Hartman\(MVP\)]

No, that's not what I said. I said to fix the primary keys and select the
enforce RI option but NOT the cascade delete option.

 

[Melissa]

I reorder the fields in the third table. My relationships are one to many tbl
1 to tbl 2 (linked by EmpID) then tbl 2 I am trying to link to table 3 by
EmpID for a one to many and it is still saying indeterminate. I reordered the
fields and made sure they were same data type etc.
My 2nd table will have only one entry per day by EmpID but my 3rd tbl can
have many entries per day by EmpID.

Any suggestions? Thanks so much for taking the time to help.