Finding next "ordernumber" in database

[Dave Lagergren]

I have a database where I store orders. One order may have multiple records.
Because of this I cannot use the "Key" to assign order numbers. I have a
column "OrderNumber" that needs to increment with each new order. I was
thinking of storing the last assigned OrderNumber in the first record but was
wondering if there was an easier, cleaner way to do this? Possibly looping
through the records to find the highest OrderNumber?

 

[Ronx]

The shopping carts I have seen use 2 tables:
Table 1 has 1 record per order, and links order-number to Customer and
to order details
Table 2 has multiple records for order details, with column for
order-number.
This makes it easy to get the last (latest) order number from Table 1.
--
Ron Symonds - Microsoft MVP (FrontPage)
Reply only to group - emails will be deleted unread.

http://www.rxs-enterprises.org/fp

FrontPage Support: http://www.frontpagemvps.com/

 

[Dave Lagergren]

Ronx - Thanks for the fast reply. That might work (it means I would have to
learn to work with two databases at once).

What I am actually doing is this:
A field sales rep enters an order on our web form. When the order is
submitted the rep is emailed a confirmation and the proper order processor is
emailed a link to click on to view/print the order.

On another page the order processor can update the status of the order by
entering in the order number, selecting a status (cancelled, processed, etc)
from a drop down and selecting their name from a drop dowm. On submission an
email is sent to the sales rep that shows the new status. Right now I simply
write the order information back in a new record with the changes.

When the order is received in the warehouse the order processor once again
changes the status and the email is sent. All told there are 4 statuses per
order.

My comfort level is to keep everything in one database and simply record the
last order number in the first record or to search for it. However, since I
am always willing to learn new things, are you aware of any tutorial
sites/examples of the 2 database solution? Of course this means I would have
to modify 5 web pages to accomodate this...

 

[Thomas A. Rowe]

You only need 1 database, but 2 or more tables within the database.

Table Examples:

OrderNumbers
Customers
Orders
Products
States
Countries
Shipping


--
==============================================
Thomas A. Rowe
Microsoft MVP - FrontPage

http://www.Ecom-Data.com
==============================================

 

[Dave Lagergren]

ahhhhhhh! I get it. Right now I have a Results table. I would simply add
an ordernumber table sorted by order number. But then I still need to
understand how to get the next order number. Would I check the number of
records in the database and open the last one, read in the order number and
increment it? (I am trying to keep the number simple because I am doing this
for sales reps and they will botch up any complex order number)

 

[Ronx]

It's one database, several tables.
The scenario you have outlined indicates at least 8 or 9 tables:

Status-states
Data-Processors
Sales-Reps
Suppliers
Products
Customers
Orders
Order-Details
Order-Status - This *may* require a table of its own to maintain a
tracking history for the order.



This is not a simple database update, but a web application.

I would set this up in Access, to work out all the relationships,
queries etc.
I don't know of any tutorials.
--
Ron Symonds - Microsoft MVP (FrontPage)
Reply only to group - emails will be deleted unread.

http://www.rxs-enterprises.org/fp

FrontPage Support: http://www.frontpagemvps.com/

 

[Stefan B Rusynko]

For new order #'s, the safest way to do it is to create a Table in your DB called OrderNo with a single record integer value field
named say NextOrderNo (initial value is your 1st order # as an integer)
Each time you need a new order # you open the OrderNo table, get the NextOrderNo value, and then Update the single record to
increment NextOrderNo value by 1 (so it is ready for next order #)
- if you need to know the last order issued it will be the NextOrderNo value decremented by 1


--

_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
_____________________________________________


|I have a database where I store orders. One order may have multiple records.
| Because of this I cannot use the "Key" to assign order numbers. I have a
| column "OrderNumber" that needs to increment with each new order. I was
| thinking of storing the last assigned OrderNumber in the first record but was
| wondering if there was an easier, cleaner way to do this? Possibly looping
| through the records to find the highest OrderNumber?
|
| --
| Dave Lagergren
| Manager - Data Applications
| Wireless Management, Inc
| Specializing in cellular wireless applications