Hpw best to link item & detail when items are very different

  • Thread starter Thread starter Bill
  • Start date Start date
B

Bill

I am trying to decide the best way to design a DB that tracks inventory of
Electronic Equipment.

Where I am struggling is that the various types of equipment each need
different details fields.

basic example: Item Telephone would have details of Color, Cordless y/n,
Answer y/n
Item TV would have screen size, Display type etc


A single details table whould have lots of unused fields for each Item
type - but it could be done.
A details table for each Item type makes the most sense but I am not sure
how to display the one I need using forms

eg I open a "New Item" form, and select TV from my Itemtype combobox -
what should happen next to display TV detail fields ONLY

reset the recordsource in VB to pull in TV detail data and then somehow
rename the form fields?
just open up another form -may look messy
Subform?
pehaps use a tabbed form with a hidden tab for each item type and hide all
except that selected?
something else?

My aplogies if this has been covered before but such concepts are really
hard to Google!

Thanks

Bill
 
We use a table structure like:

tblItems (1 record per item)
===========
itmItmID PK autonumber
itmITyID Link to tblItemTypes.ityITyID
itmName name of item
itmLocation ...
itm...

tblItemTypes
=================
ityITyID PK autonumber
ityName name of item type like Telephone, TV,...

tblAttributes
===============
attAttID PK autonumber
attName name of attribute such as Size, Color, Serial Number,...

tblItemAttributes
=====================
itaItAID PK autonumber
itaItmID links to tblItems.itmItmID
itaattID links to tblAttributes.attAttID
itaValue text field to store the Color or Size or Serial Number
 
Thanks - I understand the concept and it looks efficient but how do I go
about making a form around this to enter the data?
I want the attName value to be a form field name.

Bill
 
I forgot to add the table that describes which types have which attributes:
tblTypeAttributes
================
tyaTyAID pk autonumber
tyaITyID link to tblItemTypes.ityITyID
tyaAttID link to tblAttributes.attAttID
tyaActive

You create a form (frmItem) to enter your item information. Use a continuous
subform based on tblItemAttributes. I should have mentioned these two fields
should be defined as a unique index:
itaItmID links to tblItems.itmItmID
itaattID links to tblAttributes.attAttID

Once you select the itmITyID from a combo box (cboITyID) on the main form,
you can append to tblItemAttributes

INSERT INTO tblItemAttributes(itaItmID, itaattID)
SELECT forms!frmItem!txtItmID, tyaAttID
FROM tblTypeAttributes
WHERE tyaITyID = Forms!frmItem!cboITyID;

Your subform will then list all of the attributes for the item based on the
item type.
 
Back
Top