I think you should restructure your table definitions to add a new related table for your list of modules and time stamps. This is a much more flexible approach and coincidentally will make your query work quite simply where getting what you want with this list of individual fields becomes quite complex.
With a related table where each record has one module field and one TimeStamp field, you can put a portal to this new table on your layout and then you can enter search criteria in the timestamp field and it will bring up any parent record with at least one related record that matches to your criteria.
To do the same with your separate fields takes one of two rather tedious approaches:
1) enter find mode and specify the criteria in m1t. Select New Request from the requests menu and enter the same criteria into m2t. Repeat for each additional field. Perform your find.
2) Perform the find using criteria in m1t. Return to find mode, specify the same criteria in m2t. Select Extend Found set. Repeat for each additional timestamp field.
Thanks for the quick response!
I think I am a bit fuzzy about how I would set up the related table and tie that into my parent table search.
This database has a form layout front end which allows people to do canned queries and this would be one of them. So the user clicks on 'search by module and date' button which takes them to another layout with search criteria being 'start date' and 'end date'...the two dates the results should be confined within....then this takes them to a results page that sorts the results by test title subheader with (name) (over all score) (module1) (module 2), etc.....
If I were to start a new table and put just one Module feild and one Time Stamp field...how wold I be able to relate the results to the parent record or differentiate between modules? Are you saying that this second table should have a row for every module, for every class? thus 10 rows entries for every course and for every person?
student name | Course title | Module | Time Stamp
Bob Smith | course name| m1 | m1t
Bob Smith | course name| m2 | m2t
Then the search would search 'Time Stamp' column?
Something like this? Sorry, I am relatively new to this :(
You are on the right track, but you should not have a column for the student name. Student names are not unique and subject to change. They can also be entered incorrectly and then fixing the error can create problems if you have related records that match to the incorrect name.
Replace the name field here with a number field. Add a serial number field to your original table. (And use Replace Field Contents to assign a seril number ot any existing records in your original table.) LInk it to the number field in a relationship like this:
Maintable::StudentID = Modules::StudentID
IF the same student record can be linke to multiple courses, a different relationship would be needed in order to match by course and student.
You'll want to look up "portal" in FileMaker help and any training materials that you have if you are unfamiliar with them.
okay..Thanks for the help...we give a student ID number which will probably suffice and is already attached to each student record. I will look what you say and see if I can't figure this out. Again, thanks for taking the time.