Access 2002 Is a variable lookup possible?

[DemonDNF]

I don't what to search for (I'm a retired COBOL programmer), so I will describe what I need and hopefully it will make sense to someone.

I am making a personal inventory for electronic components. I don't know the specs of components I haven't bought yet so I need complete flexibility in table lookups.

SPECS (master DB)
Unique key
Part (no duplicates)
Spec1
.
.
Specn


ResistorTypes
Key (autonumber)
Type


CapacitorTypes
Key (autonumber)
Type


Goal: I would like the ability to chose between several lookup tables in each Spec field, ie: ResistorTypes, CapacitorTypes, etc.

EDIT: I don't mean any sort of join, I mean to decide which table to use for lookups in forms, queries and reports. The best description that comes to mind is dynamic lookups. The Spec table would store which name of the table to use in lookups.

Robert

 

[DemonDNF]

Here's an idea of where I'm going with this. My labels for the drawers look like this:

All of them share the bottom right spec, package. That one is already implemented.

Some of the specs like tolerance are shared by components like resistors and capacitors.

Some are specific to a particular part, like ohms apply to resistors, farads to capacitors, etc.

Robert

 

[Galaxiom]

Specs should be separated into a related table with fields for PartID (FK to Parts table), SpecType, SpecValue. This structure allows now Specs to be added by adding records to a Spec table rather than adding fields. (Having to change the table to accomodate change alwys indicated an strucural error.)

The table of SpecTypes will have the SpecTypeID, SpecTypeName, SpecTypeUnit. SpecTypeUnit should be Ohm, Farads, etc.

Personally I wouldn't include the multiplier ranges as different Units. Multipliers would be in another table and the appropriate one to display would be calculated on demand from the actual value entered so the value is always in an appropriate range. This would be done with a custom function.

You might choose to handle unit multipliers differently because of the complexity in this but be aware that you may be trading that for complexity in querying components accross ranges of units. Moreover the possibility of entering effectively the same component with different multipliers grows if it is done that way.

A table of ComponentTypes will hold a key for Resistor, Capacitor, etc. This key will be a field in the Parts table.

A junction table defines the many to many relationship between PartType and SpecType to that only the appropriate SpecTypes are available for partiuclar part types.

The Specs are displayed in a SubForm so any number of them can be added to a part. The SpecTypes are entered via a combo whose RowSource is defined according to the junction table records.

Hope this sets you into the right direction.

 

[Galaxiom]

BTW. Managing Specs values that are different datatypes can be managed in a few ways. One is to use text for everything but this has disadvantages. An alternative is to provide separate numeric and text fields but this requires some tricky manipulation to display the right one.

Also note that the precision of the value across a wide range of multipliers must be considered if you go that way. One possibility is to use Decimal field with very high Precision.

Another way is to store the value in two fields. One as the significant digits and another as the multiplier both as integers. Now I have thought about it I would probably use the latter. It would also eliminate the need for the function to manipulate the range multiplier.

 

[DemonDNF]

I'm a visual person so it's going to take me a while to digest all that.

Of course I thought of the term dynamic after I had posted the thread. I googled "access 2002 dynamic lookup" and fell on this:
http://support.microsoft.com/kb/304302

It shows how to set up a dynamic select statement. I'm gonna play with that a bit since I already have it set up in the sample database. That and learning something isn't hurting me in this project.

Robert