Multi-Value Fields

[BikeToWork]

I am rather new to Access 2007 and am working on a database with "multi-value" fields. I know most people oppose designing databases with lookup fields and these multi-value fields seem like lookup fields, only worse. They not only denormalize the database but make it so you're wed to Access and cannot convert the database to SQL Server. Attachment fields also seem like poor database design, denormalizing and making it difficult to move the database to SQL Server.

Do others agree with this or am I ranting over nothing?

 

[dhookom]

I generally don't care for multi-value fields or lookup fields.

I guess I could see using multi-value fields if I made a contacts and mailing list application for a friend. One contact might be associated with one or more distribution lists. This application would never be upsized to SQL Server and I assume I would never need to store anything else about the contact/list membership.

I haven't used attachment fields since most of my development is for multiple sites using SQL Server.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Multi value fields by themselves are not lookup fields, but you can make a multi valued field a lookup field. As you said that can be very confusing. But you can make a multivalued field a regular field. The data is in fact completely normalized; however, it is done behind the scenes. For advanced developers this may be uncomfortable to lose that visibility. I do not use them much, but I would not "poo-poo" them completely. The average user can run into some trouble using them, but at the same time they can do things with them that would be very hard for them to do without the fields. The attachment field works great, the average user would have an extremely hard time developing that utility.

I do not really get the whole converting to SQL argument though. I think there is a lot of bigger issues, but it would not be that hard to normalize into a traditional structure.

 

[PHV]

For me, a multi-value field is only for compatibity with sharepoint.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheAceMan1]

How are ya BikeToWork . . .

I agree with [blue]PHV[/blue] in the context that when you design a DB ... [blue]its impossible to have a multivalue field![/blue] Each field has a datatpe and thats it! ... Did you not run across this in your design?

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MajP]

That is not what a multi valued field is. A multi valued field has a single data type. The data appears concatenated when you view it, but in fact it is stored in a child table in a normalized manner. Basically it structures the data in the same way you would a child table, but it does it behind the scenes. Advanced developers may find that annoying, but novice users may like the ease.

You can see this demonstrated when using a recordset on a multivalued field

Assume you have a table with a multivalued field called "mv". In that field you have records like.

ID MV
1 red, blue, green
2 orange, yellow
3 red, yellow

dim rs as dao.recordset
dim rsChildren as dao.recordset
set rs = currentdb.openrecordset "tblOne"
Do while not rs.eof
set rsChildren = rs!MV.value
do while not rsChildren.eof
debug.print rs!ID & " " & rsChildren!fields(1)
rsChildren.movenext
loop
rs.moveNext
loop

This would print
1 red
1 blue
1 green
2 orange
2 yellow


Although you see this in the field when viewing it
red, yellow, blue
it is not a text "red, yellow, blue". It is in fact three seperate records. You can query
where MV = "red"
and it will return all fields with "red".

I am not that big into using them, because you are limited to the multi value combo box and text box and can not use traditional controls. However, there is some utility and need to be understood by advance access developers.
As I said the attachment field is a multivalued field. This has completely overcome bloating and provides a novice user capability that only advanced developers can deliver.