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

 

[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....