Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Brugge, Belgium
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Manipulating backorders (ms Access 2007)

    One part of my Acces database concernes managing orders. In practice, not all registered orders can be completely managed, for the simple reason that in some situations the stock of the product that is ordered is inadequate to fullfill the demand. In such a situation , only what is in stock will be delivered and we have to deal with a backorder. In the table 'tblOrderdetails' there is a field 'backorder' into which during processing such a kind of orderitem, a quantity is entered which is equal to : ordered quantity - current stock. In the meantime the stock becomes zero.
    Normally, an orderitem that is been executed is marked as such, but what with orderitems that has a backorder?
    What's the best way to manage these backorders. Is one of the following ideas a good and a practice one or is there a better idea :
    1) After executing the orderitem, replace the value in the field 'orderedquantity' by the value that is present in the field 'backorder' and mark the orderitem in some way as still to be executed.
    2) Make use of an appendquery to append the orderitem in a table 'tblBackorders' ,but with the value in the field 'orderedquantity' replaced by the value in the field 'backorder" and mark the original orderitem as excecuted. This has to be done during the afterupdate of the orderitem.
    3) When managing backorders, build a recordset from tblOrders and tblOrderdetails , wherein only the fields 'ClientID" , 'OrderID' and 'OrderedDate' from table 'tblOrders' and all the fields of the table 'tblOrderdetails' with the exception of the field 'Orderedquantity' . The where clause for this recordset must be: 'tblOrderdetails.Backorder >0 " . This recordset must serves as source for managing the backorders, the making of deliveryreports and invoice. After mananging the records from this recordset, mark these records as excecuted.
    Solutions 1 seems to me as the one that doesn't oblige me to write new code to manage these orderitems, because they remains in the same tables as the orginal ones, but is this a point to choose for this kind of solution?

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Manipulating backorders (ms Access 2007)

    It is hard to know how to answer this question. Once you sell things, and deal with backorders, you get into the realm of a fully fledged accounting program, which raises a whole host of other issues and complications to take account of.

    <UL><LI>I don't like the idea of changing the actual quantity ordered to the quantity backordered. I think that would create dreadful problems.
    <LI>I don't like the idea of having a separate table for backorders. I think that would complicate things[/list]When goods are backordered will the customer receive a separate invoice for the backordered goods? If so, will it have the same or different invoice number as the original invoice?
    The accounting program I use creates a New Order whenever items are backordered, so it also creates new orderitems records for each backordered item. But this is assuming you will Invoice for the goods when they are finally supplied.

    I had a simpler case a while ago, where I just had a quantity ordered field and a quantity delivered field in tblOrderitems. Whenever the full quantity ordered had not been delivered, it was treated as a backorder. But there was no backorder field.
    Regards
    John



  3. #3
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Brugge, Belgium
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Manipulating backorders (ms Access 2007)

    Thank you for the reply. Indeed it is a difficult matter, and as you asked, will there be another invoice for the backorder or not. I can't answer this question right now. I'll have a nearer look at pros and cons of this matter.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •