Table Structures

[Dwight]

I need help with my table structures. Attached is a Access 97 sample of my existing tables.

Questions

1) I need a new table that shows which BankRepID’s are assigned to each ClientID. Currently the relationship between the Banks and their BankReps is normalized.

But every time I create a table to assign BankReps to Clients I have noticed it becomes possible for the user to assign BankReps to Banks where they do not work.

How do I prevent this through normalization?

2) Because one Client can have many banks and one bank can have many clients I created a many to many relationship. Is this correct?

 

[cuttsy]

I don't fully understand your problem. What is a bank rep. A report? someone who works at a bank? I'm unsure.

I'm no billy whiz but your join between bank and client looks okay to me.

 

[Dwight]

Sorry for the confusion. BankRep is short for Bank Representative, that is someone who works for the Bank.

Each client will have a designated BankRep and of course each BankRep must work for a particular bank. My problem is structuring a table so that a BankRep can be assigned to a Client AND this does not violate which bank they belong to.