Harold, You need a "relater" table when the relationship between the two substantive tables is many-to-many. There is a concept/procedure called normalization which you need to learn a bit of to do database design in Access. Your above suggested design would not be in Third Normal Form (3NF). In my opinion you need a Mill table with columns like MillKey, Mill Name, Mill Address, Phone number, Primary Contact Name, etc., etc. Then Product table with columns like ProductKey, Product Name, Product Description, Product Price, etc., etc. Then the MillProduct relater table with columns like MillProductKey, MillKey, ProductKey. The Mill Product relater table design is based upon the assumption that a product can be produced by more than on mill. If not then the MillProduct relater table would be dropped from the design and the MillKey would be added to the Product table Dick
--- In [email protected], "Harold Williams" <[EMAIL PROTECTED]> wrote: > > --- In [email protected], "Richard Root" <d1ckroot@> wrote: > > > > Harold, > > See my reply to Volunteer. You don't happen to be on Oregon do you? > > Part of the answer is to export the data from Excel and import it into > > Access. You have to choose the delimiters when exporting. Tabs > > usually work fine. > > Dick > > > > --- In [email protected], "Harold Williams" <hh6199@> wrote: > > > > > > I have been using excel to do a weekly database for a logging company. > > > Each week I deal with 2 or 3 crews that harvest timber on 2 to 4 > > > different tracts of timber. I have 9 columns that I enter data into > > > either manually or by macro or formulas. I am dealing with loads of > > > timber that are harvested and hauled to a mill for processing. In my > > > database I enter the Date, Destination or Mill, the Mill ticket > > > number, the tract of timber that the timber was harvested from, The > > > product or type of timber hauled, the volume in tons for each load, > > > the price that the mill pays for that product per ton, the total paid > > > for the load of timber, and the name of the truck driver. > > > > > > I have recently bought access 2000 and would like to start to convert > > > to access so that I could retrieve data more easily. I have used > > > access a little in the past. So I am familiar with the program. My > > > problem is the best way to set up the tables for the long haul. > > > > > > Each mill doesn't process all the products that I deal with. Different > > > mills process different types of timber. They have a price that they > > > will pay for those types. This price will go up and down as the market > > > fluctuates. > > > > > > Finally, my question. Do I need to set up a table for all the products > > > that I deal with, another that lists the mills, and another table for > > > the prices or could they be combined into one or two tables? I would > > > like to be able to enter the mill name, the product name, and the > > > volume and have the price paid entered automatically based on those > > > entries. I will also need to multiply the volume times the price but > > > right now I want to try and set up the tables the best way and not > > > have to re-do them later > > > > > > I know this is long but I would appreciate anyone pointing me in the > > > right direction. > > > > > > Thanks > > > Harold > > > > > > Richard thanks for you help. I just bought a copy of Access 2000 for > dummies. I will be doing as much research as I can to try and set this > thing up as best as can be. > In short this is what I have. 20 mills and about 20 products. A mill > will process from 1 to 5 products. Each product has a different price. > The prices will change as the market fluctuates. I have a table for > 'Mills' that has MillName, MillLocation, ProductName, ProductPrice, > and ProductUnits. My 'Products' table has ProductName, and Product > description. The ProductName and MillName are the primary keys and > don't allow duplicates. > My questions.... Do I need a third table with relationship to these > two tables to tie everything together? > If a mill processes 5 products then do I need 5 different columns for > those products and another 5 columns for their prices? > Hope this is not too confusing. > Thanks, > Harold >
