available-to-promise inventory database

[Ayelet]

I have a small home business making name bracelets from silver beads,
crystals (available in different colors), and silver alphabet blocks. The
most critical component of my database is my letter inventory. This allows
me to know if I can fill an order immediately, if I have to restock certain
letters, etc..

I would like to set up my database with the capability of doing the
following: When a customer places an order for a bracelet with the name LYNN,
I would like Access to check if those letters are available in the quantities
needed. If they are, I would like those letter blocks to be "reserved" so
that the next customer doesn't get false info if, say, the last Y was used
and no Y is available for his bracelet. When the order is filled (i.e. the
bracelet made and shipped), Access should delete those letters from the
inventory to reflect the actual inventory. I would like to be able to view
my inventory and see how many letters are there and how many, if any, are
reserved. Access should also tell me how many of each unavailable letter are
needed in order to fill current orders (probably as a negative integer?).

I'm using Access 2003 and am fairly new at it, so please be specific.

 

[Ken Snell [MVP]]

You indicate that you're fairly new with ACCESS. What you want to do is very
doable with ACCESS, but is not a one- or two-step thing to set up.

Let me suggest some starting points.

You'll need a table of your "parts"... each item that can be put together to
make a bracelet would be a part, including each letter block being a part.

Inventory can be handled dynamically (the database calculates the current
inventory based on orders filled and on part purchases made) or statically
(your database stores a number that is the current inventory number). Each
has its benefits and drawbacks, depending upon what you want and need to
accomplish.

You'll need tables to store orders for bracelets. Within these tables, you
will need a table to store the individual "parts" needed to make that
bracelet. Using your example, you'd need to store that a "LYNN" bracelet
will need 2 "N" letters, 1 "L" letter, 1 "Y" letter, and each of the other
parts that make a bracelet. You will need to decide if the order should show
all these parts, or if the order is the item "Build bracelet that says
"LYNN" as name", and then you'd need another table for defining the order
items that are listed on the order.

You will need a table to store data about which letters you need to order,
either for "customer orders" received, or to maintain your normal stock. You
can have a purchase order system read this table to assist you in creating
orders from your suppliers for the parts.

And so on... the number and type of tables will be dictated by how
automatically you want the database to function, how you want to
present/use/store the data and information, and how many users might use the
database (if it's just you, and you've designed the database, you likely can
get by without a lot of error checks and validations on the data as you'll
probably know what to enter where, etc.).

Let us know if you'd like some specific pointers on features / concepts.

 

[Ayelet]

Hi, Ken. Thanks for your answer.

You indicate that you're fairly new with ACCESS.

I have an admission to make. I lied. I am TOTALLY new to Access. But I'm
really gung-ho about doing this. To clarify, I'm the only one using the
database.

You'll need a table of your "parts"... each item that can be put together to
make a bracelet would be a part, including each letter block being a part.

Okay, this is simple. I have these fields: letter, the item order number,
and the amount in stock. The primary key is the numerical value of each
letter (I'm using the Hebrew alphabet in which each letter has a unique
numerical value).

In addition, every bracelet needs a clasp, an extender, a heart, and two
crimp beads. Add these to my parts table along with the letters?

Some of the other bracelet components are not as simple to track. Ex: I use
silver and crystal beads but, since each bracelet is a different length, I
use different amounts and I don't care to know exactly how many I've used per
bracelet. (Should I care?) Do these belong on the same table? How about the
spool of wire?

Inventory can be handled dynamically (the database calculates the current
inventory based on orders filled and on part purchases made) or statically
(your database stores a number that is the current inventory number). Each
has its benefits and drawbacks, depending upon what you want and need to
accomplish.

What's a possible drawback for dynamic handling?

You'll need tables to store orders for bracelets. Within these tables, you
will need a table to store the individual "parts" needed to make that
bracelet.

I lost you. What's a table "within" a table? How do I keep the order so
that it reads "LYNN" bracelet, but Access understands that I'll need those
four separate parts/letters? Will I need to give Access every possible name
and break it down for it/him/her(?!?) or is Access smart enough to parse each
name into its component parts.

You will need a table to store data about which letters you need to order,
either for "customer orders" received, or to maintain your normal stock. You
can have a purchase order system read this table to assist you in creating
orders from your suppliers for the parts.

Yes! Uh, how? Are there templates for this stuff available for Access
2003? If I emailed you my current invoice design, could you help me
incorporate that into my database so that Access will produce a similar
invoice for my customers?

Let us know if you'd like some specific pointers on features / concepts.
--

Us? How many are you?

Thanks for everything!
Ayelet

 

[Ken Snell [MVP]]

Sorry for my terminology "Within these tables"... what I meant is that one
or more of these tables will need store the parts being used to make a
specific bracelet.

A drawback to the dynamic approach is that, as the number of data records
grows over time, the database must perform the "calculation" for the current
inventory by reading more and more records... mostly an issue of how long it
might take to calculate the inventory level. Not usually a big problem until
you get into the tens of thousands of records or more.

There is a Northwind database in ACCESS. Quite honestly, I've never spent
much time with it, preferring to learn as I go. But I know that this example
database does have some setups for Orders and such. But I don't believe it
will be as sophisticated as what you're wanting to do.

It's possible to "parse" out the letters that are needed -- the database can
do this, but only if you program it to do this.

What you're seeking is a customized, reasonably well-featured database. As I
noted earlier, definitely doable in ACCESS, but a major challenge for
someone completely new to ACCESS. Depending upon how quickly you learn, you
could be looking at a few hundred hours of your time to research, design,
develop, test, debug, and finish the database.

This project is not one I would recommend to someone as the "first" project
you tackle. You may be better off looking for an existing database that
could do this (or have someone modify an existing database).

The newsgroup is a place for asking fairly specific questions, but is not
well suited for discussing full-scale development designs. Just too awkward
a medium for that.

Private email communication, unlike the newsgroup communications, is not
usualy done except on a "fee" basis, unless it becomes "necessary" as part
of trying to answer a specific question and the person answering the
question asks for something to be emailed to that person.

You might benefit from John Viescas' new book, "Building Microsoft Access
Applications". This has an inventory management database on a CD in it, and
John shows how to build it. John is a Microsoft ACCESS MVP, and also has
written "Microsoft Office Access 2003: Inside Out". This too is a good book
about how to build databases in ACCESS. Both are available at
www.amazon.com.

--

Ken Snell
<MS ACCESS MVP>