I have a Database with a products table that has min / max stock fiqures , i can query the table for products that are below the min and get it to work out how much of a product needs ordering , but how can i get this data to store in a table like an Order id then it has the all the product codes and the amounts to order? Basically i want to store the query to a table .
-
-
[code=sql]
INSERT INTO YourOrderTable
SELECT * FROM theOtherTable
[/code]
1)the select query is the same your select query that you mentioned
2) Make sure YourOrderTable has fields that are appropriate to recieve the data from the select -
Originally posted by Delerna
If my oder table has these fields
Productcode qty totalcost
which then places those items to a unique order id, how do i get multiple product codes and qty to store to that orderId as there is only room for one product to a orderid or do i need another table to store multiple products to the orderid?Comment
-
I'm a bit confused now.
I thought you had your query sorted and was wanting to know how to save the query results to a table. Did I missread your first post?
So basically you have a query that returns a list of products that need to be ordered along with the qty that needs to be ordered.
The fields for that query are ProductID,Qty,T otalCost
Now you need to attach that list to an OrderNumber and insert them into another table that has the fields OrderID,Product ID,Qty,TotalCos t.
Is that correct?
Where is the OrderID coming from?Comment
-
Originally posted by Delerna
OrderId is an autonumber? (do i need it?)
I need to be able to seperate one order from another , how would i do it?Comment
-
A couple of ways.......whic h is best depends on your needs.
You could have an order number in the table you are inserting the parts to order into.
For example, say you had a table like this
[code=text]
Run , ProductID, Qty, TotalCost
10 2 1 1.34
10 3 4 6.38
10 6 3 2.76
11 2 3 4.06
11 7 1 9.99
[/code]
where run 10 an 11 are, two seperate, previous runs of your order generating insert query.
Now you want to run it again, so to get the next run number
[code=sql]
Declare @Run
set @Run=(Select max(Run)+1 FROM TheTableShownAb ove)
[/code]
then you just insert your query result into the above table along with @Run.
[code=sql]
INSERT INTO TheTableShownAb ove
select @Run,ProductID, Qty,TotalCost from YourOriginalQue ry
[/code]
Another way would be to have a table that stores details about each individual run/order number. You would only do that though if there was other info that you needed to keep.eg when was each order generated, who generated it, when is it required by has it been received, has it been sent etc etc
I can't give you more exact examples as you havent posted enough info.
Does this helpComment
-
Originally posted by Delerna
Thanks , i think the 2 option would be best for future use of more info . I am using vb 2008 express and sql express
my products table contains:
ProductCode
Description
SellPrice
SupplierID
DepartmentID
StockLevel
Min
Max
ProdcutCost
Would i need to take the primay key off the ordernumer or use another table that allows runs to be set to a ordernumer which then can be filtered by order number on a datagrid?
ThanksComment
-
I will take a look when I get home tonightComment
-
This is the way I understand your problem
1) You have a Stock table
ProdCode,Stock, .......
2) You have a products table
ProdCode,Cost,M inStock,MaxStoc k,..........
3) You have an OrderRequiremen t query
ProdCode,Qty,To talCost,....... ..
(you really don't need to save TotalCost, its calculable)
4) You have an Orders table
OrderID,....... ..
5) You want to save the results of (3) against an OrderID from (4)
I suggest you create an OrderLines table
OrderID,ProdCod e,Qty,........
(you really don't need to save TotalCost, its calculable)
Now write a stored proc that
a) obtain the OrderID from (4) into a variable
b) Save the results of (3) along with the variable into (5)
[Code=sql]
Create proc CreateOrderLine s
as
--obtain the OrderID from (4) into a variable
Declare @OrdID
set @OrdID=(Select max(OrderID) FROM Orders)
--I am assuming that the last OrdID is the one we are creating
--Save the results of (3) along with the variable into (5)
INSERT INTO OrderLines
select @OrdID as OrderID,ProdCod e,Qty
from OrderRequiremen t
go
[/code]
This is just illustrating the idea. How you actually implement it is up to you.Comment
-
Originally posted by Delerna
Thanks for your help , i have used your method and it works perfectlyComment
-
Good to hear. Thanks for the feedbackComment
-
Any idea how i could get things like promotion on products to work like any 2 of an item for x.xx ?
ThanksComment
-
How would you like it to work. From the users perspective I mean.
Is that a suppliers special and you want to inform the person creating the order that you only need 1 but if you could get 2 cheaply? Some other scenario?
A bit more detail pleaseComment
-
Good idea for the orders bit but i was thinking about the sales side when the goods are being sold to a customer , so far i have got it to process sales of items but just wondering how i would go about creating offers so like when 2 any two items are bought it would give a discount off the sale. Like 1 apple is 50p but for 2 apples it would be 70p so it would -30p off the sale.Originally posted by Delerna
ThanksComment
-
You could have a field in the products table where you save a string something like "2 for 70p". Then you just display the contents of that field somewhere on whatever you are using as a ftont end to the customer.
If you want multiple secials per product item or if only a few products have specials then use a seperate table
tblProductSpeci als
ProdID,SpecialQ ty,SpecialPrice
That way you can have this for any given product
2 for 70p
5 for 1.40
10 for 2.10
etc etc etc
and you wouldn't be wasting space in the products table because on most products the field is left empty. (Normalization)
Now when the customer buys the product just get the price for the quantities boughtComment
Comment