Custom Inventory Management Type App

[ryancgarrett]

Hi Everyone,

I own a small business and took on a project that is outside the scope of what we normally do, and I'm thinking Access is a tool we can use to solve this problem and move on. Here is the situation:

We have around 2,000 items in our warehouse right now (650ish unique) that need to be split into 4 different shipments. I have a packing list for each shipment that has the item UPC and the quantity.

I'd like to use Access to develop something quick and dirty that I can use to scan any given item's bar code, and have the app let me know which shipment it goes in.

If items were not mixed across shipments I'd be just fine, but we may have 10 units of item A, 3 of which go to shipment 1, 5 of which go to shipment 2, and the remaining in shipment 4.

The app I'm envisioning will have a form with a single text field. When I scan a barcode (our scanner automatically enters the 10 digit UPC and presses enter) the form will flash which shipment gets that specific unit, and then return the cursor to the text field for then next scan.

I can get around just fine in Access, and even better in VBA, and would love suggestions on how this could/should be done. Also more than happy to pay a developer to give me their $0.02 on how to build this beast.

 

[jdraw]

What is/are the criteria determining which item goes on which shipment? What is the logic
that creates the packing list?

I have a packing list for each shipment that has the item UPC and the quantity.

You have to know the algorithm in order to program/code it within vba.

 

[ryancgarrett]

The packing lists were given to me by my customer. It is just 3 columns of data, the shipment id, UPC code, and quantity. Since the app is just a one time use sort of project, it's not that important to determine the logic behind the packing list.

Ideally, when I scan a barcode it will tell me the qty each shipment still needs of that item. I've attached an image of a form that I am envisioning.

I think the logic I want to use, is ordering the shipments on the form as I have them. When I scan an item it looks up the qty remaining for each shipment. When I hit enter, it automatically removes 1 unit from the first shipments remaining qty. If the first shipment doesn't have any units remaining, it will take from the 2nd, 3rd, and so on.

As I have typed it out here it has become a little more clear how it should be done, but if anyone has suggestions please feel free to chime in.

 

[jdraw]

Things usually get clearer as you get further into analysis and detail. Some prototyping --what if's--also can help.

A few things to consider:
What are the shipment dates?
Can you check your quantities on hand now, so that you can determine if shipments are even feasible? If you don't have the required items, can you get them in time for the shipment?
Do you have suppliers that can deliver on short notice if you don't have sufficient quantity?
Do you have an area where you can pre-assemble a shipment?

Yes, you can "steal" from shipment 2,3,and/or 4 to complete shipment 1. But make sure the whole job (all 4 shipments) gets completed properly.