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
0
Utf
1/30/2008 11:23:02 PM
access 16762 articles. 3 followers. Follow

2 Replies
763 Views

Similar Articles

[PageSpeed] 51

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" <Rhonda@discussions.microsoft.com> wrote in message 
news:A4C9916C-B67B-406A-A16A-CAC2BCE717C5@microsoft.com...
> 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 


0
DL
1/31/2008 12:01:50 AM
On Wed, 30 Jan 2008 15:23:02 -0800, Rhonda <Rhonda@discussions.microsoft.com>
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]
0
John
1/31/2008 12:31:20 AM
Reply:

Similar Artilces:

microsoft office professional 2007 trial product key
My computor is not accepting the product key,what must i do? From where did you get the product key from -- Peter Please Reply to Newsgroup for the benefit of others Requests for assistance by email can not and will not be acknowledged. "Kaiser" <Kaiser@discussions.microsoft.com> wrote in message news:7C6101C5-A75D-4A84-B48C-992FDA921A70@microsoft.com... > My computor is not accepting the product key,what must i do? He may have stolen it from somewhere. What difference does this make? Has your wife also joined your sister and your mum in the escort ...

Primary partitions
Say I have four hard drives(physical drives) in one computer. Is the four primary partition per each physical hard drive? In theory than I could have 16 primary partitions in a computer that has four physical drives. Is this statement correct? With Microsoft's partitioning that's correct. There are other utilities that will let you do more than four primary partitions. If your installing Win 7 let it do the partitioning. Win 7 has a 100MB partition for system files and then another primary partition for the rest of Win 7. Win 7, as does Vista, you can shrink...

two units on primary Y axis
Hay!! I have spent quite some time looking for a solution and I doubt there is one. Is it possible to have two units on primary Y axis such us for example EUR /USD.. For example primary Y would have values such us : 1000 EUR / 1300 USD, 2000 EUR/2600 USD etc.. If there was a way that custom format would include possibility of calculation for unit.. tnx Rado Hi, Perhaps you can create a fake value axis using a dummy data series plotted as xy-scatter. Display data labels which are linked to cells that have the required text. Data for series would be like, X Y Label 0 ...

loading a count of another entity into a field on the primary entity
Hi I have a custom entity associated with Opportunities called Required Conditions. From the Opportunity enity I need to be able to check the count of the Required Conditions in order to disallow/allow certain actions. The simplest way of doing this (from what I can see) would be to populate a field on Opportunity with the count of the Required Conditions when the Opporutnity is loaded, however I have no idea how to do this. Can anyone out there assist? TIA Saira You may want to consider a callout (3.0)/plug-in (4.0) instead. Whenever one of your custom entities is created, updated...

Can't use key and keyref with an attribute in a group
Hi, Why can't I create a key and keyref with an attribute declared in an attribute group? Code below describes what I'm trying to accomplish. <!-- An attribute group --> <xs:attributeGroup name="AttGMain"> <xs:attribute name="Name" type="xs:string" /> <xs:attribute name="Type" type="xs:string" /> </xs:attributeGroup> <!-- CT using group above --> <xs:complexType name="ctView"> <xs:all> <xs:element name="Control" type="ctControl" /> <xs:eleme...

License Key Problems
I needed to unplug everything that was connected to the computer including the USB license key. After moving the computer to a different location and pluging back the USB license key, the POS tells me that the registration was not found. That was ok for a few days but now the POS has turned red and says its for Evaluation only. I'm afraid that the pos will stop working and prevent me from cashing people out on my lone register. I've tried pluging the key back in, re-starting the computer and nothing seems to work. Does anyone have any suggestions? try putting it to ano...

Contacts should have both a primary contact and primary account
My customer would like to have both a primary contact and primary account like the account record. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=dcb8b04e-171f-4d5a-8b96-f...

