new to access - not really understanding relationships

[mspeppa19]

so i'm trying to create a relationship and it's not working! i have the following tables

[vendor]
*ven_id
prod_id


[product]
*prod_id
ven_id

[ven_contact]
*ven_id
ven_name
ven_contact
ven_add

[prod_desc] NO PK
prod_id
prod_desc

[prod_cost] NO PK
prod_cost
prod_price

Ok, so prod_desc and prod_cost table have no primary keys because for every product there should be only one description and one cost. the primary key for vendor is ven_id and the primary key for product is prod_id. Every vendor can have one-to-many products, but every product should have only one vendor. It seems to look correct on my relationship table but when I go to make my forms I create them seperately.

The first form [vendor form] has ven_id, and all of the ven_contact information

The second form [product form] hsa the prod_id and ven_id from the [product] table, plus the prod_desc and prod_cost (excluding the respective prod_id of those tables) So what I do next is I put the [product form] as a subform into the [vendor form]

Well everything seems to be working smoothly but when I close the form and open it back up, I can see all of my vendors, but the products do not show up in the product table, it doesn't even show that I have entered a product for a vendor, but in the product table there is a whole list of the stuff I entered.

Also, I create a query based on ven_id from the vendor table and prod_id from the product table plus the prod_desc and prod_cost, but when I go to pull things up the query doesn't return anything if any of the less important info is missing. For example of I do not include a prod_desc or a prod_cost then the query returns nothing back. I'm not sure what's going on, but any help would be greatly appreciated


Ms. Peppa

 

[BNPMike]

The first thing is relationships don't really do anything. You still have to specify connections for each query/form/report. If you created the form with the form/subform wizard it will perhaps have taken your relationship into account and pre-filled the connections.

So the first thing is to design and run to queries - one for the main form and one for the subform to see if they are going to give you the right information. Then re-build your form from those.

One problem seems to be you need outer joins as you have potentially missing data eg from the 3 product entities. From what you show above there is no point in having three and you should collapse them into just one.

Anyway all tables should have a primary key so I wonder how you manage to connect to [prod_cost] at the moment.

It also looks as though you have got a form with all products and all vendors. What have you done to tell Access if and how you want those to work together?

I suspect from what you've said, you've declared relationships and then assumed Access will always display records in a logically connected way. There are technical reasons why a relational database cannot do that. You have to manage that yourself.

 

[Darrylles]

Hi,

In this case, your prod_desc, prod_cost and prod_price fields should all be in your product table (there is no reason for them to be in seperate tables, as you've already said they will only ever occur once).

This will simplify matters for you.

If you are saying that they MIGHT occur more than once each, then you must define relationships as if they WILL occur more than once (otherwise you just don't have the option when you need it).
In that case, each table will need a primary key and a foreign key to it's related table (product).

Regards,

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@hotmail.com

 

[fkegley]

Try structuring your tables like this:

[vendor]
*ven_id
ven_name
ven_contact
ven_add


[product]
*prod_id
ven_id
prod_desc
prod_cost
prod_price

Join tables on ven_id. Then use form wizard to build a form/subform that contains all of the fields from the two tables.


Frank kegley
fkegley@hotmail.com

 

[lespaul]

The table layout below is typical of a vendor/product database. You have 3 tables, one for vendors, one for products and one that shows which products you get from what vendors.

tblVendor
VendorID (PK)
VendorName
VendorAddress
(any other information about the vendor)

tblProducts
ProductID
ProductDescription
SellingPrice
(any other information about a product)

tblVendorProducts
VendorID
ProductID
ProductCost

So to place relationships between these tables, in the relationship window you would drag tblVendor.VendorID to tblVendorProducts.VendorID. Then drag the tblProducts.ProductID to tblVendorProducts.ProductID.

Now unless you select the Enforce Referential Integrity in the popup box, all these "relationships" do is allow Access to create the proper joins in the queries. When you select the enforce referential integrity, you tell Access do not let the users enter a VendorID in the tblVendorProducts unless that VendorID exists in tblVendor.

In order for you to properly design your tables, you should check out

http://www.alphabetcitydataworks.com

this is a site run by TT member JeremyNYC - check out the developers section and read the article 'Fundamentals of Relational Database Design'. Should help you out!

HTH

leslie

 

[lespaul]

fkegley, what if you get the same product from two different vendors? With your layout you would have two productIDs for basically the same thing. The tblVendorProducts would allow for primary and secondary sources for a product and allows for different costs from the different vendors.

Leslie