Please help with table relationships

[nik.be]

I have a problem with a simple DB that I am putting together. I have attached a schema that illustrates the relationships existing in the DB at the moment. What I am trying to fix is the ability in table ESTIMATES to select a SUPPLIER from a drop down menu and, by clicking a button, create a PURCHASE record which is linked to the previously selected SUPPLIER.

Please, please help.

TIA

[Fenton]

If you connected Suppliers to Estimates, via the SupplierID, then you'd see THE Supplier for the Estimate. I say "THE" because there appears to be 1 and only 1 Supplier for an Estimate (unless you add a "join table" between Estimates and Suppliers; not too hard to do). If there is only 1 Supplier, why would you need to "select a supplier from a drop-down menu" in Estimate?

[nik.be]

Fenton, thank you for replying. The Suppliers table is not linked to estimate simply because I gave up trying various connections and preferred living it unlinked for the time being. I have infact more than 1 supplier, hence the reason for a drop-down menu. The thing that I cannot figure out is how to link a supplier to an estimate to create a purchase order which is linked to estimates but also the chosen supplier.

[RobW]

I think that there is a fundamental mis-understanding here. Every join in every table is linked by "contact ID". You need to consider how you would do this in the real world. You would raise an estimate. Then when you com to raise one or more invoices against that estimate you would write the estimate number on the invoice. So your link should be between say "estimate ID" on the estimate and a fild on the invoice called "estimate ID".