Going to a specific record after a search

[PBrooks]

This may be long but want to make it clear.

Database application that is designed to let the user pick a set of subjects and then is presented with questions about each subject with multiple choices on each screen.

Single table with a self-join - Fields are "Subject", "Category (subsets for each Subject)" and then Question 1 through Question 5 all in the same record and presented on a single layout.

The user is first presented with a "Subjects' layout - they pick pick the "Subjects" from a set of check boxes presented through a Value List.

A Find is then executed through a script (does an OR search) and that produces a Found Set of records that the person then walks through (going from record to record) answering the multiple choice questions until finished.

Question is - I would like an efficient way to go to a specific question by having the user indicate whether they want to go directly to the beginning set of questions via Subject alone, or by choosing Subject, then Category and going to the first of those records. THE FOUND SET MUST BE RETAINED AT ALL TIMES - I just want them to find and navigate to the specific question. Also, if the Subject choices are presented as a drop-down, the dropdown for Subjects must be restricted to the Subjects present in the Found Set only, not showing all the Subjects which is standard Value List behavior when based on a specific field.

I have been able to do the drop down restriction by creating a another table and populating it with the Found Subjects only and basing the Value list on this and then restricting the Categories to the related table (self-join) and showing only the Categories related to that Subject (Cascading Value List) but the navigation part is tricky.

Any ideas on how to do this relatively easily and without taking a tremendous performance hit having to do things like sequentially look at all records in a table? (Can't post the file since developing this program for commercial use and has proprietary data)

Thanks in advance.

--Peter Brooks

[comment]

Single table with a self-join - Fields are "Subject", "Category (subsets for each Subject)" and then Question 1 through Question 5 all in the same record and presented on a single layout.

I think this could be a lot easier if you had dedicated tables for Questions, Subjects and Categories.

[PBrooks]

Possibly but that will have to wait for next major revision of the program. Is there any way to do what I am attempting without majorly rewriting my application?

[comment]

Probably - if you use relationships instead of finds, and show the results in portals. However, setting this up with a single table might be more work than setting it up the right way.