05-03-2004, 05:18 PM
friend has a small mfg. concern, and asked me to create something to allow him to manage his returns/rejects stock - low quantities, low number of different items, and the data only chagnes a few times a month - i'm sure an excel sheet or 5 minute access template could do the trick - but i'm supposed to be on my way there now and i suck at excel and access to the point where it's not worth trying to bother :D
all this thing needs to do is record a manually entered customer name, part number and either an addition or subtraction to the running total (the running balance as per a checkbook is a pretty good example) (and part numbers are only ever applicable to a particular customer - e.g., part number A4 is only ever bought by Customer Jim.). I spent most of the morning looking for something and there are a billion templates or databases or freeware financial packages but they're all way too much - if anyone can help me out with this with a script, xls, mdb, template or directions to one that's very easily configurable (read: minutes, vs. hours) - i'd appreciate it very much.
05-03-2004, 07:31 PM
Excel will do the trick, and I've attached a sample Excel Workbook.
The key is the sum() function. It sums the preceding cells in that column. I think it's pretty self-explanatory. If you have any troubles with it, let me know.
05-04-2004, 10:07 AM
sad: thanks very very much for the help - i appreciate you taking the time. i ended up with nothing to show, so i've got another day - and hopefully i can get the excel thing to work ...
i had something similiar, but the key isn't just a running total - it's got to keep a running total per part number, per customer - all of which can (and will be) entered later by the user - so the first time customer jim sends back 10 forks, they'll open up the workbook, find the sheet/column/record (whatever) named jim, see if there's already an entry for forks (if not add it), otherwise enter in a quantity in either an add-to-stock (positive value) or deduct-from-stock (negative value; which will happen when parts are reworked and resubmitted to customer).
the obvious answer of just having them insert a new sheet isn't feasible because no one in the entire company has an ounce of technical knowledge - in fact, about a third of my gross is thanks to the fact that no one there is able to locate power buttons or realizes electricity is necessary to fuel the devil-computar-boxes.
i tried a little vbscript and macroing a new insertion of a sheet/columns/(tried records in access too) - but ran into some small glitches. i'll attach one that almost made it - except for a minor but insurmountable glitch - i had a macro make a new column, copy the data from the current column to it, then hide it, so they just saw their add quantity vanish and the column directly to the left received a sum function from all the others in that row but ... the marquee (the marching ants) around the now-hidden user entry column stuck and i couldn't find the code to get rid of it - which meant that unless they clicked elsewhere, a keystroke would apply itself to the incorrect column... and in all seriousness asking these folks to remember that is totally pointless...
again, i very much appreciate the help and don't expect you to write it for me (although if it's easier that way, by all means...), but any kind of insight or online resource or anything would be great.
05-04-2004, 11:03 AM
Would this do?
Microsoft Inventory Control (http://office.microsoft.com/templates/preview.aspx?AssetID=TC060827171033&CTT=4&Origin=CT061995421033&CategoryID=CT061995421033) (Excel)
Inventory Management Database (http://office.microsoft.com/templates/preview.aspx?AssetID=TC010184581033&CTT=4&Origin=CT061995421033&CategoryID=CT061995421033) (Access)