Hi all

I am in the middle of developing an asset managing/inventory database
system for outdoor recreation equipment. Based loosely on the asset
tracking template in Access97

I am stuck on how to model the following scenario

Some pieces of equipment in the inventory are group of individual
items. The instructors only want to count the things as a complete
set (an abseiling kit, comprising of some carabiners, gloves and a
piece of hardware)
The manager wants to know how many of each individual item are
required to make up complete sets, as he buys xx number of carabiners
and yy numbers of gloves to make up the complete sets.

Also some of the items are counted individually when they are used for
a different purpose

Any suggestions are appreciated

Darryn
-- remove two eyes to reply!

Try something like this.

tblItem
ItemID (pk)
Description (harness, carabiner, airtank, wetsuit)

tblKit
KitID (pk)
Description (abseil kit, scuba kit)

tbkKitItem
KitID ( composite
ItemID primary key )

That would handle a one-level breakdown.

To define carabiner as a necessary part of abseil kits:
INSERT INTO tblKitItem (KitID, ItemID) VALUES (888, 999);
where 888 is the KitID for abseil kits, and 999 is the ItemID for
carabiners.

To count the total number of abseil kits:
SELECT COUNT(*) FROM tblKit WHERE KitId = 888;

To count the total number of carabiners (in whatever kits they are used):
SELECT COUNT(*) FROM tblItem WHERE ItermID = 999;

HTH,
TC


<Darryn> wrote in message news:(E-Mail Removed)...
> Hi all
>
> I am in the middle of developing an asset managing/inventory database
> system for outdoor recreation equipment. Based loosely on the asset
> tracking template in Access97
>
> I am stuck on how to model the following scenario
>
> Some pieces of equipment in the inventory are group of individual
> items. The instructors only want to count the things as a complete
> set (an abseiling kit, comprising of some carabiners, gloves and a
> piece of hardware)
> The manager wants to know how many of each individual item are
> required to make up complete sets, as he buys xx number of carabiners
> and yy numbers of gloves to make up the complete sets.
>
> Also some of the items are counted individually when they are used for
> a different purpose
>
> Any suggestions are appreciated
>
> Darryn
> -- remove two eyes to reply!

Er ... what drug was I on here?

The structure that I suggested, assumes that tblItem contains a seperate
entry for each physical item - not just for each *type* of item. So if you
had 8 carabiners, there would be 8 rows in tblItems.

Similarly it assumes that tblKits contains a seperate entry for each
physical kit - not just for each type of kit. So if there were 8 abseil
kits, there would be 8 rows in tblKits.

So the item description (carabiner) should *not* go in tblItems, and the kit
description (abseil kit) should not go in tbKits. Those descriptions should
go in sepertate tables:

tblItemType
ItemTypeID (pk)
Description (harness, carabiner etc.)

tblKitType
KitTypeID (pk)
Description (abseil kit, scuba kit)

then the relevent *ItemTypeID* should go in tblItems (as a pointer to the
relevent Item Type record), and KitTypeID should go in tblKits.

tblItem still makes sense: it stores the details of a specific item (not
just type) of equipment - eg. serial number, date last checked, batch #,
purchased from, etc. tblKits stores similar details pertaining to kits.

Sorry for any confusion!

HTH,
TC


"TC" <(E-Mail Removed)> wrote in message news:1065842177.195798@teuthos...
> Try something like this.
>
> tblItem
> ItemID (pk)
> Description (harness, carabiner, airtank, wetsuit)
>
> tblKit
> KitID (pk)
> Description (abseil kit, scuba kit)
>
> tbkKitItem
> KitID ( composite
> ItemID primary key )
>
> That would handle a one-level breakdown.
>
> To define carabiner as a necessary part of abseil kits:
> INSERT INTO tblKitItem (KitID, ItemID) VALUES (888, 999);
> where 888 is the KitID for abseil kits, and 999 is the ItemID for
> carabiners.
>
> To count the total number of abseil kits:
> SELECT COUNT(*) FROM tblKit WHERE KitId = 888;
>
> To count the total number of carabiners (in whatever kits they are used):
> SELECT COUNT(*) FROM tblItem WHERE ItermID = 999;
>
> HTH,
> TC
>
>
> <Darryn> wrote in message
news:(E-Mail Removed)...
> > Hi all
> >
> > I am in the middle of developing an asset managing/inventory database
> > system for outdoor recreation equipment. Based loosely on the asset
> > tracking template in Access97
> >
> > I am stuck on how to model the following scenario
> >
> > Some pieces of equipment in the inventory are group of individual
> > items. The instructors only want to count the things as a complete
> > set (an abseiling kit, comprising of some carabiners, gloves and a
> > piece of hardware)
> > The manager wants to know how many of each individual item are
> > required to make up complete sets, as he buys xx number of carabiners
> > and yy numbers of gloves to make up the complete sets.
> >
> > Also some of the items are counted individually when they are used for
> > a different purpose
> >
> > Any suggestions are appreciated
> >
> > Darryn
> > -- remove two eyes to reply!
>
>

On Sat, 11 Oct 2003 15:06:04 +1200, "TC" <(E-Mail Removed)> wrote:

>Er ... what drug was I on here?
>
>The structure that I suggested, assumes that tblItem contains a seperate
>entry for each physical item - not just for each *type* of item. So if you
>had 8 carabiners, there would be 8 rows in tblItems.
>
>Similarly it assumes that tblKits contains a seperate entry for each
>physical kit - not just for each type of kit. So if there were 8 abseil
>kits, there would be 8 rows in tblKits.
>
That is something else I have been agonising over!

