stock & inventory

[simon]

Hi

I know this has been done to death....but obviously at some point in the dim
and distant past!

I wish to program a retail database for my shop, I have trawled many forums
and I only manage to see "search for inventory, this has been done many
times before...", without ever actually coming across the method ;(
Could some kind person just give an overview of the 'correct' way to achieve
this please?

I will have large-item stock which will have to be individually identified
by serial number (ie TV, DVD etc) and stock which will not have to be
identified (ie accessories, video tapes etc) - I need to be able to identify
by serial number, the stock large-stock I have remaining in the shop (ie
1x14" TV serial 1000012, 1x14" TV serial 1000013 etc.) and only the total
quantity of each accessory remaining (ie 10 x headphones, 100 x video tapes)

Would I need one table for large stock and one for accessories? and if so,
can I have a subform which calls from 2 tables?

As far as the invoicing form will be concerned, I have a form which acts as
an invoice (customer details, inv number, date etc..) and a subform (invoice
detail items) linked by invoice_number. My main question is how to run an
update query to act upon only the detail in the subform of the invoice when
say the print invoice button is clicked (my theory being once the invoice is
printed, the goods will leave the shop and stock will be reduced
accordingly). I see this as a possibility to add a 'sold' checkbox field in
the large-stock table (then I could search the table for only unchecked
items, signifying they still remain in stock).
As I said earlier, it seems my main problem seems to be having the update
query affect only the items in the current invoice subform.

This seems a tall order to me, but hopefully quite a straight-forward issue
to you guys! I hope someone has the time to give me this overview and
appreciate any help. I have several access reference books and obviously
the net to call upon, so I'm not looking for a complete solution - just a
point in the right direction from someone who knows ;)

Many thanks.

[Allen Browne]

Hi Simon

It is not possible to develop a one-size-fits-all inventory, as there are so
many variations. Your case involves individual serial numbers. Others
require FIFO (first-in first-out) e.g. fresh food. Others require tracking
different purchase prices for different components (e.g. RAM prices change
with the market). It is therefore something that you must develop to fit
your needs.

For a general introduction to the topic, see:
Inventory Control - Quantity On Hand
at:
http://allenbrowne.com/AppInventory.html
This article encourages you to calculate the quantity on hand when you need
it rather than store the quantity. It's not always practical to do that, but
if you start with that approach, it will lead you in a useful direction. The
article also identifies the issues you need to consider if you do try to
subtract the quantity from a stored number at the time of sale.

I would suggest that all products go into one table. It will have a
SerialNmber field (Text type), not required (as some things don't have
them). You may want to warn the user if Brand+Model+Serial is not unique. It
may not be, e.g. if a part is returned and resold.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"simon" <st********************@hotmail.c%20om> wrote in message
news:94***************@news-binary.blueyonder.co.uk...

I know this has been done to death....but obviously at some point in the dim and distant past!

I wish to program a retail database for my shop, I have trawled many forums and I only manage to see "search for inventory, this has been done many
times before...", without ever actually coming across the method ;(
Could some kind person just give an overview of the 'correct' way to achieve this please?

I will have large-item stock which will have to be individually identified
by serial number (ie TV, DVD etc) and stock which will not have to be
identified (ie accessories, video tapes etc) - I need to be able to identify by serial number, the stock large-stock I have remaining in the shop (ie
1x14" TV serial 1000012, 1x14" TV serial 1000013 etc.) and only the total
quantity of each accessory remaining (ie 10 x headphones, 100 x video tapes)
Would I need one table for large stock and one for accessories? and if so,
can I have a subform which calls from 2 tables?

As far as the invoicing form will be concerned, I have a form which acts as an invoice (customer details, inv number, date etc..) and a subform (invoice detail items) linked by invoice_number. My main question is how to run an
update query to act upon only the detail in the subform of the invoice when say the print invoice button is clicked (my theory being once the invoice is printed, the goods will leave the shop and stock will be reduced
accordingly). I see this as a possibility to add a 'sold' checkbox field in the large-stock table (then I could search the table for only unchecked
items, signifying they still remain in stock).
As I said earlier, it seems my main problem seems to be having the update
query affect only the items in the current invoice subform.

This seems a tall order to me, but hopefully quite a straight-forward issue to you guys! I hope someone has the time to give me this overview and
appreciate any help. I have several access reference books and obviously
the net to call upon, so I'm not looking for a complete solution - just a
point in the right direction from someone who knows ;)

Many thanks.

[Bob Quintal]

"Allen Browne" <ab***************@bigpond.net.au> wrote in
news:Qp*******************@news-server.bigpond.net.au:

I would suggest that all products go into one table. It will
have a SerialNmber field (Text type), not required (as some
things don't have them). You may want to warn the user if
Brand+Model+Serial is not unique. It may not be, e.g. if a
part is returned and resold.

I agree with Allen. You can also code business rules into your
forms that says IF serial number is not null THEN quantity = 1.
and IF quantity >1 then serialNumber is null. And its always safer
to calculate inventory from the sum of receipts minus the sum of
sales plus other disbursements) when you need it (in a query, form
or report) than trying to store the value in a table somewhere.

Bob Q

[StumpY]

Cheers for the pointers!

"Allen Browne" <ab***************@bigpond.net.au> wrote in message
news:Qp*******************@news-server.bigpond.net.au...

Hi Simon

