Inventory Control Problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I'm trying to create an inventory control setup on my order entry program. It's loosely based on the Inventory Management database that Microsoft has a template of online. I have a Units on Hand count and a Units on Order count

UnitsOnOrder = Sum(nz([UnitsOrdered]) -nz ([UnitsReceived])
UnitsOnHand = Sum(nz([UnitsReceived]) -nz ([UnitsSold])

My problem is that we often don't get the exact quantity we ordered when stock comes in. Often it is more than what we order, once in a while it might be less. This is what throws my counts off, because if I order 100 of something and receive 110, it tells me I have -10 on order. I'm not good at VBA and I've tried an If statement and it didn't work out. If someone has a solution to this I would greatly appreciate any help! Thanks in advance

Jay
 
I've run into this problem before too. What I've done is to include an
extra field to account for the extras/shortage. Something like
UnitsReceivedExtra. Then your UnitsOnOrder will calculate correctly and the
UnitsOnHand can then be written as
sum(nz([UnitsReceived])+nz([UnitsReceivedExtra])-nz([UnitsSold]))

Kelvin

[email protected] said:
Hi,

I'm trying to create an inventory control setup on my order entry program.
Click to expand...
It's loosely based on the Inventory Management database that Microsoft has a
template of online. I have a Units on Hand count and a Units on Order
count.
UnitsOnOrder = Sum(nz([UnitsOrdered]) -nz ([UnitsReceived]))
UnitsOnHand = Sum(nz([UnitsReceived]) -nz ([UnitsSold]))

My problem is that we often don't get the exact quantity we ordered when
Click to expand...
stock comes in. Often it is more than what we order, once in a while it
might be less. This is what throws my counts off, because if I order 100 of
something and receive 110, it tells me I have -10 on order. I'm not good at
VBA and I've tried an If statement and it didn't work out. If someone has a
solution to this I would greatly appreciate any help! Thanks in advance!
 
You really have two separate issues.

Inventory control - ie how many you actually have on hand - should always
be based on the real number delivered (and sold), regardless of how many are
ordered. Even if you return excess delivered items back to the supplier,
this is just another inventory movement.

Order control - here you reconcile how many you have on order, compared with
how many delivered. This has nothing to do with inventory, but only how you
deal with the supply issues - like telling the supplier what the shortfalls
etc are. What you do to close an order after its 'complete' with some items
missing/over is up to your busines rules.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
[email protected] said:
Hi,

I'm trying to create an inventory control setup on my order entry program.
Click to expand...
It's loosely based on the Inventory Management database that Microsoft has a
template of online. I have a Units on Hand count and a Units on Order
count.
UnitsOnOrder = Sum(nz([UnitsOrdered]) -nz ([UnitsReceived]))
UnitsOnHand = Sum(nz([UnitsReceived]) -nz ([UnitsSold]))

My problem is that we often don't get the exact quantity we ordered when
Click to expand...
stock comes in. Often it is more than what we order, once in a while it
might be less. This is what throws my counts off, because if I order 100 of
something and receive 110, it tells me I have -10 on order. I'm not good at
VBA and I've tried an If statement and it didn't work out. If someone has a
solution to this I would greatly appreciate any help! Thanks in advance!
 
Back
Top