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