Report based on a single result coming from multiple fields
I have a database set up for tracking analytical work in our laboratory. We keep records based on a "request' for testing, which may have many samples in it. Within each sample, there may be many types of testing requested. I have set this up in a portal as shown here. Each test can have a different analyst assigned to it, and each test can have it's own due date. Each test can also be listed as "pending" or "completed."
I need to be able to generate a report of all work that is still active in the lab, and that will need to be pulled from the Status of Analysis 1-6. I know I can limit the samples that show based on the analysis status, but don't know how to not show any Analysis that has already been completed. I would prefer to have the report show only those items that are still active, and not tests that are already done (even if they are for a request that is not complete).
Similarly, we need to have a report of all work that is assigned to an analyst. That data will be pulled from the Analyst 1-6. I know how to limit the records to those that are assigned to a particular analyst, and then report on those. The problem I have is that I also want to limit the tests that are shown on the report to those assigned to that analyst (for instance, if Analysis 1 and 2 are assigned to RM, then Analysis 4-6 should not show on this report). Since any analyst can have work in any Analysis slot, I am not sure how to do this.
I am open to better suggestions for how to set this up, if that is the best way to do it. (we just started using this, and I can still move things fairly easily) Otherwise, I look forward to suggestions for how to modify the reports I have.