Multivalued fields - Can someone explain what they are for?

[efandango]

This may sound like a dumb question, but I really can't understand
Microsoft's explanation of how I may use Multivalued fields in Access 2007. I
think that I'm suffering from old-style Access orthodoxy. Can someone explain
a simple real-world scenario where I may use this new feature in Access 2007?

 

[Jason Lepack]

Where did you find this explanation?

 

[Jamie Collins]

Where did you find this explanation?

The OP probably meant this one:

Using multivalued fields in queries
http://office.microsoft.com/en-us/access/HA101492971033.aspx

The OP may find the answers here:

Multivalued datatypes considered harmful
http://www.regdeveloper.co.uk/2006/07/18/multivalued_datatypes_access/print.html

"the Access Program Manager [did] convince me that the team was fully aware
of the implications of introducing this new data type. So why has Microsoft
done it? ...The first is that Microsoft is keen for Access to be compatible
with SharePoint...The second reason is that the company does seem to be
genuinely interested in making the product easier for power users to drive.
The development team feels that power users find the creation of many-to-many
joins using three tables conceptually very difficult and will find
multi-valued data types a much easier solution."

Jamie.

--

 

[Jason Lepack]

Great... Absolutely rediculous... Another evil like the Lookup
Field...

Cheers,
Jason Lepack

Where did you find this explanation?

The OP probably meant this one:

Using multivalued fields in querieshttp://office.microsoft.com/en-us/access/HA101492971033.aspx

The OP may find the answers here:

Multivalued datatypes considered harmfulhttp://www.regdeveloper.co.uk/2006/07/18/multivalued_datatypes_access...

"the Access Program Manager [did] convince me that the team was fully aware
of the implications of introducing this new data type. So why has Microsoft
done it? ...The first is that Microsoft is keen for Access to be compatible
with SharePoint...The second reason is that the company does seem to be
genuinely interested in making the product easier for power users to drive.
The development team feels that power users find the creation of many-to-many
joins using three tables conceptually very difficult and will find
multi-valued data types a much easier solution."

Jamie.

--

 

[Duane Hookom]

Without endorsing the use of these, let's assume you have a mailing list of
friends and relatives with names, addresses, phone, emails,...

You want to be able to track these people according to different groupings
such as "Canoe Club", "Kiwanis", "Christmas Card", "Joe's Classmates",
"Church Members", ... This will hopefully allow you to forward off-color
email jokes to Joe's Classmates and not Church Members ;-)

I have seen some newbies that would create one yes/no field in the table of
people for each of the different groupings. This would be horribly
un-normalized.

The multivalued field would provide a fairly slick interface for creating a
single field in the table of people. You could drop-down a list of all groups
and check the ones that apply.

If you needed to store any other information about the person's group
membership, this would break. For instance if you wanted to add a status or
start date for the relationship between the person and the group. You
couldn't do this with a multivalue field.

I think it's best to avoid multivalue fields however they are much better
than creating multiple yes/no fields in a table.


--
Duane Hookom
Microsoft Access MVP

Where did you find this explanation?

The OP probably meant this one:

Using multivalued fields in queries
http://office.microsoft.com/en-us/access/HA101492971033.aspx

The OP may find the answers here:

Multivalued datatypes considered harmful
http://www.regdeveloper.co.uk/2006/07/18/multivalued_datatypes_access/print.html

"the Access Program Manager [did] convince me that the team was fully aware
of the implications of introducing this new data type. So why has Microsoft
done it? ...The first is that Microsoft is keen for Access to be compatible
with SharePoint...The second reason is that the company does seem to be
genuinely interested in making the product easier for power users to drive.
The development team feels that power users find the creation of many-to-many
joins using three tables conceptually very difficult and will find
multi-valued data types a much easier solution."

Jamie.

 

[efandango]

I found it in the 'what's new' section of the help file that comes with
Access 2007.

"You can create a field that holds multiple values. Suppose that you want to
store a list of categories to which you have assigned an item. In most
database management systems and in earlier versions of Access, you have to
model a many-to-many relationship in order to do this correctly. In Office
Access 2007, the hard part is done for you when you choose a multivalued
field. Multivalued fields are especially appropriate when you use Office
Access 2007 to work with a SharePoint list that contains one of the
multivalued field types used in Windows SharePoint Services. Office Access
2007 is compatible with these data types."

I don't know why, but the more i re-read it, the less i understand it?...
maybe i'm just tooooo tired (getting red-eye...)

 

[efandango]

Duanne,

Thanks for taking the time to explain, appreciated. I still don't entirely
get it, though I think i'm beginning to understand. Perhaps it's because it
tend to be obsessive about normalisation and atomising data (with the odd
unavoidable, occasional exception) that I can't imagine how this Multivalue
idea works. I need to get some sleep, then re-read your explanation.

Without endorsing the use of these, let's assume you have a mailing list of
friends and relatives with names, addresses, phone, emails,...

