Database Structure and problem with query

[Edgar Thoemmes]

Could someone check my table structure below and let me know if it all looks
ok, I seem to be having trouble with writing some queries and I think it
might be to do with my table layout.

tbl_Suppliers – SupplierID(PK), SupplierName, Address, City, Notes,
StatusID(FK to Tbl_Status)

tbl_Status – StatusID(PK), Status

tbl_Commodity – CommodityID(PK), CommodityName, CommodityDescription,

tbl_Suppliers/Commodity – CommodityID(PK), SupplierID(PK)

tbl_Contracts – ContractID(PK), SupplierID(FK to tbl_Suppliers),
ContractDescription, StartDate, EndDate, OwnerID(FK to tbl_Owners),
EntityID(FK to tbl_Entity)

tbl_Owners – OwnerID(PK), OwnerName, JobTitle

tbl_Entity – EntityID(PK), EntityName

A couple of notes, Each Supplier could have more than one contract but each
contract can only be for one supplier.

Each supplier can be assigned to a number of commodities, different
commodities can be assigned to more than one supplier.

Each contract can only be owned by one owner but each owner can own more
than one contract.

The end result of this is that I want 1 big query to display all of this
data as I need it all to link into one big form which I will be splitting up
by Tab Controls.

I am relatively new to access so if anyone can think of a better way of
doing it I would appreciate the input.

Many thanks

 

[Jeff Boyce]

Edgar

A couple observations...

First, although I see the table that resolves Suppliers and Commodities, I
don't see where/how Commodities get "used" by any other table (besides
listing in Commodity table). Does this mean that the Supplier on a Contract
supplies ALL Commodities they "own"?

Second, the most common approach to displaying one-to-many relationships is
NOT to create a massive "flat" representation through a query. Instead,
consider using subforms when you need to show, say, all the Commodities for
a given Supplier. You can embed subforms in the pages of a tab control.