Hello,

I'm creating a database for a home builder to track a lot of info, but mainly: house info, prospect info, actual customer info, and contract info.

My main tables are:
tblCustomers
CustID PK
(a bunch of customer fields)

tblHouses
HouseID PK
(a bunch of house fields)

tblContracts
ContractID PK
CustID FK
HouseID FK
(a bunch of contract fields)

The releationships are as follows:
One house can have many contracts
One customer can have many contracts

I thought I could use one table, tblCustomers, to track the prospect info and actual customer info, as so much of it will be identical (technically someone should be entered as a prospect before they can be a customer, and all info will be the same for both except for extra information fields for prospects, but I always want to be able to refer back to those fields) and I would know a prospect became an actual customer because at least one contract in the tblContracts would have their CustID associated with it.

Is it acceptable to have two foreign keys in tblContracts? And am I missing something that should exist between House and Customer? House and Customer are only releated when there is a Contract, so it is implied (I think) that a Customer can be associated with many different houses through many different contracts. Is that the right way to look at it?

I've come up against a wall regarding linking two forms so that the second form displays the data associated with what was showing on the first form. If I'm good here with the tables I suppose I'll post my detailed question in the forms area.

Thank you so much!:o
~Merissa

You seem to be on the right lines. You have a many to many relationship between customers and houses. The only way to model a m to m is with a junction table and that's what your contracts table is doing.

Not sure what the issue with the forms is, though I think you'll find a properly constructed query should be the basis for your forms, not a table.

As a general design point it is always better to base reports and forms on a query rather than directly on a table. This will make it easier to make changes to the data being used.

Neil and Rabbie - thank you both so much! You kicked me into gear with the query thing and now, after some tweaking, my forms work beautifully.

Neil and Rabbie - thank you both so much! You kicked me into gear with the query thing and now, after some tweaking, my forms work beautifully.

~Merissa

Neil and Rabbie - thank you both so much! You kicked me into gear with the query thing and now my forms work beautifully.

~Merissa

Glad to be of help