You want to be able to track these people according to different groupings
such as "Canoe Club", "Kiwanis", "Christmas Card", "Joe's Classmates",
"Church Members", ... This will hopefully allow you to forward off-color
email jokes to Joe's Classmates and not Church Members ;-)

I have seen some newbies that would create one yes/no field in the table of
people for each of the different groupings. This would be horribly
un-normalized.

The multivalued field would provide a fairly slick interface for creating a
single field in the table of people. You could drop-down a list of all groups
and check the ones that apply.

If you needed to store any other information about the person's group
membership, this would break. For instance if you wanted to add a status or
start date for the relationship between the person and the group. You
couldn't do this with a multivalue field.

I think it's best to avoid multivalue fields however they are much better
than creating multiple yes/no fields in a table.


--
Duane Hookom
Microsoft Access MVP

This may sound like a dumb question, but I really can't understand
Microsoft's explanation of how I may use Multivalued fields in Access 2007. I
think that I'm suffering from old-style Access orthodoxy. Can someone explain
a simple real-world scenario where I may use this new feature in Access 2007?

Where did you find this explanation?

The OP probably meant this one:

Using multivalued fields in queries
http://office.microsoft.com/en-us/access/HA101492971033.aspx

The OP may find the answers here:

Multivalued datatypes considered harmful
http://www.regdeveloper.co.uk/2006/07/18/multivalued_datatypes_access/print.html

"the Access Program Manager [did] convince me that the team was fully aware
of the implications of introducing this new data type. So why has Microsoft
done it? ...The first is that Microsoft is keen for Access to be compatible
with SharePoint...The second reason is that the company does seem to be
genuinely interested in making the product easier for power users to drive.
The development team feels that power users find the creation of many-to-many
joins using three tables conceptually very difficult and will find
multi-valued data types a much easier solution."

Jamie.

 

[efandango]

Jamie,

Thanks for replying, and yes that 2nd link explained it clearly for me. I
think Duanne's correct about this feature, best avoided...

thanks

Where did you find this explanation?

The OP probably meant this one:

Using multivalued fields in queries
http://office.microsoft.com/en-us/access/HA101492971033.aspx

The OP may find the answers here:

Multivalued datatypes considered harmful
http://www.regdeveloper.co.uk/2006/07/18/multivalued_datatypes_access/print.html

"the Access Program Manager [did] convince me that the team was fully aware
of the implications of introducing this new data type. So why has Microsoft
done it? ...The first is that Microsoft is keen for Access to be compatible
with SharePoint...The second reason is that the company does seem to be
genuinely interested in making the product easier for power users to drive.
The development team feels that power users find the creation of many-to-many
joins using three tables conceptually very difficult and will find
multi-valued data types a much easier solution."

Jamie.

 

[efandango]

What's the 'Lookup Field', is that another new feature for Access 2007?

Great... Absolutely rediculous... Another evil like the Lookup
Field...

Cheers,
Jason Lepack

This may sound like a dumb question, but I really can't understand
Microsoft's explanation of how I may use Multivalued fields in Access 2007. I
think that I'm suffering from old-style Access orthodoxy. Can someone explain
a simple real-world scenario where I may use this new feature in Access 2007?

Where did you find this explanation?

The OP probably meant this one:

Using multivalued fields in querieshttp://office.microsoft.com/en-us/access/HA101492971033.aspx

The OP may find the answers here:

Multivalued datatypes considered harmfulhttp://www.regdeveloper.co.uk/2006/07/18/multivalued_datatypes_access...

"the Access Program Manager [did] convince me that the team was fully aware
of the implications of introducing this new data type. So why has Microsoft
done it? ...The first is that Microsoft is keen for Access to be compatible
with SharePoint...The second reason is that the company does seem to be
genuinely interested in making the product easier for power users to drive.
The development team feels that power users find the creation of many-to-many
joins using three tables conceptually very difficult and will find
multi-valued data types a much easier solution."

Jamie.

--

 

[Chris2]

What's the 'Lookup Field', is that another new feature for Access 2007?

efandango,

Lookup Fields were introduced in MS Access 2000.

The Evils of Lookup Fields in Tables: http://www.mvps.org/access/lookupfields.htm

In my opinion, multi-valued fields are much, much worse.


Sincerely,

Chris O.

 

[John W. Vinson]

I don't know why, but the more i re-read it, the less i understand it?...
maybe i'm just tooooo tired (getting red-eye...)

I believe it took Microsoft's building maintenance staff several weeks to
clear the scorched paint smell from the room where this... "feature"... was
announced to the Access MVP's a couple of years ago. NONE of us liked it, or
like it.

The only positive comment was "Hmmm... billable hours!"

John W. Vinson [MVP]

 

[David W. Fenton]

Lookup Fields were introduced in MS Access 2000.

Er, no, they've been around a lot longer than that, since at least
A97 and probably since A95.

 

[David W. Fenton]

Can someone explain
a simple real-world scenario where I may use this new feature in
Access 2007?

It's all about Sharepoint list compatibility, seems to me.

I see it as yet another example of Microsoft changing Access in ways
that further MS's marketing agenda but don't do its users any good
at all.