In reference to the Lending Library Template

[Ufriendly]

I've made most of the changes I need but I am having problems with incorporating one thing I need. A quantity field in the assts table (DONE). I need to do a checkout/in and have that quantity field update the quantity on hand after the transaction.

Here is a video of what I am talking about being able to do? http://vimeopro.com/user3131795/misc-1/video/136509150


Here is the macro code for the check out function that is built in to template.






I can send the DB too if need be. It is small as I haven't done the inventory and data entry yet.

I am a novice since I haven't dealt with Access in over 10 years.

thanks
jack

[rpeare]

The simple answer is do not store the 'quantity on hand' you can calculate that based on the check in/check out dates of your items. Storing values that you have to perpetually update is not a great idea.

That being said, if you want to do things the way you are doing it it looks to me like you have a bad 'where condition' in the last part of your macro it says =1=0 which is always going to be evaluated as false because 1 never equals 0.

[Ufriendly]

Isn't that true only if you have a line item for each chair, as opposed to if you have 16 of the same type chair listed under 1 line item. I originally thought there were 5 or less of everything and would do it with 5 line items of the same 5 chairs. Problem is with that, you would have to do 5 check out (maybe 16 separate one) when you check chairs out, then back in. Wanted to cut that down to checking all 5 (or 16) out and back in together, updating the items on hand number so it's easy to see how many I have left, if any, to check out to the next guy. Hope this makes sense. Thanks for responding!!

[June7]

So you have a table that shows how many of each item are in the library? Determine how many are available for checkout by subtracting the total already out. There is no need to change a value in table.

[Ufriendly]

OK. I don't know how to do that. When I finish entering all the data after the inventory I will have a full house. Then Billy comes and wants three chairs. I do the check out function, BUT there is nothing in that function asking me how many chairs Billy is checking out. I think the way it is set up now, it is assuming you only have one of every item, which means the check out function takes the quantity down 1 to ZERO 0. Then back up one to 1 when the item is checked back in. But Billy took out three of the exact same item, leaving now, 13 (was previously full at 16 chairs). That is what I need to model.

Here is the DB. It is Access 2010. I can use the newest if it would be a help. this DB has about 5 items in it. You'll get an error about a form when it starts, anoher issue, but just OK past. thanks for any help you guys can give.


BB Props xxxxxxx TEST DB2.zip

[June7]

The lending library is designed for book checkout - 1 book per checkout. If you want to modify for any quantity in one checkout, then expect some challenges. Are you retaining history of checkout/checkin? If so, then really need to calculate the net difference of out and in then subtract that from the total of inventory. The only time inventory should actually be changed is when items are received/eliminated (and again conventional approach would be transactions for those events and calculate difference of sums to determine total quantity in stock).

[Ufriendly]

yes I want to retain that history, which this DB does now. So based on what you've said, I would have to have 16 line items, one for each chair I have, even though they are the same type chair. Then barcode them with different numbers, like ser #s, and check one out at a time.So if Billy came for 6 chairs, I'd go get them and do 6 check outs, one for each chair. Give Billy the chairs and when he brought them back, do 6 separate check ins, then put the chairs back in inventory.??

[June7]

No, I did not say you have to do that, just saying that's the way the database is originally designed. If I checkout 1 of 5 copies of a book from my local library, I expect the library has each copy inventoried as a single record and they track that single copy independently of all other copies. But they could still query the db to determine how many copies of the book are accountable and where each one is. (Although it's my experience that libraries don't ordinarily carry multiple copies of books.)

You have modified the db to accommodate a quantity field and now must modify calcs to accomplish the inventory tracking. Constantly changing the inventory quantity in table is loaded with issues. Calculate the available quantity when necessary.

However, this does bring up the issue of how to deal with situation when individual returns more or less quantity than checked out or returned damaged item is discarded and/or new item received. As already noted, this would be adjustment to inventory and really should be documented by transaction records.

[Ufriendly]

OK, not sure how to begin to do that. Let me play a round a bit and see what I can come up with. thanks

[Ufriendly]

Do u think I should use a different template for asset tracking and build in some sort of checkin/out system or sale buy called checkin/out?

[June7]

I think either way will face similar challenges of modifying to customize for your requirements.

[Ufriendly]

OK, lets see if anyone else has any ideas, or code that would help. I don't have the skills to get from where I am to where I want to be. thanks June7 for all your input!!!

[rpeare]

I think June has pretty much explained everything. I think most, if not all, of the more experienced people would agree with his assessment.

You really have (for all intents and purposes) 2 ways of handling your data

1. Each individual item you loan out has a serial number/bar code/whatever and you track that item in and out, can remove it from service for damage or maintenance and can charge customers specifically for damage against that item.

2. A more generalized (the path you currently have) of having a 'group' of identical items. What you should *not* do with this path is constantly modify your 'on hand' quantity, the 'on hand' should be a calculation based on what is currently loaned out vs the total number of that item you have. damage/maintenance etc would be more generalized in this model and be related to the item type rather than the individual item.

[orange]

Ufriendly,

I agree with June and rpeare re QuantityOnHand. Here is a great reference for Inventory and Stocktaking.

Most developers would use the transaction approach to determining current stockOnHand.
That is
CurrentStockOnHand = LastVerifiedStockTake -ItemsSold +Items Purchased

You perform the calculation when required.
LastVerifiedStockTake is a manual StockTaking to measure exactly what is there. This will be a real count -- no damaged articles, stolen /misplaced items....

There are several posts in various forums related to inventory and stock taking. Use Google, do a little research to investigate what others have done.
Good luck.

[Ufriendly]

Ok guys thanks. I see what you mean. That determination of what is on hand (available to lend) would be a query? I'll also check link in a bit, if answer is there. Thanks again all!!!