Re: deduct stock from a table when order placed in another form fo

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Dear Saj:

Well, you can cut out all the stuff about taking inventory and making
adjustments. Just assume you have 0 on hand to start with and post a
receipt, then carry forward the sales to reduce inventory. What is left is
the easiest way known to implement this. And, it's not going to be very
complicated at all.

A simple UNION query can combine the INs with the OUTs and then you sum them
by each inventory item. It doesn't get better than that!

Can you picture just this fragment of a system?

Tom Ellison


"Saj" <Saj@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:ECB148D1-4B80-410D-8FCE-FC8004DE8DD5@xxxxxxxxxxxxxxxx
Hmmm.... I think I should just say... I am a student trying to do an
access
project so this is a not a real company I am creating the database for.

I was told to create a simple order form that will deduct stock from the
stock table when I enter the quantity in the order subform.

I think all you have said is a little too complicated for what I need and
also I have no idea of how a warehouse inventory system works, just need
to
show when order form has quantity in stock table deduct it from the stock
availability.

The information you have provided is great.. and much appreciated... but
think it is too much for what I need. Isnt there a simpler way or is this
the simplest way of doing it... which I think you did actually say it
was...
but since it is not for a real system is there a work around for a simpler
method?

Thanks

"Tom Ellison" wrote:

Dear Saj:

Glad to see you're on-board with the need to find an alternative.

So, we start with analysis. Here's what typically happens.

1. When a company starts an inventory system, the count everything. They
count it again periodically. We will record these as a starting point.
That's what they are! The count can be of one item, a few items, or all
items. When all items are counted, everything not counted is zero. When
only a few are counted, other items are unaffected. So, when recording a
count, it is essential to differentiate between a partial and complete
count. Things not included in a "complete" count are ZERO! Things not
included in a partial count are unaffected. This is obviously a big and
very important difference. I emphasize it by repeating it here.

2. You then have a record of goods received since the date things were
counted. This adds to the inventory of the item received.

3. You have shipments of items to be subtracted.

4. There can, and probably will be other changes (adjustments).
Something
is damaged in the warehouse and discarded. It needs to be recorded and
entered into the database. Something is returned to the manufacturer or
distributor or transfered to another warehouse. A box is opened but
doesn't
contain what was expected. There is a long list of minor tragedies going
on
at the warehouse all the time. If you don't believe me, just talk to the
warehouse manager (you really should talk to that person, don't you
know?)

A query will be performed to calculate the current inventory of any or
all
items, or to find that value as of some date (or even date/time! You
need
to know what will be required in this respect before building anything.
Now
you get to talk to the sales manager and find out what he needs too!
You're
going to be meeting a lot of interesting people!)

The math is simple. Add and subtract. What is left?

There is certain information that must be kept about every "transaction"
to
inventory. Some common and unique stock number (might even be
Manufacturer
and the Manufacturer's stock number together). The quantity in or out.
The
date or date/time it happened. Perhaps the warehouse or location where
it
is found. I've even needed to keep track of which building, which room,
and
which "square" (a thing painted on the floor between the aisles where the
forklifts roam) where the item is, or at least belongs. You see, when
you
need to ship one, you also need to find it! The computer can be a help
in
doing that as well, and you'll be a superman hero for doing it (or the
goat
when people screw up the data entry and it isn't there. Don't ever
forget
the GIGO factor! If the people using your superb software aren't
trained,
or just don't care, then it will be your fault, be quite sure. Remember,
nobody around understands the nerds who do this, that is, us!)

Now, thee are technical details, for sure. There's the job of modeling
the
data next. Don't get ahead of yourself. You can't model the data until
you
understand the business. Tour the warehouse. Talk to the key people.
Get
control of the details of the business. This is what you're going to
build
on. It's not an academic exercise, its your career. Or is it? Anyway,
I'm
sure of one thing. It's your ass!

Tom Ellison


"Saj" <Saj@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:40FAFB8E-F8EF-4B98-8525-08607E9762AC@xxxxxxxxxxxxxxxx
Hi Tom

I can see this will cause problems so wont do this way now. But how
should
I do it?

You said not to have a quantity field in the stock table, but then
where
do
I store the total stock quantity for individual items.

I have a table for stock whis contains details of items etc, a table
for
an
order and table for an orderline. When I have completed an order, so
existing out of the order form with subform of orderline, I believe at
this
stage is where i need the calculation to take place.

I need to check each orderline and if the stockno field is not null
then
to
look at the quantity field and deduct that from existing stock. But
where
is
the stock quantity stored then? where does it look? and how?

I have in the stock form a field where the user was able to enter a
number
and click on a button called 'Add Stock' which added that value to the
quantity field for the stock. If I take the quantity field out, then
how
do
I know how much stock there is?

All confused now, I take it you now know I dont really have much
experience
in this, would appreciate your help.

Thanks

"Tom Ellison" wrote:

Dear Saj:

Now, with that decision done, let me ask this. If someone corrects an
order
so the quantity changes from, say, 5 to 8, how do you expect to handle
that
in the update? How will you fix things if they change the part
ordered?
How will you handle deletion of a line from an order, or the whole
order?
There are HUGE challenges to doing this the bad way!!!

Once you made the decision about WHEN, the rest of the mess it
generates
becomes clear to me. It's always like this. I urge you to change
your
mind
on how to do this. Can you now see why?

Tom Ellison


