4 Replies Latest reply on Jul 13, 2009 9:08 AM by etripoli

    Simple Calculation



      Simple Calculation


      Hello All,


      I need help finalize my database. it is a basic POS system where I have a table for Inventory, sales and purchase. I need to set it up so that If one item is sold then 1 is removed from the stock, and If I purchase 1 item then 1 is added to the inventory. I have made the shell of it with all approperiate fields but can't seem to figure out a calculation that will let me do this.


      any suggestion is much appreciated.




        • 1. Re: Simple Calculation
             Please clarify, is it 1 table each for Inventory, sales, and purchase = 3 tables?  Or 1 table that includes all 3 transactions?
          • 2. Re: Simple Calculation

            For most POS systems, you will need more tables.


            At the very least:

            Inventory   : one record for each time items are added/removed from inventory--may also be used as line items records for an invoice or you may have a separate line-items table.

            Price List    : one record for each type of item you sell, includes the current price for each item

            Invoice       : one record for each customer transaction, a link to inventory or a separate line items table so that multiple kinds of items can be listed as part of a single invoice.


            You might also want:

            Customer table: one record for each customer--this can be used to store any customer specific data you put on your invoice so that it appears automatically on the invoices of repeat customers.


            • 3. Re: Simple Calculation

              I guess I should have been more specific, Here is how it is set up...

              I have DB For Purchase Order and Purchase Order Line Items.

              I have DB for Item Inventory

              I have DB for Sales Order and Sales order line item.


              When a customer purchases an item it is done thru Sales order sheet, which is linked to Sales order line items which list all of the item which in turn is linked to DB Invenory. They are linked via Barcode of the each item. The item number, Description, stock and prices are all in Inventory DB. I want to set it up so that for each item that are purchased from the sales order, via order line item 1 item from the stock gets reduced at the Inventory DB.

               Same goes for the Purchase order, it is set up identically as Sales Order, when I purchase from the maunfacturer via the Purchase order, then the itmes get added into the Inventory DB. Currently, I am looking at the past sales invoice and deduction the quantity from the DB which can be very time consuming.


              On different note,

              Is there any method of importing items so that it adds to the current stokc instead fo over writing it?


              Any assisatnce is much appreciated.




              • 4. Re: Simple Calculation

                You might want to setup the Sales Order and Purchase Order layouts to require the user to run a script to 'place/complete' the order.  That way, the script can do the calculation to update the current quantity available.  How do you handle backorders?  And, do you want the qty to update when you complete a purchase order, or when the items on that PO are received.


                I'm not sure I understand the question about adding to the current stock vs. over writing it.