Suggest a solution - Table design

[exebat]

Hi all.

I have a table named Articles that has the fields: ArticleID,
ArticleName, Price and so on....

What I want to do is to make a new table that would hold some related
Property records for each article.

For example... Color, Weight, Length,..

This is not a problem, but what I want is to be able to change fields
names for the second table (like PropertyID, PropertyName) and be able
to search among those values from query.

This is the most important part:

 

[Steve]

You need the following tables:
TblColor
ColorID
Color

TblWeight
WeightID
Weight

TblLength
LengthID
Length

TblArticle
ArticleID
ArticleName
Price
ColorID
WeightID
LengthID

You then need a query based on TblArticle. You will be able to set the
criteria for ColorID, WeightID and LengthID to whatever you want.

I bet you don't even need "blue;2kg;5m".

Steve
(e-mail address removed)

 

[exebat]

You need the following tables:
TblColor
ColorID
Color

TblWeight
WeightID
Weight

TblLength
LengthID
Length

TblArticle
ArticleID
ArticleName
Price
ColorID
WeightID
LengthID

You then need a query based on TblArticle. You will be able to set the
criteria for ColorID, WeightID and LengthID to whatever you want.

I bet you don't even need "blue;2kg;5m".

Steve
(e-mail address removed)

Yes, but then if I want to set for example Height, I would have to
create a new table and modify the query. I am looking for the way to
be able to only add record to table PROPERTIES and it becomes
available to ARTICLES table and in query for each ArticleID.

 

[Larry Linson]

If you don't like Steve's approach, you could try a related table,
tblProperties, with each record having

PropertiesID, an Autonumber primary key
ArticleID, a foreign key to the record in tblArticle
PropertyType, either text stating the type (e.g., "color") or
PropertyTypeID, a foreign key to a table of property types
PropertyDescription, text indicating the value of the property (e.g.,
"blue")

That's not as good, really, as the approach Steve recommended, of having a
column for each type of property, and a lookup table for the value, but it's
better than having properties all strung together so you don't know which is
which as you propose.

But, if you feel compelled to "back yourself into a corner" and make life
far more difficult if you want to work with the properties in the future,
the Access 2007 and later ACCDB file format has a provision for a
"multi-value field" also called "complex data" that lets you string values
together. I do NOT recommend you use it; I believe it was included only to
accommodate existing data in Share Point tables and I believe that is the
only case in which it should ever be used... yes, only case EVER.

Larry Linson
Microsoft Office Access MVP

You need the following tables:
TblColor
ColorID
Color

TblWeight
WeightID
Weight

TblLength
LengthID
Length

TblArticle
ArticleID
ArticleName
Price
ColorID
WeightID
LengthID

You then need a query based on TblArticle. You will be able to set the
criteria for ColorID, WeightID and LengthID to whatever you want.

I bet you don't even need "blue;2kg;5m".

Steve
(e-mail address removed)

Yes, but then if I want to set for example Height, I would have to
create a new table and modify the query. I am looking for the way to
be able to only add record to table PROPERTIES and it becomes
available to ARTICLES table and in query for each ArticleID.

 

[exebat]

I tough something like

tblArticles
-----------------------
ArticleID
ArticleName
ArticlePrice

tblProperties
-------------------
PropertyID (auto number)
PropertyName (text - ex. "Color","VIN Number")



tblArticleProperties
-----------------------------
APID (autonumber)
ArticleID (to relate it to the right article from tblArticles)
PropertyID (to relate it to the right property)
PropertyValue (the value of the property, ex. "Blue","123123123")


I think this is the best approach as the properties are different for
every user of the database (there are many users). This way every user
can set his properties by himself and the Steve's way, I would have to
make different database for everyone that uses it (but it was a good
advice, just not for my situation).

Now I need a way to have these properties in one field (PROPERTIES) of
the Articles query for example:

ArticleID (1), ArticleName ("Hammer"), ArticlePrice ("2.10"),
======PROPERTIES("wooden,blue,k12123,no packing")=====

How can I do that ?

 

[Catherine Piermatteo Marie Piermatteo]

Hi all.

I have a table named Articles that has the fields: ArticleID,
ArticleName, Price and so on....

What I want to do is to make a new table that would hold some related
Property records for each article.

For example... Color, Weight, Length,..

This is not a problem, but what I want is to be able to change fields
names for the second table (like PropertyID, PropertyName) and be able
to search among those values from query.

This is the most important part:

 

[Steve]

Why don't you tell us specifically what data you are modelling in the
database and then we can better help you. The direction you are going is
frankly wrong! As Larry said, you are backing yourself into a corner for
future use of the database.

Steve

 

[John W. Vinson]

Now I need a way to have these properties in one field (PROPERTIES) of
the Articles query for example:

ArticleID (1), ArticleName ("Hammer"), ArticlePrice ("2.10"),
======PROPERTIES("wooden,blue,k12123,no packing")=====

Why do you feel you need this? What will you do with this non-atomic,
concatenated, inconsistant field?

I'm with Steve here - I'm really wondering what, in the real world, you're
trying to accomplish here? I'm strongly thinking that you're on the wrong
track.

That said... you can adapt the code in
http://www.mvps.org/access/modules/mdl0004.htm
to return a comma-separated string from a list of records in the query for
this articleID.

 

[Larry Linson]

I think this is the best approach as the properties
are different for every user of the database (there
are many users). This way every user can set his
properties by himself and the Steve's way, I would
have to make different database for everyone that
uses it (but it was a good advice, just not for
my situation).

I'm a little puzzled by your statements here. Is this a multiuser database,
with all users sharing the same backend (for data storage) tables? If so,
what you say is not correct.

If it is an individual database with each user having their own copy of the
tables, then what you describe would be true. Even in this case, you will
be well-advised to have separate backend and frontend, but for the
individual user. If you have the tables and user-interface (frontend) in
one database, you will have difficulty updating the UI without overwriting
the user's data.

What you describe is much like the approach I suggested "if you wouldn't use
Steve's". But if this data is every consolidated, or if you ever need to add
specific handling of property data in the future, you are creating a trap
for yourself.

Finally, I join with the other responders in asking what your purpose is in
stuffing inconsistent concatenated data in a single field? If it is for
anything other than in a Query that is record source for a Form (display) or
Report (printing) for a single user, using his/her database tables, you will
have a problem.

Larry Linson
Microsoft Office Access MVP