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.

Any suggestions are greatly appreciated.


Jason Hendry

Try with a UNION query...something like:

SELECT [Master List].[ID #], [Master List].Status, [Master List].[Last Name], [Master List].[First Name], [Master List].[Start Date], "30Days" AS [Completed]
FROM [Master List]
WHERE ((([Master List].Status)="Active") AND (([Master List].[Start Date])<=Date()-30) AND (([Master List].[30 Completed]) Is Null))
UNION ALL
SELECT [Master List].[ID #], [Master List].Status, [Master List].[Last Name], [Master List].[First Name], [Master List].[Start Date], "90Days" AS [Completed]
FROM [Master List]
WHERE ((([Master List].Status)="Active") AND (([Master List].[Start Date])<=Date()-90) AND (([Master List].[90 Completed]) Is Null));

Welcome to Utter Access.

The first thing you need to do is to search Utter Access for topics on Normalization. Your present structure is a flat file and will cause troubles until corrected.

Here is one post with a link and a PDF file. A good starting place.

Thanks for the quick fix xteam. The union query you suggested did the trick. I toyed with it for awhile (first time I've seen a union query) and added some things that I thought might be useful to the report.

Thanks for the suggested reading truittb. I found it rather interesting and it gave me some ideas for ways to modify the design of my database to include even more information. Good stuff.


Jason