Inventory database

I am looking to create a database which will keep track of a warehouse
inventory as well as inventory on sales trucks and track customer sales. Want
to link tables so that totals are linked when inventory is transferred to
trucks the warehouse inventory reflects this transfer. Also truck inventory
to reflect custormer sales. Any suggestions as to what type of tables, forms,
querys and relationships I should create would be helpfull. Thank you in
advance for your help.

On Mon, 06 Oct 2008 16:12:09 GMT, "pkfloyd" u46723@uwe wrote:

Assuming you are using Access 2007, there is an Inventory template.
File New Business. For older versions you may also find templates
at office.microsoft.com.
"Totals are linked"? That sounds like you want to store totals in the
db. If these are totals that can be calculated, it is likely better to
calculate them on the fly in a query.

-Tom.
Microsoft Access MVP


I am looking to create a database which will keep track of a warehouse
inventory as well as inventory on sales trucks and track customer sales. Want
to link tables so that totals are linked when inventory is transferred to
trucks the warehouse inventory reflects this transfer. Also truck inventory
to reflect custormer sales. Any suggestions as to what type of tables, forms,
querys and relationships I should create would be helpfull. Thank you in
advance for your help.

You are asking about a transaction based inventory system. The Northwind
example does not do this. Below is a summary on this that I previously
wrote.

Sincerely,

Fred

Most people would define inventory tracking is an information system that
always “knows� the current inventory. Automated inventory systems handle
this by automatically adjusting the inventory numbers for the various
happenings which increase or decrease inventory. For those that don’t
already have this in place, this is a 10 times larger job than they think it
is. So, for many of them, a “less perfect� system that doesn’t fully do
this would be better for them. For example, where you update the inventory
figure by simply entering in then new total count for that item. If not,
read on!

The central “information� system can be paper/card or electronic.
Electronic systems can be of various types such as text documents,
spreadsheets, but most are database based. For this example

Here are the main steps.....always read the later ones before you
start.....where you are headed should always influence what you do.

1. Create unique “names� / identifiers for all items that you want to
track. The most common example of this is your company’s part number for
the system, following all of the rules for a good part numbering system. An
alternative is somebody else’s part number combined with their name. For
wording in this writing, I will presume you’re using “part number� This
includes defining all units of measure. . E.G. does a part number for rope
mean 1 foot of that rope, one 200’ spool of that rope etc.

2. Define your “sphere� of what will be considered to be “in� your
inventory. Is this your stockroom, your whole building, your whole
company, a single service truck?

3. List all of the current ways in your company that inventory of an item
in your “sphere� could be modified*. The 4 main categories of this a




Income
Outgo (sales, consumption etc.)
Creation*
Destruction*

* E.G. If, by a manufacturing act, you use part a and part b to build part
c, that act “destroys� A & B and “creates� C

The results must be

4. Set up and implement procedures, rules, practices to make sure that
every instance of every item #3 will get recorded as a transaction for each
part number involved, and that happenings not under #3 are not recorded.*
Usually, this requires defining a mental or physical around your “sphere�.
For example, if your “sphere� is (only) your stockroom and your production
area, then your procedures must make sure that moving an item from your
stockroom to your production area is never recorded as a transaction, and
moving an item from either of those two areas to elsewhere is always recorded
as a transaction.

5. Get / Set up a data system which has a current inventory quantity for
each part number, and which supports receiving each recorded transaction and
making the appropriate modifications to inventory quantities for each
recorded transaction.*

6. Make sure each transaction gets processed in the data system.*


Note: A portion of the items under #3 (and processed under #4, #5 & #6)
might be, or are set up as happenings within the same data system.
Examples might be sales, shipments, usage on work orders, instances of
manufacturing, “receiving� etc.. In these cases, decide which of these your
data system can and will interpret and execute as inventory transactions, and
get/make a data system that properly does so. Then, when implementing your
procedures under #4, consider entry/execution of such an action in the data
system to also be recording of the inventory transaction






I am looking to create a database which will keep track of a warehouse
inventory as well as inventory on sales trucks and track customer sales. Want
to link tables so that totals are linked when inventory is transferred to
trucks the warehouse inventory reflects this transfer. Also truck inventory
to reflect custormer sales. Any suggestions as to what type of tables, forms,
querys and relationships I should create would be helpfull. Thank you in
advance for your help.

Great information, Fred. There are a couple of more advanced topics that
could be added. That would be serialized inventory and kit inventory, and
substitution items.
Serialized inventory is where some or all inventory items have an individual
serial number.
Kit inventory is where an item may be contstructed from other inventory
items or may be deconstructed to supply requirements from its component
parts.
Substitution inventory is where item QAZ can be substitued for THB.

But, those are very advanced inventory considerations.

