Update Records In A Table

[Guest]

Hi

Please can anyone help me with the following query:-

I am trying to create an inventory application in Access 2007.

I have created the following tables:-
- Stock table (where information is entered and stored on each shoe
when it
comes in)
- Warehouse table (where stock is kept, if transferring from one shop
to another)
- Shop table (eg. Nelson, where information is stored on each shoe)
- Transactions table (where information is stored on each
transaction)
- Sales table (where information is stored when we sell a shoe)

After I enter the shoe information into the stock table when we have
recieved an order. I would like to press a button to add the records to that
stock table and add/update the records to the transactions table and the
shop/warehouse table, (to whatever location its going to ).

When we have sold a shoe, I would like to enter the details into the sales
table. When I press a button, the records will be added to the sales table
and the transaction table. Then in turn the quantity of stock will be updated
from where we have sold the shoe, (eg Nelson table).

When we transfer a shoe, it goes from a shop to the warehouse on a credit
note then it is invoiced to another shop. When this happens I would like the
relevant shops stock to be updated and the transaction table.

I need your precious advice on how to automatically update the stock?
Will I need to create update queries for each situation? Or create a macro
which contains an update query and VBA?

I hope my explanation is clear to you.
Please do not hesitate to ask me any questions if you need to clarify before
being able to answer my question.

Thanks for all your help,
Louisa Holt.

 

[Larry Daugherty]

Hi Louisa,

Your explanation is clear enough for me to have a suspicion that your
whole design is badly flawed. I'll go over the process of arriving at
the design of an application and the tables therein in very broad
strokes. At first it will probably seem familiar to you but those
things that I mention that seem foreign or irrelevant to you are, in
fact, crucial. Given your name and the business name, I believe you
have an acute interest here.

The whole thing begins with the recognition that there's a problem and
that something should be done about it. That could be a dangerous
issue or it could be an unexploited opportunity. Whatever it is, that
information should be organized into a Problem Statement. The Problem
statement should list everything that is to be included for address
and similar or closely related things that won't be considered.
That's the beginning.

Next comes the Solution Statement, usually entitled the Product
Specification. That expresses everything that's listed in the Problem
Statement as something to be solved as already being solved. Further,
it describes at the 10,000 foot level, how each problem was solved.

Next comes the Functional Specification. Each problem is separated
from all of the others and the functions that will be employed to
solve it will be expressed. This is detailed at a level much nearer
the ground.

There may be Implementation Plans or specifications and other
documentation as required by the powers that be. However, those first
three documents are really the heart of a successful product - whether
it be an automobile, a building, an army, or a software based
application. The size and complexity of the target system may
determine the size and complexity of the documentation.

With the first three specifications in hand you can begin to analyze
your existing process and the target system you will design that will
support it. In that analysis process, you will eventually be able to
isolate the entities in play in your system. All entities of a given
type within your system will be recorded in a single table. In your
system, I"do say that "shoes" are a big item. However, I don't think
that "shoes" will actually be an entity. You might also sell shoe
laces, stockings and various other things. So you have the "Stock"
table. Great. Everything that you purchase for sale belongs in that
stock table. Shoes, sox, garters, etc. are simply attributes of some
of the stock. Here comes a curve: The location of a particular item
of stock is an attribute of the stock, not of the location. In short,
"Warehouse" is a location and not a separate entity type. The entity
type in this case is "Location". I suggest that you'd want to have a
lookup table (not the dreaded Lookup Field!) that lists all locations.
In my simple mind it would seem that the Stores would just be
locations unless there are business considerations that dictate
otherwise. I think your "Shop" table refers to "Vendors", not sure of
that. "Transaction" is such an over-used term that it's almost
useless to isolate to a specific thing. If you are storing every
movement of every item of stock then your "Transaction" table will be
huge! You'd be making Access behave more like its big brother SQL
Server than a little desktop RDBMS. Without knowing more ... ? Odds
are good that you don't need a transaction table.

The long and the short of it is that I don't think a single query is
going to solve all of your issues. I may have misread things.

As well as this newsgroup I always recommend:
microsoft.public.access.gettingstarted
microsoft.public.access.taablesdesign

There are Access groups for all of the major parts, even

microsoft.public.access.queries

I also recommend that anyone who hasn't yet done so, visit
www.mvps.org/access It's one of the best Access resources gong.
There are others. Many of the MVPs also have web sites where they
dispense wisdom.

HTH