How to find multiple values in the one field?

[pigeon]

Hi all,

In my student database, I want to find students who have enrolled in two or more subjects.

If I do a find on the subject GOVT23 and "Add new request" to also find GOVT40, I will find any students enrolled in either subject.

How do I find the students enrolled in both subjects only?

Cheers.

[bcooney]

First, we need to know how you relate Students to the Subjects. Do you have a portal to a join table btw students and subjects.

I would guess that you could do a Find on the first subject, and then a constrain on the second.

[pigeon]

There is no portal used.

It is a very simple database with only 6 fields. The 3 student fields (student number/surname/given name) are not linked in any way to the subject field.

[bcooney]

You don't have any relationships? Do you duplicate a student record for each subject in which they're enrolled?

Is it too late to redesign this?

Anyway, constrain after a find should work.

[pigeon]

Thanks.

If I do a find on one subject and then choose Constrain Find, nothing actually happens.

What am I doing wrong in using this function?

[bcooney]

I think I mislead you. You have 3 fields for subjects on each student record? The more subjects that you enter, the more narrow the search.

However, if you weren't consistent in entering, and sometimes "Math" is in Subject Field1, and sometimes Subject Field2, then your Finds will not be accurate.

So, again, you need to design this file using relationships.

[Oldsneekers]

To find students enrolled in EITHER:

find mode, pause, (now the data is entered), perform find, find mode, pause, Now the second data is entered), extend found set.

OptionB: I like using globals for the find criteria (gFindField_A, gFindField_B, gFindField_C...) then the scrip would look like this:

First script (formated to a "find" button) opens a pop up (clear findfields using setfield. Open new window named Search.

Second script (formated to a Execute search button in the pop up) is close window, enter find mode, setfield:CourseName:gFindField_A, perform search, find mode, setfield:CourseName:gFindField_B, extend found set.

OptionC: make the course field a repeating field with 2-4 repetitions but only 1 repetition is displayed in your list of students/courses. The repetitions are used for the find only. If you elect to use the pop up for searching as in OptionsB above, script one is the same. Script two would be close window, enter find mode, setfield:CourseName/rep1:gFindField_A, setfield:CourseName/rep2:gFindField_B, perform search.

The find in the above case will find all students matching either criteria. If you don't like the pop idea, put the search fields in the header.

===

To find students enrolled in BOTH:

find mode, pause (to enter criteriaA in to the field CourseName), perform search, find mode, pause (to enter criteriaB) constrain found set.

OptionB: analogous to option B above without pauses but with global fields populated first then enter find mode et cetera.

NOTE!! it's been a long time since I used the repeating field method so it IS possible that I could be wrong and it finds only those recods matching BOTH rather than either.

I hope this was helpful.

[ashill]

Oldsneekers,

I realize this is a really old post but Option B worked like a charm for me. Thanks for posting.