|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Finding next "ordernumber" in databaseI 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 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/ Show quote "Dave Lagergren" <DaveLagerg***@discussions.microsoft.com> wrote in message news:B6E1DA91-8E2A-47B3-AD60-FB5924ACEA2C@microsoft.com: > 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 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... :) -- Dave Lagergren Manager - Data Applications Wireless Management, Inc Specializing in cellular wireless applications Show quote "Ronx" wrote: > 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" <DaveLagerg***@discussions.microsoft.com> wrote in > message news:B6E1DA91-8E2A-47B3-AD60-FB5924ACEA2C@microsoft.com: > > > 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 > > 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 ============================================== Show quote "Dave Lagergren" <DaveLagerg***@discussions.microsoft.com> wrote in message news:9FE04DC2-847C-4D69-8D2E-923818917371@microsoft.com... > 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... :) > > -- > Dave Lagergren > Manager - Data Applications > Wireless Management, Inc > Specializing in cellular wireless applications > > > "Ronx" wrote: > >> 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" <DaveLagerg***@discussions.microsoft.com> wrote in >> message news:B6E1DA91-8E2A-47B3-AD60-FB5924ACEA2C@microsoft.com: >> >> > 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 >> >> 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) -- Dave Lagergren Manager - Data Applications Wireless Management, Inc Specializing in cellular wireless applications Show quote "Thomas A. Rowe" wrote: > 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" <DaveLagerg***@discussions.microsoft.com> wrote in message > news:9FE04DC2-847C-4D69-8D2E-923818917371@microsoft.com... > > 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... :) > > > > -- > > Dave Lagergren > > Manager - Data Applications > > Wireless Management, Inc > > Specializing in cellular wireless applications > > > > > > "Ronx" wrote: > > > >> 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" <DaveLagerg***@discussions.microsoft.com> wrote in > >> message news:B6E1DA91-8E2A-47B3-AD60-FB5924ACEA2C@microsoft.com: > >> > >> > 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 > >> > >> > > > 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/ Show quote "Dave Lagergren" <DaveLagerg***@discussions.microsoft.com> wrote in message news:9FE04DC2-847C-4D69-8D2E-923818917371@microsoft.com: > 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... :) > > -- > Dave Lagergren > Manager - Data Applications > Wireless Management, Inc > Specializing in cellular wireless applications > > > "Ronx" wrote: > > > 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" <DaveLagerg***@discussions.microsoft.com> wrote in > > message news:B6E1DA91-8E2A-47B3-AD60-FB5924ACEA2C@microsoft.com: > > > > > 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 > > > > 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!" (-; _____________________________________________ Show quote "Dave Lagergren" <DaveLagerg***@discussions.microsoft.com> wrote in message news:B6E1DA91-8E2A-47B3-AD60-FB5924ACEA2C@microsoft.com... |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  |
|||||||||||||||||||||||
Other interesting topics