Shortcut key #2
My forward slash key I believe is acting like a shortcut key. When in a table and I push it, it highlights the file menu. I cannot put a forward slash into my tables. It does not show any shortcut keys through. I would appreciate any help as I don't want to mess things up. Thanks for any ideas. To remove it, tools>options>transition and clean out the menu key box -- Regards, Peo Sjoblom "J Evans" <evans@yvn.com> wrote in message news:05f701c3b3ba$8296dd80$a401280a@phx.gbl... > My forward slash key I believe is acting like a shortcut > key. When ...

License key transfer
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I have a Microsoft Office for Mac 2008 Home and Student edition. It is to my understanding that I have 3 license keys. I've already used up all 3 license keys on 3 different computers. However, this is my situation. I bought a new Mac and want to install Office on it. Am I able to uninstall Office on my old Mac, then install Office on my new Mac with the same license key I use on my old Mac? Thanks! On 9/26/09 5:04 PM, in article 59b7d5cc.-1@webcrossing.caR9absDaxw, "okatidubi@officeformac.com" <okati...

Primary addresses and rejections
Running an inherited Exchange 2000 server using DNS, no smarthost. My problem is resolved...but I'm trying to understand what happened and why. Here's the events that occured...this morning, I tried to set an email deletion policy in the Recipient Policies container in Exchange. For some reason (still don't know what I did wrong), doing this changed everyone's primary email address to a different one. (for some background...originally this server was setup for the format such as jsmith@domain.com ....recently, they changed the email addresses to the format john.smith@doma...

Use registry key PrinterPrivateData to detect a physical/virtual printer?
Hola, I am programmatically trying to identify a physical printer like "HP LaserJet 4050 Series PCL" and "Brother HL-P2000" from virtual printers like "Microsoft Office Document Image Writer" and "Microsoft Office Live Meeting Document Writer" and "Adobe PDF" Is it safe to assume that each physical printer will have a value(s) under the key HKLM\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Print\Printers\<printer-name>\PrinterPrivateData? What is the significance of key HKLM\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Print\Printers\&...

Primary Keys
I have a table of data where the primary key is a short code. A number of these codes are grouped together to produce a reporting group. I have now encountered a problem where i have had to set the report name and the short code as primary keys, as i am overlapping on the short codes. The problem is that i can no longer amend data in my report, while in a datasheet view and somebody else is in the db. The table is linked to another table using the 1st primary key as a link Do you know how i can resolve this issue? ...

Relationship mapping help (CRM 4.0)
Hi, I've created a new field in Lead and Account sections. The idea is that the user creates a lead and fills in this text field (nvarchar, length 25). I've edited the relationship mapping from Lead to Account so the related field in Account is filled in when the Lead is converted to an Account. I also want the field editable in the Account form so that it can be altered later on. I've created 4 new fields in both Lead and Account (all are simple text fields), and 3 of them work but the 4th mapping doesn't. I get the error message asking me to check a list of things but...

Do I have to set the primary key to include primary key fields?
I have read that "in a junction table, you need to set the primary key to include the primary key fields from the other two tables. " If I do this, I keep getting the error message "the change you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. The only way I'm entering data is through a form based on a query which automatically writes to all 3 tables (the junction and 2 parents) at once. If I remove the primary keys in the junction tables, I no longer get the error message....

Arrow Keys 03-25-10
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel All of a sudden my arrow keys move in the opposite direction to what is intended (down go up and right go left etc). This only happens in Word. Can anyone help me correct this? PJH: Try the same fix I just sent to Simon. Let us know how it turns out... Cheers On 25/03/10 7:23 PM, in article 59bb5eb7.-1@webcrossing.JaKIaxP2ac0, "PJH@officeformac.com" <PJH@officeformac.com> wrote: > Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel > All of a s...

Strange Primary Key values
Hi, I have created a form based on a table using the wizard. This table has a primary key field plus a few more. When I add new rows to the table using the form, I gets strange primary key values such as -298239823. Any ideas why this might be? Thanks, Aine Sounds as though your primary key is an AutoNumber set to Random, rather than Sequential. That shouldn't matter: the value of an AutoNumber key has no meaning. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) <aine_canby@yahoo.com> wrote in message news:1186493404.056219.122550@d55g2000...

Importing e-mails from secondary HDD to primary
I just got a new hard drive and I want to keep the e-mails I had on my old drive. How would I go about doing this? This is Outlook 2003. Thanks! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ...

excel f function keys
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) can i please have some help?????? <br> being new to the world of Mac i am doing a excel spreadsheet and i need to be able to use the F key functions. <br> what i am after is in excel 2007 when doing a formula you can turn part of it into a $ input by pressing F4, can this be done in Excel 2008 or how can i do it otherwise please advise <br><br>thanks Hayly What you're looking to apply is called Absolute Reference. The Mac keystroke shortcut is Command+T... Or you can always type the $ yourself, there&#...

Primary supplier
I have a shop which is supplied by my warehouse. Therefore the warehouse is always set as the primary supplier to the shop. when i run a sales report for the shop by supplier i obviously get 100% of sales from the warehouse. However i would like to run a sales report by the real ie secondary supplier. Any help here would be gratefully accepted. thanks If you're not using BIN LOCATION you could use this field to track the real supplier (if you're using the detailed sales report its the only available field for this purpose); is there any reason why you aren't doing transfers...

