Create a Quote using 2 Lookup Tables with Repeated Values (SQL 2008)

Question

I need to create a QUOTE using data from 2 lookup tables (Supplier and Raw Materials)

My Supplier Table has this information

Supp_ID int,
Supp_Name varchar(50)
Supp_Address varchar(50)

My Raw Materials Table has this information

RM_ID int,
RM_Name varchar(50)

My goal is to contact at least 3 different suppliers and get a price for 1-3 raw material prices and store it on my quote table to analyze which supplier gets me the best price on each material.

I am lost as to what process to follow. I know there's has to be some sort of normalization, because every time I retrieve the data it displays the same supplier on each combo box. I want the form to allow the user to select a different supplier from each dropdown menu as well as raw materials.

Please I would appreciate a lot some help with this problem, I haven't been able to sleep for the past 2 days trying to figure it out.

Thank you.

Answer 1

Is this a real-world problem, that is one you work or get paid for, or is it part of some study exercise?

I ask, because there seems to be some bits and pieces missing. For a real-world case I would expect that there is some table that holds which suppliers that supplies which materials.

It is difficult to say what you are doing wrong, since I only see a screen shot of a form, and no code behind. But if you want to take three random suppliers, you can do:

SELECT TOP 3 ...
FROM   Suppliers
ORDER  BY newid()

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

its a class project

There is a supplier table with ID and Names.

There is a raw materials table with ID and Materials Names.

The way it works is I select a supplier from the combo box (linked to suppliers table) and then select the raw material name from the combo box (linked to raw materials table), then I input the Quantity I want and the Total Price I get from that supplier.

Then I move to the next one and so on. It is 3 different suppliers that I'm quoting using the same 3 raw materials, I'm trying to see which gives me the best price on each material. The purpose is to do a supplier's trend to see which suppliers I use the most and which one gives me a better price.

I'm using SQL 2008 and Visual Basic 2010. I have the database created with Supplier and Raw Materials info, I just don't know what I'm missing to bind them.

---

Miguel

Answer 3

Class projects are often more problematic, because there is no one to ask about the fine-print in the business rules. Of course, you could say that this leaves you with the artistic freedom to invent your rules. But that is not really good for your professional career.

With the information you have given me, I am about as clueless as you are. Should we assume that all suppliers supply all materials?

You say: then I input the Quantity I want and the Total Price I get from that supplier. How do you get that Total Price?

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 4

Yes all suppliers supply all materials.

The total price I will input it manually, its  just a number that needs to be saved. No calculation, just storing a number.

Make it easy for you.

1 Supplier and 3 materials in 1 form.

I will have 1 supplier combo box to look in the supplier database and 3 combo boxes for the materials.

How to set that up?

---

Miguel

Answer 5

Well, just set it up!

I'm sorry, I am not sure that I understand what you have problem with. Is the queries, or the form? In the latter case you are in the wrong forum.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 6

As it seems you need the following:

Create a query to fill Suppliers Drop Box List from table suppliers.

Once you select the supplier, a second query will populate Raw Material ID 1, 2, 3 combo box with the Items for that supplier.

Select distinct ItemName from SupplierMaterials where supplierid = @Selected ID From ComboBox.

Once you selected the raw Material, you run the last query to collect Raw Mat Quantity and Raw Material Price.

select top 1 RawMatQtd, RawMatPrice from SupplierMaterials where supplierid = @Selected DI From Combo Box and ItemName = @Selected Item Name.

Apply those rules to all Drop List Boxes.

---

karepa