Form or Query Question?

[JeepsR4Mud]

I have a table, used in a form, that records general information about employees, including an employee ID number.

I also have a second table, called Duty Assignment, that is linked to the Employee table by the employee ID nmber.

The main form is the employee table, and the subform is the Duty Assignment, in datasheet form.

THe idea is to show the employee info, and duty assignments, coded by year, during the past fiscal year.

It is now time to update it for the new year, and the way I have it designed, in the form view, I have to go through EVERY employee record.

Is there a way to only show information on employees who had duty assignments in 2002?

I can do a query, and print out that information on a report, but I'm not sure how to do it with the subform in a form.

I thought about adding another field to the employee table called Current Employee, and have it a check box. I could then sort on it, and unclick people who have left. But, is that solution the BEST way to solve the problem?

Creating a query on the sunform only eliminates duty assignments prior to 2002, not the associated employee record.

Thanks so much.

Gayle Ann

 

[Jon K]

You can base the main form on a query like this:-

SELECT *
FROM Employee
WHERE [EmployeeID] in (Select [EmployeeID] from [Duty Assignment] where [YearField]="2002");


If the YearField is a numeric field, remove the quotes.