The major items in the inventory are numbered ie vehicles, wetsuits,
wetsuits, backpacks, tents. Others are just counted, the abseil kits,
snorkelling kits, barrels etc etc.

Obviously an item which has been individally numbered should have a
separate record, but I am not sure if I need this for the counted
items. I know with a separate record I could keep better track of
the individual components (some have a finite lifespan as a matter of
safety policy)

>So the item description (carabiner) should *not* go in tblItems, and the kit
>description (abseil kit) should not go in tbKits. Those descriptions should
>go in sepertate tables:
>
>tblItemType
> ItemTypeID (pk)
> Description (harness, carabiner etc.)
>
>tblKitType
> KitTypeID (pk)
> Description (abseil kit, scuba kit)
>
>then the relevent *ItemTypeID* should go in tblItems (as a pointer to the
>relevent Item Type record), and KitTypeID should go in tblKits.
>
>tblItem still makes sense: it stores the details of a specific item (not
>just type) of equipment - eg. serial number, date last checked, batch #,
>purchased from, etc. tblKits stores similar details pertaining to kits.


It seems like I need to modify my table design quite a lot, so far my
my main equipment table looks like this with a lot of small lookup
tables attached to it.

Record ID (pk autonumber)
CategoryID
SubCategoryID
Equipment Description
EquipIDNumber (have not used this as PK as it is not unique and
does not apply to all items)
Equipment Description
ManufacturerID
Model
SerialNumber-idcolourcode
SizeID
ColourID
Finitelifespan T/F
Lifespan
StatusID
Acqusitiondate
Disposaldate
PurchPrice
SupplierID
Comments
EquipmentQuantity

I had done this as it seemd to be the easiest way for me to design the
input form as I will not be on hand to support the system once it has
finished.

I think I need to have BIG rethink and break it down into smaller
tables for all of my sub categories as well as the kit/individual
items

>Sorry for any confusion!

No you have been a great help so far
Thank You

Darryn
-- remove two eyes to reply!

> I had done this as it seemd to be the easiest way for me to design the
> input form as I will not be on hand to support the system once it has
> finished.

Darryn, this is a common but mistaken approach. You should design the tables
based on the nature of the data that you need to store, and how it all
relates together. You do not consider form or report design *at all*, at
that stage. Form/report design comes later!


> I think I need to have BIG rethink and break it down into smaller
> tables for all of my sub categories as well as the kit/individual
> items

I think that would be time well spent. See if the following article helps (I
can't remember whether I suggested it to you before, or not):

http://support.microsoft.com/support...es/Q100139.ASP

Good luck!
TC


<Darryn> wrote in message news:(E-Mail Removed)...
> On Sat, 11 Oct 2003 15:06:04 +1200, "TC" <(E-Mail Removed)> wrote:
>
> >Er ... what drug was I on here?
> >
> >The structure that I suggested, assumes that tblItem contains a seperate
> >entry for each physical item - not just for each *type* of item. So if
you
> >had 8 carabiners, there would be 8 rows in tblItems.
> >
> >Similarly it assumes that tblKits contains a seperate entry for each
> >physical kit - not just for each type of kit. So if there were 8 abseil
> >kits, there would be 8 rows in tblKits.
> >
> That is something else I have been agonising over!
>
> The major items in the inventory are numbered ie vehicles, wetsuits,
> wetsuits, backpacks, tents. Others are just counted, the abseil kits,
> snorkelling kits, barrels etc etc.
>
> Obviously an item which has been individally numbered should have a
> separate record, but I am not sure if I need this for the counted
> items. I know with a separate record I could keep better track of
> the individual components (some have a finite lifespan as a matter of
> safety policy)
>
> >So the item description (carabiner) should *not* go in tblItems, and the
kit
> >description (abseil kit) should not go in tbKits. Those descriptions
should
> >go in sepertate tables:
> >
> >tblItemType
> > ItemTypeID (pk)
> > Description (harness, carabiner etc.)
> >
> >tblKitType
> > KitTypeID (pk)
> > Description (abseil kit, scuba kit)
> >
> >then the relevent *ItemTypeID* should go in tblItems (as a pointer to the
> >relevent Item Type record), and KitTypeID should go in tblKits.
> >
> >tblItem still makes sense: it stores the details of a specific item (not
> >just type) of equipment - eg. serial number, date last checked, batch #,
> >purchased from, etc. tblKits stores similar details pertaining to kits.
>
>
> It seems like I need to modify my table design quite a lot, so far my
> my main equipment table looks like this with a lot of small lookup
> tables attached to it.
>
> Record ID (pk autonumber)
> CategoryID
> SubCategoryID
> Equipment Description
> EquipIDNumber (have not used this as PK as it is not unique and
> does not apply to all items)
> Equipment Description
> ManufacturerID
> Model
> SerialNumber-idcolourcode
> SizeID
> ColourID
> Finitelifespan T/F
> Lifespan
> StatusID
> Acqusitiondate
> Disposaldate
> PurchPrice
> SupplierID
> Comments
> EquipmentQuantity
>
> I had done this as it seemd to be the easiest way for me to design the
> input form as I will not be on hand to support the system once it has
> finished.
>
> I think I need to have BIG rethink and break it down into smaller
> tables for all of my sub categories as well as the kit/individual
> items
>
> >Sorry for any confusion!
>
> No you have been a great help so far
> Thank You
>
> Darryn
> -- remove two eyes to reply!