Orders Database Storing Orders | Newbie | | Join Date: Mar 2008
Posts: 13
| | |
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 .
|  | Expert | | Join Date: Jan 2008 Location: Sydney
Posts: 788
| | | re: Orders Database Storing Orders
-
INSERT INTO YourOrderTable
-
SELECT * FROM theOtherTable
-
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
| | Newbie | | Join Date: Mar 2008
Posts: 13
| | | re: Orders Database Storing Orders
Quote:
Originally Posted by Delerna -
INSERT INTO YourOrderTable
-
SELECT * FROM theOtherTable
-
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
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?
| | Expert | | Join Date: Jan 2008 Location: Sydney
Posts: 788
| | | re: Orders Database Storing Orders
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,TotalCost
Now you need to attach that list to an OrderNumber and insert them into another table that has the fields OrderID,ProductID,Qty,TotalCost.
Is that correct?
Where is the OrderID coming from?
| | Newbie | | Join Date: Mar 2008
Posts: 13
| | | re: Orders Database Storing Orders
Quote:
Originally Posted by Delerna 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,TotalCost
Now you need to attach that list to an OrderNumber and insert them into another table that has the fields OrderID,ProductID,Qty,TotalCost.
Is that correct?
Where is the OrderID coming from?
OrderId is an autonumber? (do i need it?)
I need to be able to seperate one order from another , how would i do it?
| | Expert | | Join Date: Jan 2008 Location: Sydney
Posts: 788
| | | re: Orders Database Storing Orders
A couple of ways.......which 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 -
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
-
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 -
Declare @Run
-
set @Run=(Select max(Run)+1 FROM TheTableShownAbove)
-
then you just insert your query result into the above table along with @Run. -
INSERT INTO TheTableShownAbove
-
select @Run,ProductID,Qty,TotalCost from YourOriginalQuery
-
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 help
| | Newbie | | Join Date: Mar 2008
Posts: 13
| | | re: Orders Database Storing Orders
Quote:
Originally Posted by Delerna A couple of ways.......which 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
-
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
-
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 -
Declare @Run
-
set @Run=(Select max(Run)+1 FROM TheTableShownAbove)
-
then you just insert your query result into the above table along with @Run. -
INSERT INTO TheTableShownAbove
-
select @Run,ProductID,Qty,TotalCost from YourOriginalQuery
-
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 help
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?
Thanks
| | Expert | | Join Date: Jan 2008 Location: Sydney
Posts: 788
| | | re: Orders Database Storing Orders
I will take a look when I get home tonight
| | Expert | | Join Date: Jan 2008 Location: Sydney
Posts: 788
| | | re: Orders Database Storing Orders
This is the way I understand your problem
1) You have a Stock table
ProdCode,Stock,.......
2) You have a products table
ProdCode,Cost,MinStock,MaxStock,..........
3) You have an OrderRequirement query
ProdCode,Qty,TotalCost,.........
(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,ProdCode,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) -
Create proc CreateOrderLines
-
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,ProdCode,Qty
-
from OrderRequirement
-
go
-
This is just illustrating the idea. How you actually implement it is up to you.
| | Newbie | | Join Date: Mar 2008
Posts: 13
| | | re: Orders Database Storing Orders
Quote:
Originally Posted by Delerna This is the way I understand your problem
1) You have a Stock table
ProdCode,Stock,.......
2) You have a products table
ProdCode,Cost,MinStock,MaxStock,..........
3) You have an OrderRequirement query
ProdCode,Qty,TotalCost,.........
(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,ProdCode,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)
-
Create proc CreateOrderLines
-
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,ProdCode,Qty
-
from OrderRequirement
-
go
-
This is just illustrating the idea. How you actually implement it is up to you.
Thanks for your help , i have used your method and it works perfectly
| | Expert | | Join Date: Jan 2008 Location: Sydney
Posts: 788
| | | re: Orders Database Storing Orders
Good to hear. Thanks for the feedback
| | Newbie | | Join Date: Mar 2008
Posts: 13
| | | re: Orders Database Storing Orders
Any idea how i could get things like promotion on products to work like any 2 of an item for x.xx ?
Thanks
| | Expert | | Join Date: Jan 2008 Location: Sydney
Posts: 788
| | | re: Orders Database Storing Orders
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 please
| | Newbie | | Join Date: Mar 2008
Posts: 13
| | | re: Orders Database Storing Orders
Quote:
Originally Posted by Delerna 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 please 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.
Thanks
| | Expert | | Join Date: Jan 2008 Location: Sydney
Posts: 788
| | | re: Orders Database Storing Orders
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
tblProductSpecials
ProdID,SpecialQty,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 bought
|  |
Similar Microsoft SQL Server bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,449 network members.
|
