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

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

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
    ... you can cut out all the stuff about taking inventory and making ... I was told to create a simple order form that will deduct stock from the ... "Tom Ellison" wrote: ... 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)