4 Replies Latest reply on Apr 20, 2014 1:42 AM by erolst

    How many tables are required?


      Need to keep stock report for items ordered by Batch Number (have 14 items and one batch number can equal many items).


      Stock report needs to be maintained for stock held overseas and stock held in Australia, in two different warehouses (thinking a find is the way to go with the Warehouses).


      Stock is ordered via a Supplier Purchase Order, held overseas as Stock matching Batch Number until a Container Order is placed, then it is shipped to Australia and then becomes Stock available Australia by Batch Number, until ordered by a customer, when Stock Australia is decreased by Item Batch and Pallet Number


      How many tables: I am suggesting possibly 8?


      Product to Product Line Items to Supplier Purchase Order to Batch/Item to Container Order to Stock Australia to Client Purchase Order to Client Listing


      Needing to produce a Supplier Purchase Order, Stock Held Overseas by Item and Batch Number Report, Container Order, Stock Held Australia by Item and Batch Number Report (increasing on Container Order, decreasing on Client Purchase Order


      Does a template exist anywhere on which I could base this database?

        • 1. Re: How many tables are required?



          Did you ever get s solution for this? I too would want to be able to track by "batch"



          • 2. Re: How many tables are required?

            You might also consider a Many to Many Join Table, where Batches have many items, e.g.







            > How many tables:  I am suggesting possibly 8

            • 3. Re: How many tables are required?



              This sounds like a 1 table solution for the inventory. You can use Country and Warehouse fields to separate inventory by location. You may need a separate table of batch numbers, however, and likely a Country and Warehouse table, but the core inventory table should probably be consolidated.


              The main reason is that this enables you to easily get "global" inventory reports across multiple locatons. You didn't specifically mention that, but it sounds like consolidated reports are part of the report spec. You could get these with multiple tables - adding the amount from each individual table - but then if you add a location or warehouse, you'll have to add it to all of the calcs and/or relationship graphs.


              For example, assuming you have an item table, you can easily get all items from all locations with a simple item control link to your consolidated inventory table, instead of adding up the numbers from the 8+ relationships to each separate inventory table. You can also create filtered portals from an item table to the inventory table to easily report on combinations of warehouses/countries.


              Building a consolidated report on item(s) is a simple sub-summary if they are all in one table; if they are in 8 you have a much more complicated task of building a unified report pulling data from all of the different inventory tables.


              It will also make in and out reports (purchase and sales) much easier.

              • 4. Re: How many tables are required?

                Bob –


                I think the first post suggested/mentioned the number 8 as the total number of tables in an example solution that includes inventory management, rather than using 8 separate inventory tables …