If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Generate OrderNo by comparing the previous orders
I want to combine master Order table and order_detail transactional table.
If my order_detail table has orders today then I want to give OrderNo depending on the previous orders made by customer. I need to combine both the tables and the resulting table should show only transactional data with order_id, date_ordered and OrderNo. If there is no entry in the master table then ‘Orderno= Order 1’ and then I have to insert this data into the master table as new record. I have the following which works fine and gives the OrderNo, if there is a order_id in the master table and shows blank if there is no entry in the master table. SELECT 'Order ' & (Count(orders.order_id)+1) AS OrderNo, orders.order_id INTO tbl_ordertemp FROM orders right JOIN order_detail ON orders.order_id = order_detail. order_id GROUP BY orders.order_id; To be simple I need to do the following.. if first.Order_id then i = 0 i + 1; if date_ordered ne NULL then if i=1 then OrderNo ='Order 1' else if i=2 then OrderNo='Order 2' else if i=3 then OrderNo='Order 3' else if i=4 then OrderNo='Order 4' if abi end How can I achieve this? Thanks in advance -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/201002/1 |
#2
|
|||
|
|||
Generate OrderNo by comparing the previous orders
mls wrote:
I want to combine master Order table and order_detail transactional table. If my order_detail table has orders today then I want to give OrderNo depending on the previous orders made by customer. I need to combine both the tables and the resulting table should show only transactional data with order_id, date_ordered and OrderNo. If there is no entry in the master table then ‘Orderno= Order 1’ and then I have to insert this data into the master table as new record. I have the following which works fine and gives the OrderNo, if there is a order_id in the master table and shows blank if there is no entry in the master table. SELECT 'Order ' & (Count(orders.order_id)+1) AS OrderNo, orders.order_id INTO tbl_ordertemp FROM orders right JOIN order_detail ON orders.order_id = order_detail. order_id GROUP BY orders.order_id; To be simple I need to do the following.. if first.Order_id then i = 0 i + 1; if date_ordered ne NULL then if i=1 then OrderNo ='Order 1' else if i=2 then OrderNo='Order 2' else if i=3 then OrderNo='Order 3' else if i=4 then OrderNo='Order 4' if abi end How can I achieve this? Thanks in advance What is it that you are trying to solve? Do you have a database of orders and order details without unique OrderNo? I do not understand your request- sorry. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/201002/1 |
#3
|
|||
|
|||
Generate OrderNo by comparing the previous orders
Sorry for the confusion. You can ignore this message. I fixed this.
Thanks orange wrote: I want to combine master Order table and order_detail transactional table. If my order_detail table has orders today then I want to give OrderNo [quoted text clipped - 28 lines] How can I achieve this? Thanks in advance What is it that you are trying to solve? Do you have a database of orders and order details without unique OrderNo? I do not understand your request- sorry. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/201002/1 |
#4
|
|||
|
|||
Generate OrderNo by comparing the previous orders
Consider posting your solution...
Someone in the future may be looking for an answer and your solution may be it. Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "mls via AccessMonster.com" u55943@uwe wrote in message news:a356f690381fe@uwe... Sorry for the confusion. You can ignore this message. I fixed this. Thanks orange wrote: I want to combine master Order table and order_detail transactional table. If my order_detail table has orders today then I want to give OrderNo [quoted text clipped - 28 lines] How can I achieve this? Thanks in advance What is it that you are trying to solve? Do you have a database of orders and order details without unique OrderNo? I do not understand your request- sorry. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...esign/201002/1 |
#5
|
|||
|
|||
Generate OrderNo by comparing the previous orders
My query posted earlier works perfectly..
SELECT 'Order ' & (Count(orders.order_id)+1) AS OrderNo, orders.order_id INTO tbl_ordertemp FROM orders right JOIN order_detail ON orders.order_id = order_detail. order_id GROUP BY order_detail.order_id; Jeff Boyce wrote: Consider posting your solution... Someone in the future may be looking for an answer and your solution may be it. Regards Jeff Boyce Microsoft Access MVP Sorry for the confusion. You can ignore this message. I fixed this. [quoted text clipped - 9 lines] I do not understand your request- sorry. -- Message posted via http://www.accessmonster.com |
Thread Tools | |
Display Modes | |
|
|