Query - Multivalued fields (Reverse)

[jim may]

I am using the Multivalued field feature first introduced in Access 2007.

For Example if I have a simple table "Class Enrollment"
With fields:
ID
ClassName
StudentsEnrolled (this field is a multivalued field)

I have 10 classes (records) aready entered

Say, in records 2, 5, and 7 in the StudentsEnrolled - my name "Jim May" appears among 6 or 8 other names, like Bob Jones, Harry Potter, Jim May, Joe Blow

How can I write a parameter query so I could "run it" the parameter dialog box pops up saying "Enter Name" - I then enter: Jim May

and the results show the ClassName(s) for record 2, 5 and 7.

Meaning - What Classes am I currently enrolled in?

Thanks in advance for any help you can offer..

Jim

 

[Norie]

Jim

It's not too late, and it should be possible to 'fix' what you have so that the data is far more conducive to work with.

It might take some work but I really think it would be worth the effort.

Multi-value fields are just a bad idea - you can work with them but the effort needed to do so just isn't worth it.

The only thing I can think of to compare them to is merged cells in Excel.

Sorry if that sounds negative and it is just my opinion but I think you might find some positives if you were to have a rethink.

 

[jim may]

"merged cells in Excel" - Holy Crap!! That ain't good... (I understand that, as I work with Excel 99% of the time, but with Access, only 1% of my time..)

Access, to me is like the contents of a can of vegatable soup -- you open the can, pour it into a pot.. then someone says, hey - can you restore this back to it's original state?

That's the complexity of Access, to me -- too many ((unknown)) parts.

Thanks,

Jim

 

[Norie]

Jim

I think that m-v fields are kind of the 'vice-versa' of merged cells.

Merged cells are a way in Excel to store one piece of data in multiple cells.

An M-V field is a way to store multiple pieces of data in one field.

Maybe another way to look at it is that you would probably never store multiple values in a cell in Excel.

If you did imagine the fun you would have.

 

[jim may]

I must trust that Microsoft KNOWS what they are doing. Most of my career, thus far, is based on their success, and right-decisions.. Right? When multivalues were introduced in the 2007 release -- if enough BAD PRESS and Complaints had come back to them (MS) -- I doubt if they would have continued it the 2010 release.
I don't have the time to learn the "older" ways of doing things - if they offer something "newer", I have to take it !!.. LOL

 

[Montez659]

Jim,

Not that I am trying to change your habits, but like I said earlier, I do agree with Norie. We've had several discussions about the limitations of m-v fields, as a matter of fact, here is one from last week or so: http://www.mrexcel.com/forum/showthread.php?t=538536 .

I don't think that it is a matter of "newer" and "older" ways, just a matter of what is best practice. If you are working with a small db, maybe m-v fields can save you some time. But if your db starts to grow and you need more functionality (and one would need to think about whether this could happen or not when the db is first designed) then you would definitely want to go with a junction table.

And they really aren't too hard to set up. It took me only about an hour to convert an m-v field to a junction table with a minor error that was later corrected by Norie.

Anyways, just some stuff to think about. Btw, I used to use Excel for 100% of all my stuff because I could easily manipulate the interface via VBA. However, within the past month, I was forced to learn Access because of work. Now I will never go back! (Ok, I will because they are two different programs with two different purposes ). Glad everything worked out well, and blessings back to you!

 

[Norie]

Just passing through, but I thought I might mention that multi-valued fields have been around for quite a file.

Even longer than Access I think.

Also, I think multi-value fields can be useful for certain types of databases/data.

I think MS introduced (re-introduced?) is somehow connected with Cloud computing or SharePoint.

 

[jim may]

Norie,

Thanks for all your comments.

Jim May