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

 

[Montez659]

Jim, I can offer you the logic of what you are trying to do, just not the code (cause I am at work and don't have time to check it ).

Essentially, if you run a query on the table in its entirety, you will receive back multiple "records" for each primary key because of the m-v field. So then you would just want to limit that query by some type of parameter - in this case a textbox on a form in which you enter the name. At this point, you can spit out a report with the pertinent info (maybe grouped on contactName) and run the report off of the query. This will return all info for the name you specified.

Hopefully, this gets you started on the right track. If you are needing more concrete SQL statements and the such, I will get back with you as time permits.

 

[jim may]

I created a Query from my Table alone.

I included all the fields. I ran it and the 10 records from the table expanded to 32 rows allowing for the unique multivalued fields.

I then In the Criteria row (in Design view) entered:

"Jim May" --- then RUN

Got Error:
Data type mismatch in criteria expression. (Error 3464)

The criteria expression in a Find method is attempting to compare a field with a value whose data type does not match the field's data type.

Thanks,

Jim

 

[Montez659]

When you add the names to the class, do you add the physical name or is there a table of students and you select from a combo box or type a new name in?

In essence, is the control that you use to select a student bound? If it is then I am assuming all of your information is running off of the primary key (PK) as opposed to the actual name.

Under that circumstance you would need to search for the ID (which is more than likely an autonumber) but you are looking for a string ("Jim May") hence the type mismatch.

As a test, search for a number value (like "1") instead of Jim May in the query field and see if that returns something.

 

[Norie]

Jim

Have you considered not using m-v fields?

What you have sounds like a many-to-many relationship between class and student.

That is usually best handled by using a 'junction' table which represents that relationship.

Each record in that table would have the primary keys of the student and class tables as foreign keys.

With that setup it would be straightforward to create a query to return all the classes a student was enrolled in and vice versa.

 

[jim may]

Norie,

No, it's too late -- I suppose I assumed that if MS offered the Multi-valued field it could perform basic tasks -- regardless of all the critism coming from the "old-guard". LOL

If in the Criteria Line I enter the PK of the Students versus their Names, it Works!!!
So How can I enter into "Jim May" - have Access internally, say Oh, Jim May is ID (PK) 8, and then feed the 8 into the criteria field before the query is run?

Thanks for the Help -- Jim

 

[Montez659]

Yeah, that was going to be my next suggestion. I know from experience (and from Norie talking me down ) that m-v fields are a huge pain. I just use junction tables for everything these days (I say that like it wasn't last week when I was looking for help on m-v fields!).

 

[Montez659]

Jim, you can set up a form with a combobox control populated by the student table. The bound column will show the ID, but you can hide this by setting the first column to a 0 width. Make sure that you have at least two coumns showing. Create a command button to run the query and in the query criteria reference the combobox valuelike [forms]![yourFormName].[yourComboBox].

 

[jim may]

Montez659 - Thanks for being so positive and giving me such absolute detailed instructions. I'm all over this (with excitement) !! - I'll be a while before I can get this done.. I'll let you know the results as soon as I know -- THANKS, Again Jim

 

[jim may]

Montez659;;;;

I GOT IT ;;; I GOT IT !!!

But only with your help (and Positive attitude)!!

Bless YOU!!

Jim