Table Design and Relationships

[David Kennedy]

Hi I have a query please can you help. I have two tables,
a Customer Table and a Sales Rep Table. The Customer
Table has a Customer ID(key) and Customer Name. The Rep
Table has a Rep ID(key) and Rep Name.
A Rep can be assigned to many Customers and Customers can
have many Reps. How can I set up the Tables so that if I
delete a rep it doesn`t delete a customer and if I delete
a customer it doesn`t delete the rep?
Do I need a bridging table between these two tables?

 

[John Vinson]

Hi I have a query please can you help. I have two tables,
a Customer Table and a Sales Rep Table. The Customer
Table has a Customer ID(key) and Customer Name. The Rep
Table has a Rep ID(key) and Rep Name.
A Rep can be assigned to many Customers and Customers can
have many Reps. How can I set up the Tables so that if I
delete a rep it doesn`t delete a customer and if I delete
a customer it doesn`t delete the rep?
Do I need a bridging table between these two tables?

Yes; a table (RepAssigned perhaps) with fields for RepID, linked to
the Salse Rep Table and CustomerID linked to the Customer table will
do the trick here. Any time you have a many to many relationship you
need such a third table.

You will want to define the two relationships to RepAssigned with
relational integrity enforced. If you just do that, you won't be able
to delete a rep or a customer without first deleting the assignment
record(s) for that person. If you prefer, you can set the
relationships' Cascade Deletes property to True; now deleting a
customer will automatically (and silently, and irreversibly) delete
all that customer's RepAssigned records. The Rep's records will not be
affected.

 

[PC Datasheet]

TblCustomer
CustomerID
CustomerName

TblRep
RepID
RepFirstname
RepLastName

TblCustomerRep
CustomerRepID
CustomerID
RepID

Create your relationships and enforce referantial integrity, cascade delete and
cascade update. When you delete a customer, you won't delete a rep and when you
delete a rep, you won't delete a customer.

 

[David Kennedy]

I use Access everyday in work.

David Kennedy
P.S. I finished school 10yrs ago

-----Original Message-----
Not that it has anything to do with anything, but is

this for a school project or an actual business?

 

[David Kennedy]

Thank you very much for your reply

-----Original Message-----
Yes you need a third table to relate customers and reps,

it will contain two columns cust id and rep id.

To avoid the automatic deletion just dont check 'enable

cascading deletes' in the table relationships (you are
using them right?)

 

[Guest]

Thank you very much for the detailed reply its excellent

 

[Guest]

Thank you very much for your reply

-----Original Message-----
TblCustomer
CustomerID
CustomerName

TblRep
RepID
RepFirstname
RepLastName

TblCustomerRep
CustomerRepID
CustomerID
RepID

Create your relationships and enforce referantial integrity, cascade delete and
cascade update. When you delete a customer, you won't delete a rep and when you
delete a rep, you won't delete a customer.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com





.