DataBase - Generate OrderNo by comparing the previous orders

Asked By mls via AccessMonster.com

08-Feb-10 06:45 PM

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/Forums.aspx/access-tablesdbdesign/201002/1

08-Feb-10 07:21 PM

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/Forums.aspx/access-tablesdbdesign/201002/1

08-Feb-10 07:30 PM

Sorry for the confusion. You can ignore this message. I fixed this.

Thanks

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/201002/1

08-Feb-10 04:20 PM

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.

08-Feb-10 09:36 PM

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;



--
Message posted via http://www.accessmonster.com