Create A Query that excludes Yes/No Field

[neil@ctcexpress]

Hi

I have created a query for telling me the dates when I need to check driving licences from my drivers.

I have also made a Yes/No check box for when they are 'Live' employees and another Yes/No check box for when they have left. I need to exclude the records from the query with the 'Left Employment' box checked ('Live' box un checked).

I need to keep the record for 12 months after they leave so I cant delete it.

Can anyone help?

Thank you

 

[bob fitz]

Can you show us the SQL statement for your query

 

[neil@ctcexpress]

SELECT [CTC LMA DA Table].ID, [CTC LMA DA Table].[First Name], [CTC LMA DA Table].Surname, [CTC LMA DA Table].[Next Passport Check Date], [CTC LMA DA Table].Hemel
FROM [CTC LMA DA Table]
WHERE ((([CTC LMA DA Table].[Next Passport Check Date])<Date()+28) AND (([CTC LMA DA Table].Hemel) Is Null))
ORDER BY [CTC LMA DA Table].[Next Passport Check Date];

 

[vbaInet]

I wonder what the difference between a 'Live' and a 'Resigned' employee is?
Does it mean that an employee may still be with the company but isn't currently live? Otherwise, shouldn't they just be one field.

 

[neil@ctcexpress]

Its the same field. They go through 3 stages. 1st tick box is 'Pre Reg Driver' 2nd is 'Live' and 3rd is 'Resigned' There is only 1 box ticked at once.

The field is still saved as 'Resigned' for 12 months.

Hope that helps.

 

[vbaInet]

You would be better off with one field and the three options.

 

[neil@ctcexpress]

Okay

How would I do that? Would it be the tick boxes or dropdown menus? And how would I then sort the query so it excluded the resigned people?

Thanks

 

[vbaInet]

Is this for a new db? If it isn't then don't follow the steps below.

Yes it will be a drop down list. You will need a new table.

1.
tblEmpStatus
----------------
ID (PK) - Autonumber
EmpStatus - Text

2. Save and open the table then enter the three employment status' into the EmpStatus field
3. Go back to your original table and create a new Number field
4. Save and close the table
5. Open the Relationships Designer and the two tables together enforcing Referential Integrity

NB: It should be done on a backup copy of course.