"Saj" <Saj@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:208E8307-5890-4BBC-A491-2B339F5DEB3C@xxxxxxxxxxxxxxxx
Hi again

I will try the query and see how I get on with it, but I want to
decrement
stock at time of order entered.

Thanks for getting back to me


"Tom Ellison" wrote:

Dear Saj:

My meaning was:

1. Do not have a field in the table called quantity

2. Do not add or subtract quantities anywhere but in a query that
adds
up
the quantity on hand.

I believe you probably do not know what I mean, or that what I am
suggesting
sounds complex and difficult.

It is not. The way you are suggesting to do it is, in the long
run,
complex
and difficult. Unless you take extreme care and use great
expertise,
it
will fail to give accurate results.

I do not know how to help you, truly. If you're going to do it the
hard
way, then I must ask a question. WHEN do you want your inventory
relieved
for a sale? As soon as the order is entered? When the order is
shipped?
At some other time?

You see, the question would not be first WHERE to put this, but
WHEN
is
it
to be done. The answer to that question determines the WHERE. One
of
the
difficulties of doing this the hard way will also be to ensure it
is
only
subtracted once! This makes the WHEN even more important.

The answer to WHEN should be in your operational chart of how this
software
should function. This chart will show the progression of events,
from
giving a quote, taking the order, packaging the order, shipping the
order,
getting confirmation it has arrived, billing, collections, etc.
Each
of
these events may have a corresponding computer activity associated.
So
the
WHEN tells you the WHERE in terms of this organization.

Does that make some sense?

Tom Ellison


"Saj" <Saj@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:DD6CEED0-52EC-446E-92BE-6AD849330AE2@xxxxxxxxxxxxxxxx
Thanks Tom

But not sure how you mean, not an access savvy person me, having
to
use
so
now stuck.

I have a table called stock and a field in there called quantity
which
is
has stock added to it from another calculated field on the stock
form.

When producing the order, I enter in the subform the quantity
items
purchased.

How do I put a query...or where... to do the neccessary
calculation
to
deduct the stock from the stock table field.

would appreciate some help... if possible

"Tom Ellison" wrote:

Dear Saj:

I posted the following reply to your former post, which shows up
fine
for
me:

Dear Saj:

May I please offer some advice. Don't do it!

The quantity on hand can be calculated. Keeping a calculated
value
stored
in a table means every little thing that could affect that total
must
also
update this total for the change it makes. I believe you will
find
that
doing this will be very daunting.

Consider, for example, what you must do if the customer reports
a
shortage
in a shipment. If you believe him, then you must change the
quantity
shipped. This means you would have to increase the quantity on
hand.
But,
if it was lost or damaged in shipment you would not.

When I write inventory, I have "starting points" for quantity on
hand.
Whenever a certain stock item is counted in any warehouse, this
count
is
entered in a table of transactions to inventory.

Various queries to tables of receipts and shipments of inventory
create
uniform "inventory transaction records". These are added up and
summed
with
the most recent count to find the quantity on hand as of any
date.
This
works without strain or failure to account for all changes.

Trying to keep this in a table is a violation of principles for
good
database construction, principly because it is a lot of
programming
to
make
sure everything is summed into this one total. If your system
has,
or
ever
comes to have multiple locations for the inventory, this becomes
even
more
important.

Tom Ellison


"Saj" <Saj@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:48DC73CA-DEF4-46C0-8533-D211D5E1A221@xxxxxxxxxxxxxxxx
Hi anyone

Can anyone help me with the following:

I have a table called stock which has a field for stock called
'stockQuantity' which basically holds the value of how much
stock
is
available.
When a customer places an order I have an order form with
subform
orderline,
in the subform I enter the stockno. then the 'quantity' they
want.
I want the table with the stock to update on what is ordered,
so
if
customer
orders e.g 3 pairs of shoes and 5 were in stock, the stock
table
will
update
by decrementing the stock level to 2.

May be a simple query for someone out there, but I cant think
at
the
moment
and need some urgent help.... thanks in advance.



.

Relevant Pages

  • Re: Table Design Problem
    ... carry out the job and return excess stock to the store. ... The kind of inventory tracking system you seem to ... > The Receiving module will track your receiving transactions. ... >> correctly model the materials? ...
    (microsoft.public.access.tablesdbdesign)
  • Re: deduct stock from a table when order placed in another form fo
    ... I was told to create a simple order form that will deduct stock from the ... "Tom Ellison" wrote: ... When a company starts an inventory system, ... is damaged in the warehouse and discarded. ...
    (microsoft.public.access.queries)
  • Re: create a small stock control and sales database
    ... "Inventory control" sample database doesn't really "fit the bill". ... My comments also apply to the Northwind Traders database. ... Description, type of container, weight, Price, and Purchase price.This table will rarely be changed, the "changing tables" are stock in and stock out. ... But you might also want to copy it to a new field in [Product sold] to reflect what the unit price was at the time of a specific sale. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Inventory and Stock Monitoring in Project
    ... Jan De Messemaeker for your swift response. ... stock is used changing the information presented within the Inventory ... Inventory software places information in an SQL database which Project can ... VBA macros to keep track of stock. ...
    (microsoft.public.project)
  • Re: deduct stock from a table when order placed in another form fo
    ... When a company starts an inventory system, ... distributor or transfered to another warehouse. ... Some common and unique stock number (might even be Manufacturer ... "Tom Ellison" wrote: ...
    (microsoft.public.access.queries)