If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Database Structure and problem with query
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 |
#2
|
|||
|
|||
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. -- Good luck Jeff Boyce Access MVP |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
RANKING | gambler | Running & Setting Up Queries | 22 | November 4th, 2004 11:53 PM |
Problem with Access database conversion | Mark | General Discussion | 2 | September 26th, 2004 04:46 AM |
Template Wizard Problem - Database Structure | setoFairfax | General Discussion | 1 | September 17th, 2004 09:26 AM |
Query problem - many to many relationship | Deb Smith | Using Forms | 0 | July 13th, 2004 04:06 AM |