I need to create a recordset (or two) 1

[MADDPM]

I have the following tables: EMPLOYEE and CONTRACT – one to many – joined by Employee_Number

In my database, the user screen is composed of a main Employee form - info on the left side of the user's screen, and a Contract subform – continuous form and appears on the right side of the user's screen.

The Main Employee form has a calculated control: "Employment_Status" based on whether or not the "Employment End Date" control is null or not – null=Active; not null=Inactive.

The Contract subform has a similar calculated control "Contract_Status": IIf(Date()<[Begin_Date_this_appt],"Pending",IIf(Date() Between [Begin_Date_this_appt] And [Expire_Date_this_appt],"ACTIVE","EXPIRED")) AS Active_Calculated

Unfortunately, I cannot base the Main form "Employment_Status" control on whether or not there is an "active contract" since some Employees are still considered active although they may not have an active contract within our division.

What I am trying to do is find and review status mismatches which happen occasionally. I need to create a recordset based on the following: If "Employment_Status" control is "Active" and if "Contract_Status" control is "Active" – all records (including the "expired" contract records) for that employee should be ignored.

And then I need a reverse recordset: Those Employees with a "Contract_Status" of "EXPIRED" for ALL of their contracts - but have an "Employment_Status" of "Active".

Can this be done? Or perhaps this should be submitted to the query forum? Any help will be greatly appreciated.
-Colleen

 

[MajP]

1. Build an Employee and Contract query with your with your "active" calculations in the query as calculated fields. "qryEmployeeStatusContractStatus"

2. If "Employment_Status" control is "Active" and if "Contract_Status" control is "Active" – all records (including the "expired" contract records) for that employee should be ignored.

So build a query "qryIgnore" that returns the employee ID for any employee where employment Status = active and contract_status = active.

Now you want to exclude any employee records that are NOT IN gryIgnore.

3. You then can use a subquery
Select fields from some query where employeeID NOT IN (Select EmployeeID from qryIgnore)

4. In your second question you say Employees not Employee contracts. This should be a simple select query using the query ""qryEmployeeStatusContractStatus""

Select Distinct Employee from qryEmployeeStatusContractStatus where
EmploymentStatus = "Active" and Contract_Status = "Expired

 

[MADDPM]

Perfect!
Thank you.
-Colleen