Storage Bin Movement - Access Developer Forum


	Back to Access Developer Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Storage Bin Movement

William Campbell

4 years ago

Hi,

I've nearly finished my access database but am searching for a way to transfer stock from location/storage bin to another location/storage bin.

I'm currently receiving items in on tblReceive & tblReceiveItem and dispatching with tblDispatch & tblDispatchItem (this gets products from tblReceiveItem before using an update query to remove qty from stock on hand qty in tblReceiveItem.

I still need to be able to log each move/transfer so altering the original tblReceiveItem isn't an option.

I was thinking maybe a separate table (tblProductMovement) to track each individual movement and then an update query to remove qty from tblReceiveItem stock on hand and then write a new record in same table with new movement, which would reflect change when dispatching as well. I haven't touched Access in a couple of months though an am lost.

Any help would be appreciated!

Thank you

Scott Axton @Reply

4 years ago

I would start out by looking at the Inventory Management video.

How you handle it or account for the movement will kind of determine what you do or need.
In the 'movement of the product', is it a VA (Value Added) operation or just a physical move of location?
example: You bring in raw lumber and mill it down to parts for a cabinet would be a VA type of move.
On the other hand, if you're a car salesman and just move it from Lot A to Lot B and nothing changed on the car I would just have a "location field" in the record and just update the field.
Unless you need to have a record of where and when it moved and who moved it.

So... The answer is, it depends on your needs and what your work flow is. The nice thing is, Access being so customizable, you can pretty much do what ever you need.
To help further we would need a more information on what you are doing.

Kevin Yip @Reply

4 years ago

William, if you're running a business, you definitely want to keep records of everything. A shipment may have tracking numbers, arrival date, carrier's name, etc., any data you want to keep. My "rule" is if anything has paperwork involved, it probably needs to be recorded. In my old job, every shipment had a "bill of lading" that documented everything about the shipment, from its content, ship date, to the signature of the driver who drove the truck that transported our goods. As Scott said, your business needs decide everything.

William Campbell OP @Reply

4 years ago

So I did think originally that, yes I could just change storage location, but then that would change my receiving paperwork for location where it was originally put. The second thing is that if I'm only moving half of the received items to another storage bin, then that wouldn't work either.

So what I'm thinking is...
Have an update query do the following.
Subtract stock at hand from the relevant tblReceiveItem row/record being moved.
Create new row/record in tblReceiveItem with new location and storage bin. (Is this possible?)

This would then allow me to keep the original receive item associated with my tblReceive and then have a new location to pick when using tblReceiveItem for products available when dispatching goods

	William Campbell OP @Reply 4 years ago
OK! I worked it! So I use an Update query for the subtraction from where it is being moved and then I use an Append query to add a new record in with the new location information.

	Scott Axton @Reply 4 years ago
Whoo hoo! Glad you got it. Sometimes just talking it out is all you need. The small victories are rewarding too.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Developer Forum.

Next Unseen

New Feature: Comment Live View