Primary Catagory
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel How do you set a catagory ss primary so that a new event automatically is assigned to that catagory? ATM, mine comes up as &quot;None&quot; <br> When in the &quot;assign catagories&quot; section there is a button on bottom left which is &quot;Set Primary&quot; which I imagine is what I want but it is grey and cannot be used. <br><br>Also, does Entourage have a manual? So that I can find these things out for myself rather than have to search this forum or watch their s...

Use insert key to control overtype
I`m trying to deploy office 2007 in silent mode. How can i turn on "Use insert key to control overtype" checkbox in registry or in office customization tool? i can`t find it Try the Office Button (left corner) then Word Options | Advanced | Editing and check "Use insert key..." "Ivan" wrote: > I`m trying to deploy office 2007 in silent mode. How can i turn on "Use > insert key to control overtype" checkbox in registry or in office > customization tool? i can`t find it ...

Assign data series to primary axis
Line chart: Data Series: Altitude Oil Temp Oil Temp uses the primary vertical axis and Altitude the secondary. I want to add OAT (outside air temp), plotted against the primary axis. But when I add the new data series it uses the secondary axis and plots itself as a nearly straight horizontal line along the bottom of the chart. In this position I cannot seem to 'select' the series with the cursor. How can I add the OAT data series plotted against the primary axis. I have just upgraded to Excel 2007. -- Art Right click the added series, choose Format. In a visible position, I ...

return key in text edit
I would like to enter a text in my text box on the form. When I press "enter" key the form goes to next record. I want to enter a text to go to next line, but I am unble to do so. I need type in the note pad which has multi lines and copy paster to the form. Are there any property to turn retrun key to go to next line in the text edit instead go to next record. Your information is great appreicated, On Dec 13, 6:06 am, Souris <Sou...@discussions.microsoft.com> wrote: > I would like to enter a text in my text box on the form. > > When I press "enter" ke...

What is a primary key?
If someone can please explain what a primary key in access is, I would appreciate it. Also in a database that has multiple tables, are there multiple primary keys? Thanks for helping. Obviously I'm a newbie. A primary key (PK) is a unique index on a table. Every table should have one. If you have multiple tables, each one should have its own PK. A database sorts its records in the way you specify in your query. So if a table has 2 identical rows, and you ask to update one of them, which one gets changed? The answer is not properly defined. Therefore every table should have some wa...

Primary Key names. (with random ?? numbers in them).
Dear reader, For primairy key names I was (and still am) used to names like : 1. PK_<table_name> Now I have a database which has primairy keys with double underscores and names like : 2. PK__<table_name>__<number> There are two underscores and a large number. When scripting (database or table): The name from 1 appears in the script. The name from 2 does not appear in the script and a similar name with a different number is generated when the script is executed. My problem, if I do not have control over the names of objects, it becomes difficult to ma...