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
|
|||
|
|||
Primary Keys and Relationships
Here's some information on what I'm trying to do. I own a greenhouse and I
want to keep data on inventory, costs and sales. Be able to keep databases on the cost of supplies and be able to pull them together to determine the costs of pots, flats, and baskets. I have set up 4 databases Expenses (contains items not related to sell cost of plants, ie utilities, repairs,etc. Seeds and Plugs (contains product id, product name, product description, cateory ID, units in stock and unit cost Supplies (contains category id, product name, product description, supplier id, units in stock, unit price, product id...there is no information in this field at present) Tags (contains tagsid, product name, units in stock, unit price, product id (no information in this field at present) I let Access choose the primary key and it set up an automatic number is not the same in each table for the product name. How do I set up primary and relationships that will link all the tables together in order to price and inventory sales? Thanks Rhonda -- Rhonda |
#2
|
|||
|
|||
Primary Keys and Relationships
The Tables need to have a common relationship
ie, Suppliers Tbl Product ID etc Sales Tbl Product ID etc The above two are linked on Product ID Some examples http://www.databaseanswers.org/data_models/index.htm "Rhonda" wrote in message ... Here's some information on what I'm trying to do. I own a greenhouse and I want to keep data on inventory, costs and sales. Be able to keep databases on the cost of supplies and be able to pull them together to determine the costs of pots, flats, and baskets. I have set up 4 databases Expenses (contains items not related to sell cost of plants, ie utilities, repairs,etc. Seeds and Plugs (contains product id, product name, product description, cateory ID, units in stock and unit cost Supplies (contains category id, product name, product description, supplier id, units in stock, unit price, product id...there is no information in this field at present) Tags (contains tagsid, product name, units in stock, unit price, product id (no information in this field at present) I let Access choose the primary key and it set up an automatic number is not the same in each table for the product name. How do I set up primary and relationships that will link all the tables together in order to price and inventory sales? Thanks Rhonda -- Rhonda |
#3
|
|||
|
|||
Primary Keys and Relationships
On Wed, 30 Jan 2008 15:23:02 -0800, Rhonda
wrote: Here's some information on what I'm trying to do. I own a greenhouse and I want to keep data on inventory, costs and sales. Be able to keep databases on the cost of supplies and be able to pull them together to determine the costs of pots, flats, and baskets. I have set up 4 databases Jargon alert: in Access terms, a "Database" is a .mdb (.accdb, .mde, or other) *container* for multiple tables, forms, reports and other objects. You would seem to be talking about creating four *tables* in a database, not four databases - if not, you should be. Expenses (contains items not related to sell cost of plants, ie utilities, repairs,etc. What's the Primary Key of this table? What identifies an individual expense? I presume this table has a date field indicating when the expense was incurred (and perhaps another for when it was paid)? Seeds and Plugs (contains product id, product name, product description, cateory ID, units in stock and unit cost It's questionable whether you should store the units in stock at all. It's often better to store just an initial quantity (as of the date of a physical inventory, say) and calculate the current amount in stock by using a totals query to sum all the in and out transactions since that date. Supplies (contains category id, product name, product description, supplier id, units in stock, unit price, product id...there is no information in this field at present) Same issue. Tags (contains tagsid, product name, units in stock, unit price, product id (no information in this field at present) How are Seeds and Plugs, Supplies, and Tags related? If they all deal with a product, then there should be one master table of Products with a field that indicates whether the product is a seed, a plug, a tag, etc. I let Access choose the primary key and it set up an automatic number is not the same in each table for the product name. Correct. Autonumbers are COMPLETELY ARBITRARY, and an autonumber in one table will have no connection whatsoever with an autonumber in any other table. How do I set up primary and relationships that will link all the tables together in order to price and inventory sales? You'll know that better than we will - what is the real-life, in the greenhouse, relationship between the Entities (real persons, things or events) represented by these tables? Each kind of Entity should have its own table; you need to define what the entities are. Is a Seed a different kind of entity from a Tag, or are they just two particular examples of a Product entity? That's a business rule decision that *you* must make. In any case, you should try very hard indeed to be sure that you don't have data stored redundantly. If a given product occurs in more than one table (say, a Products table and a Sales table), then the product name or description should be stored once and only once; the other tables should have a ProductID as a foreign key to the Products table primary key, and should not contain ANY other information from that table. John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|