2 one to many relationships in the same table

[lefty2cox]

Is it ok to have one table on the "one" side of a relation ship pointing to two fields in the "many" side of the relationship? See attached photo. I have a client table relating to an orders table. The orders table has both "client" and "lender" fields. I'd rather use one "clients" table to relate to both fields in the orders table rather than having a "clients" table and a "lender" table since both would have the same records. My apologies if this is confusing. I've attached a photo to illustrate. Access appears to have renamed one to "client_1".

[ssanfu]

The photo is correct. You have to have "Client" and "Client_1".

Other recommendations:
Don't use spaces in object names
Don't use special characters in object names ("House#", "City/Town", "Co-Borrower"). Use only letters, numbers and the underscore.

Take the time to name fields. Having all of the tables with a primary key of "ID" will get you and Access confused. Better names would be "OrderID", "Order_Detail_ID", "ClientID"......

Also see http://access.mvps.org/access/tencommandments.htm

[lefty2cox]

Got it. I'm making the changes now. Thanks for you help.

[RonL]

Guess you've marked this solved, but I'm still wondering if your original instinct wasn't good - that it's not optimal to repeat the same data in two different tables. (Maybe this is why Steve references the tencommandments).

Not sure if you're developing this project or have inherited it, but how about this for an alternative: have only one "People" table, but define a new one, say "Roles". You'd make a one to many relation between Orders and Roles, and a many to many between Roles and People (which would require a junction table).

I *think* that would provide an equivalent structure without redundancy. As a novice, I'd be interested in the pros thoughts.

-Ron

[lefty2cox]

Ron,

I may be a bit confused but the client table is only one table. I had added to the relationship window twice in order to create the relationship between it and two different fields in orders. The relationship window, for reasons I don't understand as a newbie, added this table with "_1" at the end of the name. The more I looked at it, I realized that I really don't need the relationship between client table and lender field in orders because I'm really only interested in the client field in orders for billing purposes. 9 out of 10 times, both fields will be the same. The lender field will be referenced once in a while but I will never need to query on it. Am I understanding you correctly?

[RonL]

............... I had added to the relationship window twice in order to create the relationship between it and two different fields in orders. ..........

I didn't understand that. Sorry, my confusion. Glad it's worked out. -Ron