Handling "Multiple Foreign Keys" in the same table 1

[keithhbs]

Although I am fairly new to Access and VBA, I understand relationships fairly well. But I have a situation that I am unsure how best to approach.

In the Order File I have already defined a Foreign Key for the Sales Rep that ties back to the Primary Key in the Sales Rep File. I later realized that it is possible to have 2 Sales Reps involved with a single order - the Ordertaker and the Installer. For example, the Ordertaker may sign the order in his area, but the equipment then gets installed into a different sales rep's territory (the commission is split among these 2).

In most cases the Ordertaker and Installer are the same, but the system must be able to accomodate this occurrence.

I have had several ideas including creating a duplicate Sales Rep file just for the Install rep, but I suspect there is a better way. Any thoughts?

 

[KenReay]

Hi

Why create a seperate or duplicate sales rep file?

In your Order Table have two Columns:

OrderTaker
Installer

In your relationships table add teh Sales Rep Table to the relationships window twice (Access will name the second one SalesRep_1), make you relationship join from

OrderTable.OrderTaker to SaleRep.SalesRepId

and

OrderTable.Installer to SaleRep_1.SalesRepId

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[keithhbs]

Ken - I did not realize that this was an option. It appears to me that this new table (SalesRep_1) is a clone of the the SalesRep table. I am assuming that any changes to "SalesRep" also appear in "SalesRep_1"? This is pretty slick.

After sending the initial question I started thinking about creating a new table called "tblSalesRepOrders" that would contain the "many side" of the Sales Reps associated with each order in "tblOrders". If the number of sales reps associated with each order could exceed 2 then this might be better, but since I am looking at a situation of 2 AT MOST, your suggestion will work quite nicely.

Thanks again.