It is not possible to develop a one-size-fits-all inventory, as there are so many variations. Your case involves individual serial numbers. Others
require FIFO (first-in first-out) e.g. fresh food. Others require tracking
different purchase prices for different components (e.g. RAM prices change
with the market). It is therefore something that you must develop to fit
your needs.

For a general introduction to the topic, see:
Inventory Control - Quantity On Hand
at:
http://allenbrowne.com/AppInventory.html
This article encourages you to calculate the quantity on hand when you need it rather than store the quantity. It's not always practical to do that, but if you start with that approach, it will lead you in a useful direction. The article also identifies the issues you need to consider if you do try to
subtract the quantity from a stored number at the time of sale.

I would suggest that all products go into one table. It will have a
SerialNmber field (Text type), not required (as some things don't have
them). You may want to warn the user if Brand+Model+Serial is not unique. It may not be, e.g. if a part is returned and resold.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"simon" <st********************@hotmail.c%20om> wrote in message
news:94***************@news-binary.blueyonder.co.uk...

I know this has been done to death....but obviously at some point in the dim

and distant past!

I wish to program a retail database for my shop, I have trawled many

forums

and I only manage to see "search for inventory, this has been done many
times before...", without ever actually coming across the method ;(
Could some kind person just give an overview of the 'correct' way to

achieve

this please?

I will have large-item stock which will have to be individually identified by serial number (ie TV, DVD etc) and stock which will not have to be
identified (ie accessories, video tapes etc) - I need to be able to

identify

by serial number, the stock large-stock I have remaining in the shop (ie
1x14" TV serial 1000012, 1x14" TV serial 1000013 etc.) and only the total quantity of each accessory remaining (ie 10 x headphones, 100 x video

tapes)

Would I need one table for large stock and one for accessories? and if so, can I have a subform which calls from 2 tables?

As far as the invoicing form will be concerned, I have a form which acts

as

an invoice (customer details, inv number, date etc..) and a subform

(invoice

detail items) linked by invoice_number. My main question is how to run an update query to act upon only the detail in the subform of the invoice

when

say the print invoice button is clicked (my theory being once the invoice is

printed, the goods will leave the shop and stock will be reduced
accordingly). I see this as a possibility to add a 'sold' checkbox

field in

the large-stock table (then I could search the table for only unchecked
items, signifying they still remain in stock).
As I said earlier, it seems my main problem seems to be having the

update query affect only the items in the current invoice subform.

This seems a tall order to me, but hopefully quite a straight-forward

issue

to you guys! I hope someone has the time to give me this overview and
appreciate any help. I have several access reference books and obviously the net to call upon, so I'm not looking for a complete solution - just a point in the right direction from someone who knows ;)

Many thanks.

[Eric]

Hi Guys,

I too am just starting to make an inventory database and have perused
many inventory-related threads. For simplicity, I have settled on
trying to only use 2 examples for the databse--Allen's article (thanks
for this!) and Microsoft's own invoice templates.

The MS templates use a transactions table in which to lump product
orders and acquisitions. This kind of appeals to me as my products
will not only be ordered, sold, and acquired, but also used by me.
However, I also want to incorporate the stock take table and
associated functionality of computing quantity on hand at a particular
time.

My other problem is that I must group the products (items) by category
(demo). For example, 1 demo may involve certain items, and the same
items can be used in other demos. Thus, I'm thinking about tracking
inventory by the combination of items and demo as opposed to just by
item. I'm just not exactly sure where and how to start. Any
additional advice/assistance would be greatly appreciated.

Thanks,
Eric

[Allen Browne]

Combining the ins and outs (acquisitions and sales) into a single table can
be a good idea, as it simplifies the calculation of the quantity on hand.
There is no problem using this approach with a StockTake value so that you
start with a known quantity of the product, and add/subtract the
transactions since that date.

If there is a one to many relationship between category and product, the
category does not have to come into the calculation of the quantity on hand.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"Eric" <ee*******@yahoo.com> wrote in message
news:5f**************************@posting.google.c om...

Hi Guys,

I too am just starting to make an inventory database and have perused
many inventory-related threads. For simplicity, I have settled on
trying to only use 2 examples for the databse--Allen's article (thanks
for this!) and Microsoft's own invoice templates.

The MS templates use a transactions table in which to lump product
orders and acquisitions. This kind of appeals to me as my products
will not only be ordered, sold, and acquired, but also used by me.
However, I also want to incorporate the stock take table and
associated functionality of computing quantity on hand at a particular
time.

My other problem is that I must group the products (items) by category
(demo). For example, 1 demo may involve certain items, and the same
items can be used in other demos. Thus, I'm thinking about tracking
inventory by the combination of items and demo as opposed to just by
item. I'm just not exactly sure where and how to start. Any
additional advice/assistance would be greatly appreciated.

Thanks,
Eric

[Eric]

Thanks Allen.

[Tony Toews]

"Allen Browne" <ab***************@bigpond.net.au> wrote:

Combining the ins and outs (acquisitions and sales) into a single table can
be a good idea, as it simplifies the calculation of the quantity on hand.
There is no problem using this approach with a StockTake value so that you
start with a known quantity of the product, and add/subtract the
transactions since that date.

I'd certainly agree with this approach as well. For one client we have nine
different transaction types in the transaction table. Including Qty on Req, Qty on
Order, Qty Received, Qty Consumed (it's welding shop), Qty Shipped, Adjustment and
several more I forget.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm