I'm building a database for a laboratory setting. The operators perform 20 test, and input the data into a form. The table in which it is entered is roughly as below:
DATE | TIME | Test 1 |Test 2 | ... | Test 20
I'm trying to build a form for the supervisor to search for specific ranges with in the tests. Ideally, I'd like for them to enter a date range (via two unbound text boxes - startDate/endDate), and then have text boxes for them to enter the data range they are searching for via other text boxes (StartTest1/EndTest1, StartTest2/EndTest2, ... StartTest20/EndTest20). I was going to build a query in query design view and use the criteria for each field as Between [startDate] and [endDate], then for the fields would be Between [StartTest1] and [EndTest1] Or [StartTest1] Is Null And [EndTest1] Is Null. Sometimes the supervisor will search ranges for one test, but sometimes he/she may need to refine the search based on serveral test. My assumption (and this has worked on a smaller scale) is that this would search the table for records between the dates and with values between whatever information was inputted, unless they were left blank, then it would simply return the records between the inputted dates. If two fields have a start and end value then it would refine the search to fit those criteria with the others left null, and so on. Is there a better way to creating a query to search for this data? When I use the criteria above for more than 8 fields the query seems to lock up.
The query created is to be used to create value in a listbox, which when selected would find that record and allow for the supervisor to edit the record, and also used to create a graph and report.
Any help, or resources to help with this problem would be greatly appreciated.
EDIT: I could possibly get this to work by making separate queries to perform the date range, then a query based on the date range query for test 1-5, then another query based on the test 1-5 for test 6-10... etc... but I assume this would be inefficient