Multivalue fields

[Mike Painter]

The newer version of Access allows multivalued fields, something I am
familiar with from Pick.
Are there any new (string handling) functions in VB that will allow working
with these?

It struck me tonight that used with openargs among other things they can be
a great way to pass some data.

 

[Allen Browne]

Not new, but Split() would be useful for parsing a delimited list into an
array.

Some of the built-in functions have been modified to handle MVFs, e.g.
DLookup().

 

[Albert D. Kallal]

The newer version of Access allows multivalued fields, something I am
familiar with from Pick.
Are there any new (string handling) functions in VB that will allow
working with these?

I am a long time pick developer, and I also a access developer

There is really no additional functions because when you pull multi values
in
access code, you wind up with a reocrdset like before. So, in a sense you
don't gain the same flexibility that you did in pick land. In other words
the
strong feature in pick was one string could represent a whole invoice, or
one string who would represent a whole bunch of values. It is my belief that
this feature of a simple string being able to hold a complex structure and
then pass it around between all your code modules is what made pick so
incredibly great to use.


In fact today the reason why developers are so crazy about xml is this exact
reason
(flexibility) that allows you to send a bunch of values as a string. Most
web
services such as soap are all xml based for this very reason - (ie:
that web services (soap) can send a whole invoice to another web server
or a client that is consuming those web services. So, these xml strings are
very similar to pick.

Xml strings as a structure
is a true affirmation of how strong the pick conceptual model is.

Also keep in mind that a lot of developers in modern development platforms
are using these delimited strings (xml) to store structures. A great example
of this is the new ribbon control in access 2007 is based on xml (the
developer simply needed a handy dandy weight to store complex structure as a
string, and therefore they chose xml for this purpose)

While they call these "new" fields multi-valued in access, they are in fact
regular normalized tables behind the scenes that you simply don't see.

It struck me tonight that used with openargs among other things they can
be a great way to pass some data.

Since the access multi values only applied to the actual table structures,
it doesn't change anything in your coding practices. However because I came
from a pick background one tends to think in terms of delimiters a lot. And,
there is a number of functions in access that you can use with delimited
strings.

I have for years used:

strPassValue = Parm1 & "~" & Parm2 & "~" & Parm3

docmd.OpenForm "frmCustomers,,,,,,strParms

In the forms on-load, we can go

Parm1 = split(me.OpenArgs,"~")(0)
Parm2 = split(me.OpenArgs,"~")(1)
Parm3 = split(me.OpenArgs,"~")(2)


So, we have the "split" command for years.

eg:
strTest = "one,two,three"

Msgbox split(strTest,",")(1)

The above would display two (split function is zero based)


You can also pull that split() string right into a array. (kind of like
matread in pick).

eg:

dim MyParms as variant
dim s as variant

MyParms = split(me.OpenArgs,"~")
for each s in MyParms
debug.print s
next


The above would print the 3 values to the debug window. And, you don't have
to use for...each, but can declare a counter var and go:


dim MyParms as variant
dim i as integer

MyParms = split(me.OpenArgs,"~")
for i = 0 to ubound(MyParms)
debug.print MyParms(i)
next i

So the multi-value features you speak about in access **only** apply to
tables. There's really no programming side that changes how you would deal
with delimited strings (but as you see above, we always been able to deal
with delimited strings quite well in access anyway).

The significant advantage of the multi value fields in access is a novice
developer/user can build a form that allows you to capture your favorite
colors, and we all know all about poor designs that have people stuffing
all kinds of messy things into some memo or large memo text field. The
problem is then you cannot do any decent reporting when you design database
this way.

The correct way to deal with these things is to
split this data out to another table.

So, doing things wrong, in access one might go:

table name "Persons"
id : (autonumber)
Name: Albert Kallal
City: Edmonton
FavColor1: Red
FavColor2: Blue
FavColor3: Green

As you can see the above is a very poor, and un-normalized design. It is
difficult since what happens if we need 4 colors? And printouts get messy
because a person might only have 1 color, but you have to put all 3 fields
in the report.

A much better normalized design is:


Table person:
id : (autonumber)
Name: Albert Kallal
City: Edmonton

Table tblFavorateColors
id : (autonumber)
person_id : (fk back to person)
FavColor : Red

In the above design, we would have to set up a form plus a sub form, and
build the two tables. For new users to access this can be too much work.

With the new multi value feature in access, you simply creates a multi-value
field and you are done (what this means is that new users don't have to be
taught relational database design).

Also there is a new control that appears on your form with a bunch
of check boxes that will allow you to simply check off a users favorite
colors. In
other words this feature is just a simplification of the user interface for
new
users.

Of course, it also means that sql also been extended also. If you want to
find all people that have blue and red in their list of favorite colors,
if you done this correctly and use the above two tables, you have to
go:

select Name, FavColor from tblPersons
inner join tblFavorateColors on persions_id = tblPersons.id
where FavorateColor = "Red"
and
id in (select persons_id from tblFavorateColors where color = "Blue")

Now, the above is quite nasty. In pick we would simply go:

PICK:
list Persons Name FavColor with FavColor = "Red" and with FavColor = "Blue"

With the new multi value feels an access, the sql been extended, you can go:

NEW MV SQL:
select Name, FavColor from tblPersons
where FavColor = "Red" and FavColor = "Blue"

As you can see it significantly reduces the complexity of the sql.

So what the new multi value features in access does is it allows new users
have to build applications in which they don't have to design a related
table to store the list of favorite colors or whatever. in addition you'll
see a new control appear on the screen in which it's a bunch of check boxes
and again it requires no code to do this.

A good number of developers
don't like the way the new mv fields work because we don't have an option to
turn on or "show" the additional tables that access creates behind the
scenes. Note that in code you *are* able to read/use the multi-values
(you just stuff the field into a reocrdset..and deal with reocrdset code
as you always done, and this allows you to use/read/update the mv data
in access, but it a regular reocrdset as before).

 

[Mike Painter]

I did a soundex module several years ago and wrote functions that emulated
Count, Insert into, and Convert (If I remember the names right.)
The net result was Access code that, if you ignored the functions, was
almost as short as doing it in Pick Basic.
The Pick model was great, but what can you expect when it came from GIRLS.

Thanks for the info.

 

[Gajendra]

I read your comment, actually I am implementing a report for Access database.
In this database we have some multivalued columns and I want to count on
values, I mean

Ex. Column Name: Category
values: Student/Professor/Faculty

I want to count how many students, faculty, professor in the table and
how many students are professor and how many students are faculty or how
many persons lie on all three categories.

I am using expression builder for this I tried
DCount("

![ID]","Table", "

![Category] Like 'c*' ")
it is returing Error###

but
DCount("

![ID]","Table", "

![Name] Like 'c*' ") is working

It means this expression is working only for singlevalued columns, can you
tell me how to do it with multivalued column...
or any other way to solve above problem(count)?
Is is confusing I believe I cannot apply these functions on multivalued
columns but if donot want to change my database schema(I need to make lots of
changed)
how can I solve this problem. At the end if i cannot continue with
multivalued I have to change database design. I used oracle/DB2/My SQL. I
thought access will be simpler but I found it confusing..

please help me guys..

 

[Mike Painter]

Set up a query using the values from the multivalued fields.
(SomeField.values)
This will give you a query that works propeerly with a relational dadabase.

I read your comment, actually I am implementing a report for Access
database. In this database we have some multivalued columns and I
want to count on values, I mean

Ex. Column Name: Category
values: Student/Professor/Faculty

I want to count how many students, faculty, professor in the table and
how many students are professor and how many students are faculty or
how many persons lie on all three categories.

I am using expression builder for this I tried
DCount("

![ID]","Table", "

![Category] Like 'c*' ")
it is returing Error###

but
DCount("

![ID]","Table", "

![Name] Like 'c*' ") is working

It means this expression is working only for singlevalued columns,
can you tell me how to do it with multivalued column...
or any other way to solve above problem(count)?
Is is confusing I believe I cannot apply these functions on
multivalued columns but if donot want to change my database schema(I
need to make lots of changed)
how can I solve this problem. At the end if i cannot continue with
multivalued I have to change database design. I used oracle/DB2/My
SQL. I thought access will be simpler but I found it confusing..

please help me guys..

I am a long time pick developer, and I also a access developer

There is really no additional functions because when you pull multi
values in
access code, you wind up with a reocrdset like before. So, in a
sense you don't gain the same flexibility that you did in pick land.
In other words the
strong feature in pick was one string could represent a whole
invoice, or one string who would represent a whole bunch of values.
It is my belief that this feature of a simple string being able to
hold a complex structure and then pass it around between all your
code modules is what made pick so incredibly great to use.


In fact today the reason why developers are so crazy about xml is
this exact reason
(flexibility) that allows you to send a bunch of values as a string.
Most web
services such as soap are all xml based for this very reason - (ie:
that web services (soap) can send a whole invoice to another web
server
or a client that is consuming those web services. So, these xml
strings are very similar to pick.

Xml strings as a structure
is a true affirmation of how strong the pick conceptual model is.

Also keep in mind that a lot of developers in modern development
platforms are using these delimited strings (xml) to store
structures. A great example of this is the new ribbon control in
access 2007 is based on xml (the developer simply needed a handy
dandy weight to store complex structure as a string, and therefore
they chose xml for this purpose)

While they call these "new" fields multi-valued in access, they are
in fact regular normalized tables behind the scenes that you simply
don't see.


Since the access multi values only applied to the actual table
structures, it doesn't change anything in your coding practices.
However because I came from a pick background one tends to think in
terms of delimiters a lot. And, there is a number of functions in
access that you can use with delimited strings.

I have for years used:

strPassValue = Parm1 & "~" & Parm2 & "~" & Parm3

docmd.OpenForm "frmCustomers,,,,,,strParms

In the forms on-load, we can go

Parm1 = split(me.OpenArgs,"~")(0)
Parm2 = split(me.OpenArgs,"~")(1)
Parm3 = split(me.OpenArgs,"~")(2)


So, we have the "split" command for years.

eg:
strTest = "one,two,three"

Msgbox split(strTest,",")(1)

The above would display two (split function is zero based)


You can also pull that split() string right into a array. (kind of
like matread in pick).

eg:

dim MyParms as variant
dim s as variant

MyParms = split(me.OpenArgs,"~")
for each s in MyParms
debug.print s
next


The above would print the 3 values to the debug window. And, you
don't have to use for...each, but can declare a counter var and go:


dim MyParms as variant
dim i as integer

MyParms = split(me.OpenArgs,"~")
for i = 0 to ubound(MyParms)
debug.print MyParms(i)
next i

So the multi-value features you speak about in access **only** apply
to tables. There's really no programming side that changes how you
would deal with delimited strings (but as you see above, we always
been able to deal with delimited strings quite well in access
anyway).

The significant advantage of the multi value fields in access is a
novice developer/user can build a form that allows you to capture
your favorite colors, and we all know all about poor designs that
have people stuffing all kinds of messy things into some memo or
large memo text field. The problem is then you cannot do any decent
reporting when you design database this way.

The correct way to deal with these things is to
split this data out to another table.

So, doing things wrong, in access one might go:

table name "Persons"
id : (autonumber)
Name: Albert Kallal
City: Edmonton
FavColor1: Red
FavColor2: Blue
FavColor3: Green

As you can see the above is a very poor, and un-normalized design.
It is difficult since what happens if we need 4 colors? And
printouts get messy because a person might only have 1 color, but
you have to put all 3 fields in the report.

A much better normalized design is:


Table person:
id : (autonumber)
Name: Albert Kallal
City: Edmonton

Table tblFavorateColors
id : (autonumber)
person_id : (fk back to person)
FavColor : Red

In the above design, we would have to set up a form plus a sub form,
and build the two tables. For new users to access this can be too
much work.

With the new multi value feature in access, you simply creates a
multi-value field and you are done (what this means is that new
users don't have to be taught relational database design).

Also there is a new control that appears on your form with a bunch
of check boxes that will allow you to simply check off a users
favorite colors. In
other words this feature is just a simplification of the user
interface for new
users.

Of course, it also means that sql also been extended also. If you
want to find all people that have blue and red in their list of
favorite colors,
if you done this correctly and use the above two tables, you have to
go:

select Name, FavColor from tblPersons
inner join tblFavorateColors on persions_id = tblPersons.id
where FavorateColor = "Red"
and
id in (select persons_id from tblFavorateColors where color = "Blue")

Now, the above is quite nasty. In pick we would simply go:

PICK:
list Persons Name FavColor with FavColor = "Red" and with FavColor =
"Blue"

With the new multi value feels an access, the sql been extended, you
can go:

NEW MV SQL:
select Name, FavColor from tblPersons
where FavColor = "Red" and FavColor = "Blue"

As you can see it significantly reduces the complexity of the sql.

So what the new multi value features in access does is it allows new
users have to build applications in which they don't have to design
a related table to store the list of favorite colors or whatever. in
addition you'll see a new control appear on the screen in which it's
a bunch of check boxes and again it requires no code to do this.

A good number of developers
don't like the way the new mv fields work because we don't have an
option to turn on or "show" the additional tables that access
creates behind the scenes. Note that in code you *are* able to
read/use the multi-values (you just stuff the field into a
reocrdset..and deal with reocrdset code
as you always done, and this allows you to use/read/update the mv
data
in access, but it a regular reocrdset as before).