I'm not sure I follow the purpose of your Move table.
I've been able to help others set up the ledger table and not have a "move" table. Each record in the ledger table included a location field. Moving product from location A to Location B would be logged with two records in the Ledger table. One that reomved it from Location A and one that added it to Location B.
From such a ledger, you can sort your records to group them by Product, then by location in order for summary fields to show the total on hand for each product at each location. There are several variations of this report you can set up depending on whether you want to see just the figures for one product and whether you want to see the individual changes in inventory or just the current totals.
Thanks so much for the prompt reply!
Well, now you've said it, I'm not sure why I have the move table either. :) At the moment I mostly use it to print barcodes and provide human-readable names for the locations on some layouts, but I suspect it's mostly there because it seemed logical at the time. I can probably get rid of it if necessary.
The setup of the ledger I am somewhat more cautious about. The workflow of my business is extremely untidy: though we have only a few employees, I'd estimate we handle 100-200 units of product a day, all of which has to kept track of via both type and batch (we have to log each day's production separately), and it's unfortunately common for a batch to be split between two or three locations and then sold out of any of them -- or all of them. The good folks who are moving and selling all this stuff have quite enough to deal with, so I'm trying to keep the number of times I want them to stop and mess around with a barcode scanner very minimal and the scanning itself very simple.
I think I see how the setup you're describing would be able to manage a batch split between two or three locations, which is a primary concern, but I am worried that it would clog up the workflow. Would my guys need to scan "out" product and then do a separate scan "in" for the identical product and amount? (I can probably talk them into it, but I was promising a single scan, so... I may need to go armed with chocolate....) Also, is there a way in your setup to differentiate between product being "removed" to a different location and being permanently removed from inventory (sold)?
Don't get me wrong -- I see what you're saying and this may well be the solution I go with. But if I can appeal to your patience while I try to wrap my head around the details.
The starting point is the structure of the ledger table. Once you have that, you can set up layouts customized to the needs of your business. It would certainly be possible to scan an item once and then input the needed info to log a move from one location to another that generates two records in the ledger off of that single scan.
This is similiar to what I'm looking to create. Would you be able to send me your template?
I may have a demo file buried somewhere but I am not finding it and lack the time to really search through older files looking for it. I did pull up this thread that describes the method I recommend in some detail: Managing Inventory using a Transactions Ledger