First, I appreciate anyone even rea 1

[mmaglio]

First, I appreciate anyone even reading this, it is pretty long, but i wanted to get all the details that might matter. If its not enough, I will add whatever. Thanks in advance!


I have been trying for a few days with little success to program this database. (I am very new at this)
I have these basic tables:
Manufacturer(Manufacturer, ManID)
Vendor(Vendor, VendID)
Product(ProductType, ProdID)
with the **ID as the PK's

The Products are general, like 'valve', they are made by many manufacturers.
The Vendors sell Products from many Manufacturers.
The Manufacturers make many products.

I have also created tables (manually):
Manf-Prod(ManID, ProdID)
Manf-Vend(ManID, VendID)
I designed these so that the combo of both columns is the PK.(was this right?)

and this final table with a query:
Vend-Prod(VendID, ProdID)
I cant get this table to accept anything as a PK, and Im not sure if i even need one.

This is what i want; to know which vendors sell which product types. (Once the user knows which vendors sell what he's looking for, he can call them up and get what he wants.) However, I can't figure out why this final table repeats its values many times, for instance, say one vendor sells valves from 10 different companies. This doesnt matter, and I dont want it to show up 10 times on my table.

Is there a certain type of join or query or method of producing relationships that i should be using somewhere that I am missing? (I was told to look at help files about totals queries, but this seemed pretty irrelevant)


Thanks to all,
Mark

 

[delfishie]

So I'm not the greatest at this.. but this is what I would suggest trying..

Setting up relationships. It sounds like you want a one to many relationship with each Vendor as the one and the products as the many. Certainly I do not know the specifics of your database, but it sounds like that might work out. You will need to include a field with the name of the primary key in the Vendor table in each subsequent table, though. Then you should be able to find the data in the way that you need.

If this is below where you're at in making your database, I apologize. You didn't mention having existing relationships so I wasn't sure if it was something you tried yet.

If I can be of any help, please let me know.

 

[mmaglio]

The problem is that I guess it is a many many relationship: each vendor has many product types, many vendors have each product type. this is the problem. I can get the vendor-product table to be made with a make-table query, but it repeats some of the rows many times (because the vendor sells the same product type from each manufacturer.)

I cant manually create the vendor-product table because it would just get ridiculous to update, and it will get awfully bloated if i dont make all the repeats disappear.
I just want to update which manufacturer makes which products, and which vendor sells which manufacturer.

Thank you for the help, though. I appreciate it.
-Mark

 

[SMerrill]

Excuse me, but Vendors do not sell Manufacturers. This is an extraneous relationship making a triangle that will never relationally resolve.

What you cannot get away from is the fact that certain Vendors sell a Product from some, but not all Manufacturers of that Product.

You seem to confuse "Product" and "ProductType". Do you wish to change the name of your Product table to ProductType for clarity? Or do you wish to make a new ProductType table?

The truth is, that Vendors sell Products, and each Product has one ProductType.

I'll think a little harder on this, and get back with you. But that's food for your thoughts.

Take care,
--Shaun

"I wish that my room had a floor; I don't care so much for a door.
But this crawling around without touching the ground is getting to be quite a bore!" -- Gelett Burgess

 

[mmaglio]

Thanks for pointing that out, I guess I was unclear.

Vendors don't sell manufacturers.(obviously)
Vendors represent and sell many manufacturer's product lines.
Manufacturer's are represented by many vendors.
This database doesn't included explicit products, only the product type (otherwise this database would be obsolete about every day)

The logic I have behind this is that I have a large binder full of sheets from vendors listing what type of products they sell and whom they are made by. As I go through it, I list what manufacturers go with what vendors and what product types manufacturers are selling (in separate tables). This way I can (hopefully) just update what new manufacturer a vendor is representing, etc. and the rest will take care of itself. This is the ultimate goal of the db. Then anyone can look up the product type they want, get a list of vendors who sell it, and then get a list of manufacturers who make it (if they need it).

 

[SMerrill]

I think I've got a great answer for you.

Skip all the many-to-many relationships and make yourself a many-to-many-to-many table. This table should be very easy to populate if you use two combo-boxes in a subform(ProductType,Manufacturer) and one combo-box in the main form (Vendor). Filter the subform to the combo-box on the main form.

ManfVendProd(ManfID, ProdTypeId, VendID)

Then, use two simple queries to give the information you need:

WhoManufacturesThis (

  SELECT ProductType.ProductType, Vendor.Vendor
  FROM (ManfVendProd 
  INNER JOIN ProductType ON ManfVendProd.ProdTypeID = ProductType.ProdTypeID) 
  INNER JOIN Vendor ON ManfVendProd.VendID = Vendor.VendID
  GROUP BY ProductType.ProductType, Vendor.Vendor;

)

WhoSellsThis (

  SELECT ProductType.ProductType, Vendor.Vendor
  FROM (ManfVendProd INNER JOIN Vendor ON ManfVendProd.VendID = Vendor.VendID) 
  INNER JOIN ProductType ON ManfVendProd.ProdTypeID = ProductType.ProdTypeID
  GROUP BY ProductType.ProductType, Vendor.Vendor;

)

Take care,
--Shaun

"I wish that my room had a floor; I don't care so much for a door.
But this crawling around without touching the ground is getting to be quite a bore!" -- Gelett Burgess

 

[mmaglio]

i just started reading this and it sounds interesting already... thanks, I'll look into it. I VERY much appreciate the help. Everybody here has been very helpful.

 

[SMerrill]

Oh . . . and bind the main form to the Vendor table; bind the subform to the ManfVendProd table.

Take care,
--Shaun

"I wish that my room had a floor; I don't care so much for a door.
But this crawling around without touching the ground is getting to be quite a bore!" -- Gelett Burgess

 

[SMerrill]

This seemed to be the only solution that did not wrap you around your own axle with regard to your existential criterion. I look for intellectual stimulation to break up the daily monotony. I enjoyed this one very much!

Enjoy!

 

[SMerrill]

For what it's worth:

You could add two optional date fields to the center table, thus giving you a start-date and a stop-date.

This would allow you to forecast the availability of a part; or say, "Nobody has carried that part since ..."

 

[mmaglio]

Glad you enjoyed it; hope i enjoy implementing the solution.
As far as intellectual stimulation, 'The Age of Spiritual Machines' does a good job harmonizing the philosophical consequences of quantum physics with Eastern philosophies. And it gives lots of cool predictions of where computing will take us in this century.

Thanks again,