Tell me about your Inventory application


Sorry I haven’t posted for a few weeks. I recently took an enjoyable NCL cruise with ports in Costa Maya, Santo Tomas De Castilla, Belize, and Cozumel. It was rewarding to spend a week playing hard with my lovely two little girls and Taunya. Love that!!


Back to work…


As I have mentioned in previous posts, Access templates are wildly popular and provide a foundation for many new Access applications. Improving the templates and expanding the number of templates offered will continue to be important as we move forward. The Inventory Management happens to be one of the most popular templates we ship, yet the scenarios and potential data models vary wildly. Some inventory applications track specific items while others track quantities. Some scenarios require re-ordering with and with-out purchase orders. As you know, the scenarios get quite complicated.


The Access 2007 version of Inventory is a good starting point but we think there are plenty of ways it can be improved. Some people on my team are going to be spending some time thinking about different user models, scenarios, and possible data model alternatives. I’m a huge fan of listening to customers and hearing more about the inventory applications in the real world.


For those of you with existing Inventory Management applications it would be helpful to learn more about your scenario, navigation model, data entry forms, data models, etc. I would be most grateful if you could take a few minutes to tell me more about your application. The outline might go something like…


Describe your business scenario.
How many tables, forms, queries, and reports are in the application?
How long have you been using the application?
How many records are in the biggest tables?
How many people use the application?
How many people concurrently use the application?
What is the backend database?
Describe the business rules that govern the application.
Describe and provide screen shots of the applications opening screen.
How do people navigate the application?
Describe and provide screen shots on how people enter data into the application.
Provide a simple screenshot of the relationship window with the most important tables.
What reports are most commonly used?


Rest assured, the time and effort you spend describing your application will be super helpful for the next iterations of templates and influence Access 14 development.

Comments (2)

  1. DarrenMyher@hotmail.com says:

    Describe your business scenario:

    – Fully integrated accounting with purchase orders, sales orders, inventory movement between warehouse locations, etc.

    How many tables, forms, queries, and reports are in the application?

    – Inventory-related table: ~20

    How long have you been using the application?

    – Over 10 years

    How many records are in the biggest tables?

    – The biggest tables are the Inventory Movement tables that track the Before / Change / After quantities, from what source, on what transaction date / the postdate/time / the user that made the change, the unique identifier for that transaction, etc.  It is not unusual for there to be hundreds of thousands of records in this table.

    – After that, the next biggest tables are the purchase history tables, and value change tables that track the change in the inventory value for the various costing methods: AVG, FIFO, Lot, Serialzed

    How many people use the application?

    – Thousands across hundreds of different customers

    How many people concurrently use the application?

    – Typically no more than 30-40 concurrent users

    What is the backend database?

    – SQL Server 2000 or 2005

    Describe the business rules that govern the application.

    – Is there somewhere that we can e-mail / post info like this that isn’t the little comments box on the Blog?

    Describe and provide screen shots of the applications opening screen.

    – Is there somewhere that we can e-mail / post info like this that isn’t the little comments box on the Blog?

    – Basic UI elements include:

      – Inventory Screen

      – Inventory Pricing screens for managing / updating Multi-UOM pricing

      – Inventory availability by warehouse location

      – Drill-downs to see what units are allocated to what open order

      – Drill-downs to see what inventory is inbound on open POs and what the expected receipt dates are so that projected availability can be calculated.

    – Inventory re-order screen that allows you to reorder products based on various factors which could include re-order levels (seasonal or not), prior period sales activity, EOQ, etc.

    How do people navigate the application?

    – Primarily by going directly to the screen they want off of a menu.

    Describe and provide screen shots on how people enter data into the application.

    – Again, where can we post screen shots or to whom can we e-mail this information? — all your blog gives us is a text box

    Provide a simple screenshot of the relationship window with the most important tables.

    What reports are most commonly used?

    – Inventory Movement report

    – Inventory Availability report

    – Back Order fill report (items you’ve received that can be used to fill open orders that previously didn’t have sufficient quantity)

    – Purchases report

    – Inventory Sales Reports

    – Inventory Sales Pivot Tables

    – etc.

  2. Feel free to respond directly to me with images. My email is clintc _AT_ microsoft dot com.

    Thanks!