I have a SQL Query that returns me:
- the "product code" i must purchase,
- the "quantity" to purchase for this product
- and if it's in "promotion or not".
Based on this result, i must to look at "manufacturer" table and find the cheapest "price" for each product to purchase and the quantity available at the manufacturer.
If "quantity to order" (e.g. 20) is higher than "quantity available" (e.g. 12) at "cheapest manufacturer", i must order the missing quantity (8) to the next cheapest manufacturer.
How can i do that ?
I don't think this is just a query, I think this is going to be some VBA. But I need to know specifically how this would work and what you want.
I understand the method, its just pinning down the interface now. Is this going to be a form where you enter one product code and it returns a list of how to order it? Or do you want this to take a whole list of products and create a table for how to order them? Explain the user-interaction part to me.
Use a SELECT Query on the manufacturer table with the Product code as the WHERE and ORDER BY Price and Quantity. You might need a bit of VBA to achieve the Ordering process.
SELECT product, manufacturer, available, price, nz((SELECT SUM(available) from manufacturer as M where product=manufacturer.product and price<manufacturer.price),0) as prevselected, IIf([quantity required]-[prevselected]-[available]>0,[available],[quantity required]-[prevselected]) AS thisorder
FROM manufacturer
WHERE nz((SELECT SUM(available) from manufacturer as M where product=manufacturer.product and price<manufacturer.price),0)<[Enter quantity required]
Order by price
not tested and change names to suit
this will give you which manufacturers, their price and the quantity to order
Here is the result of my previous Query: http://prntscr.com/6m0hin
so you can see what are the product code to order, their respective quantity and if they are promotional or not.
for each product i need to purchase:
1. if product i not promotional, i must order the full quantity to the cheapest manufacturer. So in my case for MON003, i must order 16 to IB for $225
2. for promotional products, i must firstly check if the cheapest manufacturer has enough prod in stock. If not, i order all the prod in stock the manufacturer has, and i must order to missing ones to the next cheapest manufacturer.
So in my case for DTP1002 i must order 5 prod to HP for $700 and next 15 prod to IB for $800
Unfortunately I am not psychic and you have provided minimal information, but I'm sure it is within your capabilities to replace a parameter with what you require.
Thank you for the subsequent more detailed explanation, but regret I will be dropping from this thread.
I don't know exactly what you are asking. Are you questioning if its really impossible with just SQL or are you asking for how to do it with VBA?
Its not possible with pure SQL because its an iterative process. The first record not only effects the second record's value it determines if the second record even appears. Or third, fourth or fifth record for that matter. You need to loop through a record set with VBA and do your calculations on it iteratively to produce the results you want.
The problem is that i do not use any Form, or report.
and as far as i understood the exercise text, i had to do it using only queries... and sub-queries
Here is the exercise text:
Procurement manager, Bob, wants to generate procurement orders for the suppliers.
These procurement orders will be based on the available data and the business rules.
Upon further investigations, we have found out the complete set of business rules:
Business Rules
● Only those products need to be ordered where the PROD_QOH (quantity on
hand) will fall below the PROD_MIN_QOH (minimum quantity on hand) after
providing for the orders with the ‘ON ORDER’ status.
● Procurement order quantity should be such that the final stock is equal to the
PROD_MAX_QOH (maximum quantity on hand)
● Products should be ordered from the supplier with the lowest price for any
product not on promotion.
● If the lowest price supplier cannot provide the required quantity for a product on
promotion, the remaining amount should be ordered from the next best supplier.
● Requested Date should exceed the order date by 10 days
Here are the required steps
1. SM1: Write a query to determine the products which need to be procured and
the corresponding procurement quantity for each product. Results should
include the Product Code, Required Quantity for each product (zero or more)
and whether the product is a promotional product. (7 points)
2. SM2: Write a query to determine the lowest procurement cost for each
product. Results should include the Product Code, Lowest Cost for each
product. (5 points)
3. SM3: For each of the products to be procured, find the procurement order
details: product code, supplier(s) code, order quantity and unit cost. (You can
split this in multiple queries) (12 points)
i already did SM1, SM2 but SM3 is for unknown reason not doable the way i wanted
SELECT sub1.[Product Code], (product.prod_max_qoh-(product.prod_qoh-sub1.[qty_ordered])) AS [Quantity To Order], IIf(product.prod_promo="1","Yes","") AS [Promotional Product]
FROM (SELECT order_line.prod_code AS [Product Code], Sum(order_line.units) AS qty_ordered FROM [order], order_line WHERE [order].status='OnOrder' AND order_line.ol_ordernum = [order].ordernum GROUP BY order_line.prod_code) AS sub1, product
WHERE ( ((product.prod_qoh-sub1.[qty_ordered]) < product.prod_min_qoh) AND (sub1.[Product Code]=product.prod_code));
and my SM2 looks like:
Code:
SELECT product.prod_code AS [Product Code], subLP.[lower_price] AS [Lowest Price]
FROM product, (SELECT s_prod_code, min(prod_cost) AS lower_price FROM supplier_prod GROUP BY s_prod_code) AS subLP
WHERE product.prod_code = subLP.s_prod_code;
