contact table issues

[redknite]

I was trying to create a transaction database for real estate. I'm confused on the best way to set up my contact tables. I want to have a table for the actual transaction (including buyer, seller, buyers agent, sellers agent, property address, escrow, and lender, sale date, sale price, etc). I want to have the contact info in a separate table. My confusion is how to set that up. i.e., the buyer could be the buying agent as well, so there would be two direct links. Another way to say it i guess is that all contacts (buyer, seller, lender, escrow, buyers agent, and sellers agent) will all be from the same contact list (and perhaps a contacttype table separately) and so there will be several relationships between the contact and transaction tables. What is the best way to do it?



Thanks again!!!

[RuralGuy]

Are you familiar with putting a PrimaryKey value of a table in another table as a ForeignKey to establish the relationship?

[redknite]

Thanks for the question! I am familar with this process: here is what the setup would be, i'm asking if it is normal or if there is a better method:

tblSale:
SaleID (PK)
address
buyer (contactID 1)
seller (contactID 2)
buyeragent (agent 1)
selleragent (agent 2)
lender (agent 3)
escrow (agent 4)
date
saleprice
etc...

My contact table would be
tblcontact:
contactID (PK)
first
last
address
phone
etc

tblagent:
AgentID (PK)
Company
AgentypeID (FK)
first
last
address
phone
etc

the problem would be how to use this info repeatedly in the sale table...ie a contact from tblContact could be the buyer, seller, agent (asnoted by id example in pararenthese) etc in the tblsale. Does that make sense?

[RuralGuy]

You are probably going to want a Property table with a PropertyID field. I'm a big fan of using AutoNumbers as the PrimaryKey of a table and therefore a LongInteger as a ForeignKey field type. Access like it better too.
The Sale table should not have a problem using the same value for all of the FK entries.

[redknite]

thanks! i'll go that direction then

[RuralGuy]

Post back if you still need assistance and have fun.