Inventory Purchasing Aid

[Skip Bisconer]

I am building a database to give our buyer a tool to find under and over inventoried items and develop a recommended buy report by inventory item, vendor and warehouse location.

I am working developing this information from a nightly download from our main server into Access. With this process I am trying to link tables from the Access download and build the queries I need to develop Line Point, Order Point, EOQ and so on which requre some computations recommended by the "Invetory Guru's". I also have some fixed amounts such as Lead Times, Review Cycle, Safety Allowances, Carrying and Replenishment costs that need to be tweaked by the buyer by line item if necessary. I estimate that only 20% if our items will qualify for these calculations the balance to the 10K line items will probably be set with Min/Max quatities which also must be included in the view/report.

I have reviewed the Northwind database for examples and have found a lot of what I want it to display like but it doesn't go far enough for our purposes. As an example the Purchasing dept. wants to use the average of the last 8 months of unit sales to calcuale a Montly average sales to use in the calulations, which means I must use a revolving montly average for 12 mothly purchasing periods. In my sale history table I have 24 months of history 1 being a this years January and 13 being the last years January etc.

Here are some of my questions.

Are the calculations for the Line Point, Order Point and EOQ which are based on the results of three other math calcualtions, best done in the queries or on the form or by VBA/Function or sent out to Excel or what?

Where should I put the fixed calculations' ( I am assuming for the moment in the form), that the buyer may have to tweak?

What is the best way to set up the revolving month sales average? Build 12 queries or with a function or code of some sort?

I am definitley not an expert here but I have found by just plugging through each issue as they invariably come up, I usually end up with a usable product for the user. Of course, it goes with saying, with a lot of help from my friends on this board.

I am also have a big problem with using linked Access tables but am currently on another post with this issue.

 

[The_Doc_Man]

First, I'm not an inventory control expert, so some of the questions will be answered from a DB theory standpoint, not an inventory control viewpoint.

Are the calculations for the Line Point, Order Point and EOQ which are based on the results of three other math calcualtions, best done in the queries or on the form or by VBA/Function or sent out to Excel or what?

I would rank "in query" and "in VBA" close, "send to Excel" would be my LAST choice. Access can do everything that Excel can do computation-wise if you just organize the data correctly. And it will do it more efficiently and with greater specificity than Excel ever could. The "in VBA" option might be complex for a novice but offers greater flexibility down the road.

Where should I put the fixed calculations' ( I am assuming for the moment in the form), that the buyer may have to tweak?

This choice will be purely application driven. From the information provided I do not offhand see a clear-cut answer. It is possible that I misunderstand the type of tweaking, because offhand I don't see the underlying set-theory relationship. I guess my counter-question to you is if your buyer chooses to tweak a given line item that applies to, say, the March sales figures, will that tweak also affect April, May,..., September, October (8 month window)?

What is the best way to set up the revolving month sales average? Build 12 queries or with a function or code of some sort?

Since it seems that you are doing the full-blown import daily, you are losing the ability to maintain history. (Unless I totally misread your description, which is totally possible given these old cataract-blurred eyes.) If there is a way to drive it, a query based on the complex operator "BETWEEN ... AND ..." syntax is probably your best bet. Look into the idea of having some dinky little table of dates as a crutch to make the operation workable. Like, two dates plus other data identifying the averaging period, then JOIN that table to the table from which you are gathering the data to be averaged.

SELECT A.FIRSTDATE, A.SECONDDATE, AVG( B.SALES ) FROM DINKYTABLE A, BIGTABLE B WHERE B.SALEDATE BETWEEN A.FIRSTDATE AND A.SECONDDATE ;

(expand the above to your situation, of course).

Theoretically, the above might offend purists, but as a way of facilitating the moving-window stuff, there are precedents for taking the easy way out.

If you continue to post supplemental information, I'm sure many of our members will be able to contribute. Some of them have way more experience than I do with formal inventory control methodology.

 

[Skip Bisconer]

Thanks for your input. I will experiment with these suggestions and come back with issues, as you suggested.