How do I relate my tables! Access Relationship Problem 2

[mspeppa19]

Ok so I have a database with four tables: [Business] [Purchase order] [Product] [Vendor]

i'm having huge problems getting my relationships to work!

Ok so here is the deal:
[Business]
busn_id*
busn_name
purch_id

[Purchase Order]
purch_id
prod_id*
ven_id*

[Vendor]
ven_id*
ven_name
ven_add

[Product]
prod_id*
prod_cost
prod_price

Ok..so a business can have one to many purchase orders. A purchase order can have one to many products, and a vendor can have one to many purchase orders. Does that make sense? Well that relationship ONLY works when I do not make prod_id in the [Product table] the primary key. But i know that every table has to have a primary key.

Also, when I go to make my form up, what should I use as the main form? should it be based on purchase order? since it is the one table that connects all of the databases? Whenever I do that I always get an error message that says that there is a null value somewhere, but all of my data is filled in!

Can anyone give me some tips on how to get this to work? It would be greaatly appreciated! Thanks

 

[KenReay]

Hi

What is the significance of teh * in your example? Prime key perhaps?

How can you relate Business Table and PO Table you do not have Business Id as Foreign key in PO Table

Why is PK of PO table not POId (PO Number?)

You need to sort all of this out before running off and creating Forms

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[johnherman]

Well, let me say first that you are correct in using PO as the basis for your form. Not only is it the table which connects to the others, but it is the business process which you are trying to resolve.

As for the relationship, you need to remove the purch_id from Business and add busn_id to the Purchase Order table.

Best Regards

Sometimes the grass is greener on the other side because there is more manure there - original.

 

[lespaul]

And why would you want vendor ID in the purchase order table?

check out JeremyNYC's website:

http://www.alphabetcitydataworks.com

in the developers section there's an article "Fundamentals of Relational Database Design". Read that, restructure your tables and try your relationships again. You have your foreign keys in the wrong places.

leslie

 

[johnherman]

Might depend on whether there are multiple vendors for multiple products. For instance, Dole is not the only company that makes canned pineapple, and Dole also makes other products.

Sometimes the grass is greener on the other side because there is more manure there - original.

 

[johnherman]

Nice link lespaul - very useful - you get a star!

Sometimes the grass is greener on the other side because there is more manure there - original.

 

[mspeppa19]

Is there anyway I can send the database to one of you for you to look over? It may be easier to see what's wrong rather then how I am explaining. Something is just not right and I just cannot pinpoint it. Thanks


Meg

 

[lespaul]

Here's some basics (most of us prefer to keep the thread public rather than going private):

tblBusiness (list everything you know about the business):
BusinessID
BusinessName
BusinessAddress
BusinessPhone

tblVendors (list everything you know about the vendors):
VendorID
VendorName
VendorAddress
VendorPhone

