Need advice on choosing template...

[paulb104]

Hey

I've been using Excel for years as a database and since I just upgraded to
Office 2003 I know it's time that I learn Access.

There is one database I need to create.

As a hobby, my wife makes beaded jewelry and we sell them at shows. We need
a database to keep track of the beading supplies and the finished jewelry.
What we've been doing is calculating the component cost of each piece and
then we work from there. We have about four hundred different components in
all. They fall into about eight main categories and a large number of
subcategories. All of it is already entered into Excel, with the quantity and
the purchase price.

Keeping in mind that I've never used, or have seen Access used, what I'm
envisioning is when a new piece is added, there would be a drop down menu for
each category to choose the component, then a box to enter the quantity, then
having Access automatically calculate the total cost of the components. If
possible, we'd create a formula where time to make=percentage to markup cost
(where if it took one hour to make then the sell price is xx% more than the
component cost).

I know that I'm asking a lot here. I honestly don't know how to proceed with
this. I'd really love to stop using Excel for this. We know that there's a
good chance that the data from Excel would have to manually inputted into
Access, as opposed to importing it, and we're ok with this.

Thanks very much, in advance, for any advice given!!!!!!

Paul

PS Actually, there's another database I need, for a liqueur bottle
collection. For this I figure I'll download the "Wine collection database"
from the website and modify it as needed...

 

[Larry Linson]

If your jewelry is always composed of indiviual components, not
pre-constructed components which would each have their own Bill of
Materials, you are talking about the simplest form of "Bill of Materials
Processing". In complex form, programming BOMP was a career for many
mainframers.

As a start you need a tblJewelry for the pieces of jewelry, a tblComponents
for the individual components, and a table referring to the unique IDs for
both these tables, with a "number of" showing how many of the components
were used in the piece of jewelry.

tbl Jewelry
JewelryID -- unique ID, the Primary Key
JewleryPrice --
JewelryActualSales Price --
... other information you'd keep, e.g. description

tblComponent
ComponentID -- unique ID, PK
ComponentCost --
... other information you'd keep, e.g. description

tblJewelryComponents
JewelryComponentID -- unique ID, PK
JewelryID -- foreign key to Jewelry table
ComponentID -- foreign key to Component table
NumberOf -- number of this component in this piece of jewelry

Note that I did not include a field for Cost in the tblJewelry Table. You
will calculate that when you need to display it in a form or report. And, it
won't be trivial... if you buy beads by the 16" strand, and use them by the
"each", then you'll need to decide how to keep the cost.... if you keep it
by "each" which will make calculating costs simple, then you may will have
to figure in the number of "rejects", too.

Obviously, there may be other information of interest... dates and such...
which you will want to track, too.

Larry Linson
Microsoft Access MVP