"Fred" wrote in message
...

You are asking about a transaction based inventory system. The Northwind
example does not do this. Below is a summary on this that I previously
wrote.

Sincerely,

Fred

Most people would define inventory tracking is an information system that
always "knows" the current inventory. Automated inventory systems handle
this by automatically adjusting the inventory numbers for the various
happenings which increase or decrease inventory. For those that don't
already have this in place, this is a 10 times larger job than they think
it
is. So, for many of them, a "less perfect" system that doesn't fully do
this would be better for them. For example, where you update the
inventory
figure by simply entering in then new total count for that item. If not,
read on!

The central "information" system can be paper/card or electronic.
Electronic systems can be of various types such as text documents,
spreadsheets, but most are database based. For this example

Here are the main steps.....always read the later ones before you
start.....where you are headed should always influence what you do.

1. Create unique "names" / identifiers for all items that you want to
track. The most common example of this is your company's part number
for
the system, following all of the rules for a good part numbering system.
An
alternative is somebody else's part number combined with their name. For
wording in this writing, I will presume you're using "part number"
This
includes defining all units of measure. . E.G. does a part number for
rope
mean 1 foot of that rope, one 200' spool of that rope etc.

2. Define your "sphere" of what will be considered to be "in" your
inventory. Is this your stockroom, your whole building, your whole
company, a single service truck?

3. List all of the current ways in your company that inventory of an
item
in your "sphere" could be modified*. The 4 main categories of this a




Income
Outgo (sales, consumption etc.)
Creation*
Destruction*

* E.G. If, by a manufacturing act, you use part a and part b to build part
c, that act "destroys" A & B and "creates" C

The results must be

4. Set up and implement procedures, rules, practices to make sure that
every instance of every item #3 will get recorded as a transaction for
each
part number involved, and that happenings not under #3 are not recorded.*
Usually, this requires defining a mental or physical around your "sphere".
For example, if your "sphere" is (only) your stockroom and your production
area, then your procedures must make sure that moving an item from your
stockroom to your production area is never recorded as a transaction, and
moving an item from either of those two areas to elsewhere is always
recorded
as a transaction.

5. Get / Set up a data system which has a current inventory quantity for
each part number, and which supports receiving each recorded transaction
and
making the appropriate modifications to inventory quantities for each
recorded transaction.*

6. Make sure each transaction gets processed in the data system.*


Note: A portion of the items under #3 (and processed under #4, #5 & #6)
might be, or are set up as happenings within the same data system.
Examples might be sales, shipments, usage on work orders, instances of
manufacturing, "receiving" etc.. In these cases, decide which of these
your
data system can and will interpret and execute as inventory transactions,
and
get/make a data system that properly does so. Then, when implementing
your
procedures under #4, consider entry/execution of such an action in the
data
system to also be recording of the inventory transaction






I am looking to create a database which will keep track of a warehouse
inventory as well as inventory on sales trucks and track customer sales.
Want
to link tables so that totals are linked when inventory is transferred
to
trucks the warehouse inventory reflects this transfer. Also truck
inventory
to reflect custormer sales. Any suggestions as to what type of tables,
forms,
querys and relationships I should create would be helpfull. Thank you in
advance for your help.

Yeah, you're right.

My work has always been in manufacturing where kitting and substitutions are
subsets of / already covered by manufacturing (= consuming items to "make"
items) and so I underemphasized and overgeneralized those in my post.

And I never had to do automated inventory where the serial number sof the
individual items had to be recorded / tracked.

Sincerely,

Fred

Back in the dark ages of mini computers using a really good version of BASIC
called Business Basic, I did an Inventory tracking system for a gun dealer.
Tracking serial numbers was very important. Had to be able to track an item
from the time it hit the door until the time it left the store.

"Fred" wrote in message
...
Yeah, you're right.

My work has always been in manufacturing where kitting and substitutions
are
subsets of / already covered by manufacturing (= consuming items to
"make"
items) and so I underemphasized and overgeneralized those in my post.

And I never had to do automated inventory where the serial number sof the
individual items had to be recorded / tracked.

Sincerely,

Fred

Hi
There are some great designs for Inventories (and loads of other treasures)
here.
http://www.databaseanswers.org/data_models/index.htm
Can they be adapted for your purpose?
Evi

"pkfloyd" u46723@uwe wrote in message news:8b4487f57710d@uwe...
I am looking to create a database which will keep track of a warehouse
inventory as well as inventory on sales trucks and track customer sales.
Want
to link tables so that totals are linked when inventory is transferred to
trucks the warehouse inventory reflects this transfer. Also truck
inventory
to reflect custormer sales. Any suggestions as to what type of tables,
forms,
querys and relationships I should create would be helpfull. Thank you in
advance for your help.