Easy structure ques

[Edgar]

I am designing a DB for storing and managing contracts. I
am reasonably new to access so please be gentle….

So far I have the following Tables Suppliers(SuppID, Name,
Address, Tel, Email) Contract(ContractID, Supp ID, Start
Date, End Date, Commitment) and Commodities(CommID,
Number, Name)

I have linked the Supplier and Contract tables together
and this works fine, I am not sure as the best was to go
about linking the other tables though. Basically the
supplier and commodity table need to be linked but each
supplier could have more than one commodity so I wasn’t
sure of the best way to go about this. I was thinking of
maybe using a linked table but I thought I would ask an
expert first….!]

TIA

Edgar

 

[Allen Browne]

There is probably a many-to-many relation between Suppliers and Commodities,
i.e.:
- 1 supplier provides many commodities, and also
- 1 commodity can come from multiple suppliers.

If so, you do need the junction table between Suppliers and Commodities.
Fields:
SuppID Number (Long) foreign key to Suppliers.SuppID
CommID Number (Long) foreign key to Commodities.CommID
UnitPrice Currency price of this commodity from this
supplier.
You could use the combination of SuppID + CommID as primary key if you wish.

With this kind structure, you could find the best price for a commodity like
this:
DMin("UnitPrice", "SuppliersCommodities", "CommID = " & Nz([CommID], 0))

And if you need to know which supplier gives the best price for a commodity:
ELookup("SuppID", "SuppliersCommodities", "CommID = " & Nz([CommID], 0),
"UnitPrice")
where ELookup() is a function you can copy from:
http://members.iinet.net.au/~allenbrowne/ser-42.html