Updating Inventory Count

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Roxanne - 26 Apr 2006 02:09 GMT

I have a simple access inventory database. I have a table with employee
names and a table with products (part numbers, quantity on hand, description).

I want to update the quantity on hand of parts. Basically, when one of our
technicians uses a part in stock at a customer site, I want to show which
customer got the part, the date it was used, and then when the tech submits
that information, I want it to automatically deduct the number of parts used
from the quantity on hand in the products table.

How do I do this?
Thank you,
Roxanne

Reply to this Message

Allen Browne - 26 Apr 2006 02:57 GMT

Presumably you have some kind of table for a WorkOrder, and a
WorkOrderDetail table that lists the parts the technician used in each job.
You also need to know when parts are received, so you will have an
Acquisition table and an AcquisitionDetail table that lists the parts
received.

The best solution is to then ask Access to calculate the quantity-on-hand at
any date by summing the acquisitions of the part and subtracting the parts
used. In practice, you also need to perform a periodic stocktake to deal
with real-world issues, so the calculation boils down to:
Quantity at last stocktake + Acquisitions since then - Usage since then

For detail on how to set up such as calculation, see:
Inventory Control: Quantity on Hand
at:
http://allenbrowne.com/AppInventory.html

It is possible to store the quantity in the Product table as you suggest,
using the events of the form to increment/decremet it. But there are so many
issues to consider if you take that approach. For example, if you use the
AfterUpdate event of the form to subtract the Quantity of that Product in
the Product table (by executing an Update query statement):
- What happens if the user goes back and changes the quantity from 100 to
10. Does your code then subtract another 10, so it is now wrong by 100?
- What happens if the user goes back and changes the Product? Do you restore
the quantity of the previous product?
- What happens when the user deletes a row from the work order detail? Do
you reinstate the quantity of the product?
- What about if the user selects several rows? Are you able to determine
which rows were selected and deleted, and restore them all?
- What happens if there is a failed write, and the quantity does not
subtract one day? Will your program just be wrong from that time onwards?
Will it gradually become more and more wrong over time?

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I have a simple access inventory database. I have a table with employee
> names and a table with products (part numbers, quantity on hand,
[quoted text clipped - 12 lines]
> Thank you,
> Roxanne

Reply to this Message

Roxanne - 26 Apr 2006 15:00 GMT

Allen,

I only have one other table in this database and that is title Parts Used (I
have no acquisitioin table at all). This table contains the Customer part
was used at, employee that used the part, date part was used and quantity
used. It has an autonumber order ID.

For the most, we don't receive in parts on an ongoing basis. Occassionally
here and there we will restock a part, but for the most part these are
discontinued parts that are rarely used.

I need the database to show accurate inventory levels for ANYONE in the
company that needs to see the quantity of any of the parts we have on hand
when they open the database. To have all the users calculate (or try to
calculate) quantity on hand is not feasible since many of them have minimal
knowledge of Access databases.

So there is no way for me to have the Parts Used table update the Products
table after a part is used?

The users, in this case, will not be deleting rows in the tables - they will
only see the form for Parts Used and the form that shows all the inventory
that I create. They will also not be changing the products. Once a product
has any changes (like part # and such), they won't be doing anything with the
tables; I would be implementing those changes. Their form will have drop
down boxes to select a product - the only typing they will do is type in the
quantity and save the record (or submit button if I can figure out how to
make one on the form). I also would like to be able to delete parts out
after stock is used up and no more come in.

Thank you for your help - it is greatly appreciated!
Roxanne

> Presumably you have some kind of table for a WorkOrder, and a
> WorkOrderDetail table that lists the parts the technician used in each job.
[quoted text clipped - 46 lines]
> > Thank you,
> > Roxanne

Reply to this Message

Allen Browne - 26 Apr 2006 16:43 GMT

You could execute an Update query statement in the AfterInsert event of the
form where the entries are created.

The code for the event procedure would look something like this:
Private Sub Form_AfterInsert()
Dim strSql As String
strSql = "UPDATE [Products] SET [Quantity] = [Quantity] - " & _
Me.[Quantity] & " WHERE [PartID] = " & Me.[PartID] & ";"
dbEngine(0)(0).Execute strSql, dbFailOnError
End Sub

For me, the assumptions you are making are horrifying, but that's how you
can do what you asked for. Perhaps you should also consider setting the
form's AllowDeletions and AllowEdits properties to No, so users cannot
change or remove any existing records (or even correct an entry they made a
mistake with.)

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Allen,
>
[quoted text clipped - 93 lines]
>> > Thank you,
>> > Roxanne

Reply to this Message

Grover Park George - 26 Apr 2006 23:38 GMT

Allen, I think you are being too kind, actually.

Calculating inventory on hand like this is a disaster waiting to happen. For
one thing, there will be no audit trail against which to recover previous
inventory levels if an entry error occurs--and we all know that's going to
happen sooner or later. I really think the solution is a valid inventory
module.

Signature

George Hepworth

> You could execute an Update query statement in the AfterInsert event of the
> form where the entries are created.
[quoted text clipped - 110 lines]
> >> > Thank you,
> >> > Roxanne

Reply to this Message

mnature - 26 Apr 2006 16:51 GMT

Microsoft has an Inventory Management Database template that is available for
download:

http://office.microsoft.com/en-us/templates/TC010184581033.aspx?CategoryID=CT011
366681033

If you look at the way it is set up, it uses an inventory transaction to
record what is happening to inventory items. These transactions are then
used in calculations (that are performed by the database) to show units on
hand, and units on order. You seem to be treating your database as a
spreadsheet, and this means you are doing a lot of work which the database
can do for you.

> I have a simple access inventory database. I have a table with employee
> names and a table with products (part numbers, quantity on hand, description).
[quoted text clipped - 8 lines]
> Thank you,
> Roxanne

Reply to this Message

Roxanne - 27 Apr 2006 18:19 GMT

Thank you all for your information. I agree, the way they wanted it set up
was disasterous and they have little working knowledge of databases in this
sense. I will keep the information you gave me as it will be valuable for my
future databases. I no longer have to worry about this database as I no
longer work for the company.

Thanks again!
Roxanne

> Microsoft has an Inventory Management Database template that is available for
> download:
[quoted text clipped - 20 lines]
> > Thank you,
> > Roxanne

Reply to this Message