Suggest a solution - Table design

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
santus.Remove.RemoveThis@penn.com

"exebat" wrote in message

> 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:
> ----------------------------------------------------------------
> I would like to have them as a field in query from my first table like
> field PROPERTIES: "blue;2kg;5m" (all values in one field for each
> ArticleID)
> ----------------------------------------------------------------
>
> What do you suggest ?
 >> Stay informed about: Suggest a solution - Table design 

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

"exebat" wrote in message

On Jan 9, 10:13 pm, "Steve" wrote:
> 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
> san... DEL @penn.com
>
> "exebat" wrote in message
>

>
> > 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:
> > ----------------------------------------------------------------
> > I would like to have them as a field in query from my first table like
> > field PROPERTIES: "blue;2kg;5m" (all values in one field for each
> > ArticleID)
> > ----------------------------------------------------------------
>
> > What do you suggest ?

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.
 >> Stay informed about: Suggest a solution - Table design 

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 ?
 >> Stay informed about: Suggest a solution - Table design 

"exebat" wrote in message

> 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:
> ----------------------------------------------------------------
> I would like to have them as a field in query from my first table like
> field PROPERTIES: "blue;2kg;5m" (all values in one field for each
> ArticleID)
> ----------------------------------------------------------------
>
> What do you suggest ?
 >> Stay informed about: Suggest a solution - Table design 

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


"exebat" wrote in message

>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 ?
>
>
>
>
>
>
>
 >> Stay informed about: Suggest a solution - Table design 

On Sun, 10 Jan 2010 00:16:59 -0800 (PST), exebat
wrote:

>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
[URL="http://www.mvps.org/access/modules/mdl0004.htm"]http://www.mvps.org/access/modules/mdl0004.htm[/URL]
to return a comma-separated string from a list of records in the query for
this articleID.
 >> Stay informed about: Suggest a solution - Table design 

"exebat" wrote

> 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
 >> Stay informed about: Suggest a solution - Table design