deduct stock from a table when order placed in another form for or

[Guest]

Hi anyone

Tried posting this query earlier but got thrown out so not sure if
posted..so re-submitting:

I have a query on how can you do the following:

I have a table called stock which stores the totalStock of items. I then
have an order form with a subform orderline, customer place order of items, I
want the stock table to decrement the stockQuantity when cust orders items.

eg. cust place order for 3 shoes, stock table has 5 shoes available, should
now show 2 shoes available.

Maybe really simple for someone out there, should also be for me, but cant
think how to do it... please help

thanks in advance.

 

[Tom Ellison]

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

 

[Guest]

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]

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

 

[Guest]

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]

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

 

[Guest]

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]

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

 

[Guest]

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]

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