tblProducts (list everything you know about the product):
ProductID
ProductName
ProductSalesPrices
VendorID (if you get the same product from two vendors you'll need to set this up differently)
ProductCost

Now you want to create a purchase order:

tblPurchaseOrders
PurchaseOrderID
BusinessID (?only if each purchase is for a specific business)
VendorID (only if each purchase order is for a SPECIFIC vendor - if you want to order from multiple vendors, this will need to be modified)

tlbPOItems
PurchaseOrderID
ItemID
ProductID
QuantityOrdered

So now each business can have many purchase orders, each purchase order can have many items and each vendor can have many purchase orders.

HTH

leslie

 

[mspeppa19]

Ok, that's great, now what will be my primary key's for each table? Do I have to have primary key's for each table? And for example in the tblPurchase Orders would I have three primary keys consisting of my product, vendor and business table?

 

[johnherman]

The ID's are your keys. When there are more than one ID columns, the concatenation of them is your primary key.

Or you can autogenerate a sequence number (aka = surrogate or synthetic key) for each table and leave the "natural" keys alone.

Sometimes the grass is greener on the other side because there is more manure there - original.

 

[mspeppa19]

Great thank you for your help concerning the tables! I think i've got it. I have another question though concerning the creation of my forms...


Under the purchase table I have the primary keys [prod_id, ven_id and busn_id] and purch_id is just a key for the purchase table.

When I go to create my forms how should I link them? When I do it now I create seperate forms for each table and then I include each of them as subforms in the following way.

Business form has:
busn_id
busn_name

Purchase form has:
purch_id
vendor table. ven_id

Product form has:
purch_id
product table. prod_id

After I do that I include the product form as a subform on the purchase form...and then I take that form (the purchase form) and include it as a subform on the business form.

The thing being. *Every business has multiple purchases. Every purchase can have multiple products from one vendor.

So..a text lay-out of how my form look is as follows
Main Form: Business
Busn ID
Busn Name

Subform: Purchase
Purch ID
Purch Date
Vendor ID
Vendor Name

Subform: Product
Prod ID
Prod Name

I do not know if I am doing it correctly, but am I supposed to include they keys from the purchase table, or from the respective product and vendor tables?

 

[lespaul]

before you start worrying about forms, why don't you post your table structure for review. list tablenames,all the fields in the tables and which fields are Primary and Foreign keys.



Leslie

 

[mspeppa19]

* denotes PK

business table:
tblBusnID*
tblBusnName

purchase table:
tblPurchID
tblBusnID*
tblVenID*
tblProdID*

vendor table:
tblVenID
tblVenName

product table:
tblProdID
tblProdName


the purchase table is the table that connects everything together with one to many connections.

 

[mspeppa19]

* denotes PK

business table:
tblBusnID*
tblBusnName

purchase table:
tblPurchID
tblBusnID*
tblVenID*
tblProdID*

vendor table:
tblVenID*
tblVenName

product table:
tblProdID*
tblProdName


the purchase table is the table that connects everything together with one to many connections.

 

[lespaul]

First off, you should have your field names starting with tbl - that's traditionally used for table names.

Ok, now then - you have tblBusiness - are these the people you are selling your products to? - this table should only contain information about the business - id(PK), name, address, phone number, etc.

tblVendor - are these the people that you are purchasing your products from that you are selling to Businesses? this table should only contain information about the vendor - id(PK), name, address, phone number, etc

tblProducts - are these the items that you are buying from vendors and selling to businesses? this table should only contain information about the product - id (PK), description

Now you want to issue a purchase order. This means that you need to buy something from a vendor:

tblPurchaseOrders
PurchaseOrderID (PK)
VendorID
PODate

since there can be more than one item on a purchase order, you need a purchase order detail table:

tblPODetail
POID (FK to tblPurchaseOrders)
PODetailLine
ProductID

now each purchase order can have more than one item on it. The two fields POID and PODetailLine would make a composite PK.

Now you say that each business can have many purchase orders. What if two businesses ordered a GIZMO today. You need to issue a PO for some GIZMOS from Vendor Gizmo Toys. Do you issue a PO for EACH business? Or do you issue a single PO to the vendor for the two businesses orders?

Another thing to consider, tblVendors and tblBusiness have similar fields (name, address, phone number), perhaps you could make one table and add a field to indicate whether that company is a Business or a Vendor.

I think you should continue to work on your table structure until you get it to 3NF before moving on.

HTH

leslie

 

[mspeppa19]

This program is a vendor tracking program for businesses. So for example a business let's say : MsPeppa's Shoe Shop can have multiple purchase orders. But each purchase order contains only one vendor but multiple products purchased from that vendor. This program is just an inhouse tracking program which will allow a business to keep track of orders that they placed with vendors.

 

[lespaul]

So, if two business placed orders for the same vendor, there would be two PO's for that vendor - one for each business?

So business1 orders 10 GIZMOS from Gizmo Toys at the same time that business2 orders 10 GIZMOS, would you create two purchase orders to Gizmo Toys? If so, then you would just add businessID to the purchase Order table and include it as part of a composite PK.

Then to create your relationships, you would open the relationship window. Click on the VENDORID in tblVendor and drag it to VendorID in tblPurchaseOrders. Do the same for Business (make sure you drag from the table where BUSINESSID is the PK to the table where BusinessID is the FK). Same with PurchaseOrderID to tblPODetail. And again from tblProduct to tblPODetail.

Leslie

 

[mspeppa19]

Ok, I got that part working but I am now having trouble creating forms. Do I need to create a type of query? or can I just use the form wizard to create my forms?

What I have done is create a seperate form for each table. A business, purchase, product and vendor table. And then I placed each of them as a subform into the business table. Everything seems to work, but the product table does not update. The vendor and purchase ID are referenced properly, but when it comes to the products its as if I never even entered them into my system.

 

[lespaul]

I'm not understanding what you are saying. You're mixing terms:

And then I placed each of them as a subform into the business table.

I'm not sure how you can make subforms in tables!

What I usually do when designing forms is decide what the users need to be able to do. In this case, I would say users need to do maintenance to tables, record and modify purchase orders.

So there should be three simple user interfaces to allow access to the vendor, business and products tables to let users add, delete or modify these records. Then there should be a process to create a new purchase order. Create a form that the user selects the vendor and the business and items for the purchase order. Create a search form that lets users search for purchase order by number, vendor or business.

I would recommend the Access - Forms forum702 and Access - VBA forum705 for further instruction, I don't really develop in Access, but table design and SQL is fairly generic! Good luck on your project!

Leslie