Confused on combining multivalued fields and junction tables

[alexandersalamander]

I understand that Access 2010 has a multivalued fields function, but my supervisor has told me not to use those.

Essentially I have EVENTS(eid,title,date,presenters) and PEOPLE(pid,fname,lname,role)

An event can have any number of presenters. I currently have imported the rawdata from excel, where the presenters field has comma delimited values (pid1, pid2, pid3).

How do I use junction tables to convert the multivalued data to a proper format?

Or am I thinking about this wrong?

Thanks so much.

 

[xenou]

Hi,
This is how to set up the junction table:

<img alt="screenshot" src="http://northernocean.net/etc/mrexcel/20130623_rels.jpg" />

The presenters go in the junction table.
(Yes, you have to clean it up a little since you don't want them all in a single field anymore)
--------------------
| eid    | pid     |
--------------------
| Event1 | Person1 |
| Event1 | Person2 |
| Event1 | Person3 |
| Event2 | Person1 |
| Event2 | Person2 |
--------------------

 

[alexandersalamander]

Okay, this makes sense, I can do it. In your example, what goes in the "presenter" field under Events? Or is it just not used anymore, since we established a relationship with the primary keys?

 

[xenou]

Hi,
Just not used anymore. The Event ID matches one or more people ID's in the junction table. Those are your presenters. If you prefer, you can label the field presenters:

Events_People(eid, presenter)

Just be sure to make the two fields a unique index (you should not have a person listed under the same event twice). Or make them a composite primary key (which amounts to the same thing).

-----
Note: Supposedly Access does create junction tables of some kind "under the hood" when you use a multi-value field. But they are probably a liability in situations where you want to interact with other databases that don't use m-v fields. Plus I'd advise understanding the "old school" methods too, since it's best if you understand how many-to-many is *really* resolved.

 

[HiTechCoach]

I understand that Access 2010 has a multivalued fields function, but my supervisor has told me not to use those.

I totally agree. Most Access expert would agree with your supervisor. That was wise advice.

IIRC, the multivalue field "feature" was added for the ability to have a Web Database that coudl be published to a Sharepoint Server.

FWIW: I would never use them. On the surface they sound good but they only work OK in a very few instances. IMHO, they violate good database design. I would not be surprised is they are dropped soon with the push in Access 2013 to use an SQL Server back end which does not support multivalue fields.