I've got a table that uses a form to collect quite a bit of info regarding my employees. Employees are evaluated at set intervals and I designed the form (small macro) so that it would automatically calculate the 30 day, 90 day, 6 month, 12 month etc. intervals based on the start date of the employee. All of this data is stored in the same table. I've added a field to the form and the table that is populated with a date once the particular evaluation is completed.
Here's the puzzle:
I can design a query that will select all employees who are overdue for a 30 day evaluation based on their start date, the current date, and whether the completion field ISNULL. This works like a charm.
What I want is a way to run a single Report that will pull all of the information for overdue evaluations. I need a way to write a single query that will test for each of the unique evaluation periods (30,90,6 month, 12 month, etc) and return all of the employees who need to be evaluated.
When I add multiple fields to the tests to the query, to check if the current date is greater than 30/90 days from the start date of the employee and if the completed evaluation fields for 30/90 days are populated, the query returns no employees. Basically, I've added the multiple "tests" but the only way that it would return any records is if an employee were overdue for ALL of their evaluations.
I've toyed with this for quite sometime and I know that it can be accomplished but, given my limited experience, I've been unable to solve the riddle.
I've attached a "bare-bones" shell of the project. You can see that there are seperate queries for identifying overdue 30 day evals and overdue 90 day evals. I'm searching for a way to collect both pieces of information in a single query OR a way to tie multiple queries into a single report that would show both categories.