If multi-valued fields is discorage, what should I use instead?

[JLPerez]

Can anybody explain to me why we shouldn't use multi-valued fields in Access
2007? I'm having a hard time coding the same functionality that multi-valued
offers which is exactly what the project requires. However, it looks like
Access has some problems when it comes to manipulation/storage/search etc of
those type of fields. I'm stuck and not sure how to procee here. What
functions,or code should I use that does exactly what the "manufactured"
multi-valued field does.

Any light would be appreciated..

 

[Jeff Boyce]

I'm not sure that multivalued fields don't have a place, but when Access
handles all the complexity "behind the curtain" and gives you, the
developer, no easy way to see or get at it, the value of that 'feature' is
less.

The notion of a multivalue field is that there is a one-to-many relationship
(the "many" is the multivalues). You would handle this just exactly the
same way you handled a one-to-many relationship in the earlier versions of
Access. If you haven't done that before in Access, you might want to do a
little research/study on how that's done.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[JLPerez]

Hi Jeff;
Thanks for you answer.. However, I started using Access and vba from the
2007 version, so I haven't seen how it was done before. I had searched for
few days and no answer to this. Maybe I'm asking the wrong quetions, but no
luck with this. That's why I posted here. Is there a place that you recomend
for me to go and look?
Thanks again

 

[Chris O'C via AccessMonster.com]

Because you'll trip over every query, import and export that isn't dead
simple. Try to produce any mildly complicated query and you'll fall flat on
your face. You spent days searching and couldn't find out how to do what you
wanted with a simple append query on a table with multivalue fields, so you
had to ask here. Have you really got that much time to waste?

Your project doesn't require multivalue fields. It requires three tables
that represent a many to many relationship. There are only three kinds of
relationships, one to one, one to many and many to many. The most popular
one is one to many, but many to many is the next most popular one.
Relational db designers have been modeling these relationships for more than
30 years so your project's db model isn't a new paradigm nobody's ever seen
before. It can be modeled with three tables which represent a one to many
relationship between table1 and table3 and a one to many relationship between
table2 and table3. It's really 2 one to many relationships, and like I said
it's the most popular relationship there is.

Chris
Microsoft MVP

 

[Jeff Boyce]

Chris' response gives you a pointer. Search on one-to-many and many-to-many
with "relational" to find examples of how to model the relationships. An
example I frequently use (although woefully simplistic):

tblPerson
PersonID
FirstName
LastName
... (other person-specific info)

tblClass
ClassID
ClassTitle
ClassDescription
... (other class-specific info)

trelEnrollment
EnrollmentID
PersonID
ClassID
EnrollmentDate
... (other enrollment-specific info)

If "relational" and "well-normalized" are not familiar terms, you'll need to
address THAT learning curve before you start building table structure.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[JLPerez]

Thanks Jeff.. I'll take that into consideration....