A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Generate OrderNo by comparing the previous orders



 
 
Thread Tools Display Modes
  #1  
Old February 8th, 2010, 07:45 PM posted to microsoft.public.access.tablesdbdesign
mls via AccessMonster.com
external usenet poster
 
Posts: 46
Default 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  
Old February 8th, 2010, 08:21 PM posted to microsoft.public.access.tablesdbdesign
orange via AccessMonster.com
external usenet poster
 
Posts: 15
Default 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  
Old February 8th, 2010, 08:30 PM posted to microsoft.public.access.tablesdbdesign
mls via AccessMonster.com
external usenet poster
 
Posts: 46
Default 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  
Old February 8th, 2010, 10:20 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default 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  
Old February 8th, 2010, 10:36 PM posted to microsoft.public.access.tablesdbdesign
mls via AccessMonster.com
external usenet poster
 
Posts: 46
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:11 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.