Variant Datatype Workaround (1 Viewer)

Hi All,

I'm working on putting together a MS Access based Database at work and have a bit of an issue. We've got "Parts" that I'm trying to describe in Access. Ultimately the descriptors for these parts frequently change. Right now we may have some and then two months from now there are new things we need to use to describe the parts - different, changing attributes basically. These attributes are used later for things like pricing, analysis, etc.

So that I am not constantly having to go in and make the modifications myself to add these new attributes I want to give the uses (many not so tech savy) the ability to add the new attributes as needed. My current idea is to have:

• A basic Part table
• An Attribute table (for the master attribute - Length, Width, Type, etc.)
• An Attribute Value table (that can be populated with the different values for each attribute Length = 1, Length = 2, Length = 3, etc. - pulling from the Attribute Table)
• A table to link Part to Attribute Value

My reasoning for this is that the users should then be able to add an attribute with a value to each part - and add attributes and attribute values as needed without me having to constantly tweak things.

The problem that I have is the attributes will not all be of the same data types. I know that Access doesn't really do variant data type fields, but I'm trying to work around that.

My current thought is on the attribute value table to have a field for Numeric Data and a field for NonNumeric data. For anything that is text based (type, etc.) it should go find into the NonNumeric. For other stuff, Integers (not particularly big integers), Decimal numbers, Booleans - store them in the Numeric field as a Double.
Additionally as I was reading elsewhere on here - I could probably use a Union query to create a query that spits out only one column instead of the two. My other thoughts were to store everything - Numbers and Text - in a Text field and then cast them to the correct data type when they are used. Or instead of two fields on the same table - I thought of doing two separate tables one for Numerics and one for Non.

I'm not particularly pleased with any of these ideas, but they are what I've come up with right now.

Is one of these solutions better than the others? Is doing things this way a horrible idea? Any alternative solutions? If anyone has any thoughts I would greatly appreciate it.

Hello ! Welcome to the forum !

Can you show us en example ?

PartName

TodayAtributes:
AtributeName1 -> Value -> MeasurementUnit
AtributeName2 -> Value -> MeasurementUnit
..........
AtributeNameN -> Value -> MeasurementUnit

TomorrowAttributes:
AtributeName1 -> Value -> MeasurementUnit
AtributeName2 -> Value -> MeasurementUnit
..........
AtributeNameP -> Value -> MeasurementUnit

Sure, thanks for the response. It would be something like:

Part -> Front Panel

Attributes (Current):
Length -> 100 -> mm
Width -> 50 -> mm
Thickness -> 2.5 -> mm
Material Type -> Steel
(where the first three will be stored just as a number, not with the unit of measurement - we only use mm for these)

Attributes (Future):
Length -> 100 -> mm
Width -> 50 -> mm
Thickness -> 2.5 -> mm
Material Type -> Steel
Material Quality -> High
(where Material Quality is added)

As you can see in both situations current and future I have attributes that are numbers and others that are text. In building a table of attribute values I can't combine them unless I store the numbers as text - which seems wrong but maybe not.
I need the ability to have the users add these attributes more or less on the fly.

To add attributes "on fly" to a part don't seem to be very hard:

tblParts
ID_Part - AutoNumber (PK)
PartName
OtherFields

tblAttributes
ID_Attr - AutoNumber (PK)
AttrName
OtherFields

tblPartsAttributes
ID_PartAttribute - AutoNumber (PK)
ID_Part - Number (FK on tblParts)
ID_Attr - Number (FK on tblAttributes)
OtherFields

So, you can add any number of attributes (in tblPartsAttributes ) from the tblAttributes .
Also, by using the NotInList event the user can add new on fly attributes to tblAttributes, in order to use this in tblPartsAttributes .

But I am wonder how you will implement a logic for computation based on this "on fly" attributes ?!?!?!?

That is more or less how I have it structured. My problem/concern though is how to store information about the attributes that is of varying data types.

For instance, the way I'm currently working is something like:
tblAttributes
ID_Attr - AutoNumber (PK)
AttrName
NumericAttrValue (Double)
NonNumericAttrValue (Text)

And depending on what type of data that attribute is store its value in either the NumericAttrValue or NonNumericAttrValue field - but this doesn't seem quite right. Since I can't have a field that stores data of a variant type I'm not sure of a better way.

It is very unusually what you try.
I suggest to declare a field (text) for values and another field (text too) for data type (choose from several data types using a combo). So your program will know how to handle the value.

I remind you that VB has a function IsNumeric(argument) that return True if argument can be evaluate as number. So, if you will never need to store a number as text, you always can decide if the value is a number or text and, in this case, the field for data type is no more necessary.

This involve to instruct the users about data types. Any mistake here is a fatal error.

I created an application for specialty lines insurer a few years ago. As they developed new types of policies, they were constantly coming up with new data elements. A traditional schema can't keep up with a growing business. It simply takes too long to create new tables and forms to handle the new policy types so I created a more flexible system that used a series of tables that allowed the user to define new data fields and define new policies to group those data fields.

I've included two pictures of tables that will show how the tables worked. The first shows how fields are defined. I included data type and control type fields as well as query names to be used for combos. The other picture shows the actual data table. I used a memo column for long text and a text column for everything else. The Field definition table defined how fields were handled.

The third picture is the actual data entry form.