8 replies to this topic

[JPEnterprises]

Hello all first post on the board. Now I keep running into the problem of my physical inventoy being off in MOM. I'm not sure if it is getting entered wrong or if it is lost/stolen.

At the end of entering items into the order entry screen my employees are supposed to make sure the total in mom matches the invoice given to them. If not fix, otherwise proceed.

When using the REPORTS->INVENTORY->INVENTORY TRANSACTION I ONLY GET THE DAY WHEN ORDERS WERE PLACED ON ITEMS AND NOT THE ORDER #'S this can become quite tedious when looking for the culprit order that might have an incorrect qty for the item.

Any suggestions on the above situation would be great.

Situation # 2. Biting the bullet and making the adjustment manually to inventory so low level warnings work.

I've adjusted manually inventory items that are incorrect with the physical amount to maintain my ability to rely on the MOM for correct inventory counts.

What should I do in the events of this and how should I think about this when it comes to accounting time.

Thanks,
JS

[dseibold]

Do you have Report Writer or MS Access to work with? If so, I can help you put together a report that will include the order numbers.

Keeping your inventory accurate is a common problem. I found I had items being pulled for kits (both break outs and composites) that were not properly set up. Correcting that situation solved most of my problems.

I went a step further and made a note of problem items. When a problem was discovered, I corrected the inventory, put it on my check list, and two weeks later checked the inventory again. If a discrepancy was found, I only had to look at two weeks to discover what caused the problem.

This method showed me two possible scenarios that can cause errors.

First, when I correct the inventory, I wait until all of the orders have been filled and shipped and the salesmen are gone. Then I check to make sure there are no committed items remaining and no orders that have charged for the item but there were none on the shelf. This is the biggest cause of errors in counting.

Second, if you do a reindex, you may find that your inventory is off. This is caused by a discrepancy between the stock, bin, and the inventor tables. When checking and correcting errors, be sure to check all three tables using dbuwin. If the inventor table has a different number than the stock and bin tables, the reindex will "correct" the stock table to match the inventor table.

We do a full inventory count every 6 months. The number of errors is dropping steadily and I'm hoping we will soon have a 'perfect' count.

[JPEnterprises]

Thanks for the quick respone.

Do you have Report Writer or MS Access to work with? If so, I can help you put together a report that will include the order numbers.

I don't have R&R report but I do have MS Access to work with. Have you built reports using MS Access and the database files?

I've read that small article about the cycle counting of inventory as opposed to a full inventory count. One thing that seems positive is if you're lacking man power, cycle counting can be a lot more manageable than a full inventory count at the end of the year.

JS

[dseibold]

Yes, I use MS Access extensively to analyze our inventory and sales. I'll prepare a query for you that should get what you need. Watch for posting later.

Yes, cycle counting can be very helpful. It depends on your operation. We have too many pending orders to count that often. Those pending orders can really foul up your count. So, since manpower is not a problem for us, we clear out all pending orders and bring everyone in twice a year for a full count. In the meanwhile, I only worry about problems that show up in the process of filling orders such as orders that can't be filled or backorders that have stock on the shelf. Happily, we have fewer and fewer of those.

[john]

We are having some of the same inventory issues. I listed them under the cycle count post. if you cycle count, you'll drive yourself crazy with the ups and downs that are hard to track down.

We'd be interested in your RR report if you are willing to share. We have put a lot of time into this, trying to eliminate anything we are doing that might inadvertantly add or subtract inventory from MOM, but MOM always can amaze us with something we did not know. Your comments about kits and composites are good; we'll check out some of those.

Dydacomp has not been much help here for us. They can untangle a lot of bin and stock issues, but they have not been able to satisfy me as to why we get some of these phantom returns, etc. Future releases of MOM should definitely beef up the inventory modules to near best in class, with better control and auditing capabilities. I know there are a lot of marketing features they need in the software, but inventory is so critical to larger organizations and their business objectives.

if you try to cycle count, i recommend you do the same SKU's for a couple of weeks to make sure you are consistent.

[dseibold]

I'll prepare a query for you that should get what you need. Watch for posting later.

(Picture of me with both feet in mouth)

Well, I spoke before I could perform...
The invtrans.dbf table has no apparent connection with any other table in MOM that I could get the order number from. It has its own transaction id, item id, etc. So, I will not be able to produce the hoped for query. That is really a shame because it would be very helpful! Next time I'll check my facts before I make a promise...

[dseibold]

After a night of being upset that I couldn't provide you with what you wanted, I came up with a second best solution.

In MS Access, link the tables items.dbf, stock.dbf, cms.dbf, and cust.dbf.
Create a query in design view and close the table selection window.
Select the arrow on the right of the Design View icon (icon at left end) and select SQL.
Paste the following into that window:

PARAMETERS [Enter Start Date] DateTime, [Enter Item] Text ( 25 );
SELECT items.item, stock.desc, items.it_sdate, items.order, IIf([lastname]>" ",RTrim([lastname]) & ", " & RTrim([firstname]),[company]) AS Name, items.quanto, items.quantf, items.quantb, items.quantp, items.quants, items.desc, items.sales_id
FROM cust RIGHT JOIN (cms RIGHT JOIN (items LEFT JOIN stock ON items.item = stock.number) ON cms.order = items.order) ON cust.custnum = cms.custnum
WHERE (((items.item) Like [Enter Item] & "*") AND ((items.it_sdate)>=[Enter Start Date]))
ORDER BY items.item, items.order;

Run it a couple of times to see how it works and then you can make a report that fits your needs.

Hope this helps!

[JPEnterprises]

Are there any freeware programs for viewing R&R.

[notehead]

invtrans is in summary anyway and therefore can't relate to line/order level detail. dseinbold appologies for the spelling) has offered a good compomise solution. Also, just copy the SQL (query) and if you have an access database with the tables linked already just create a new query, change the view to SQL view and paste the statement. It is ready top run!

notehead