I am working on a query for a client and I have hit a wall. I usually take great pleasure in figuring stuff like this out, but I am brain dead right now and running out of time.
I have two tables in a set. The first table has employee personal information and the second table has the employee's check history. The tables are joined by an employee ID in a one to many relationship. (One Employee Information Record, Many Check Records) The information table has the date of hire and the checkhistory table records the checkdate, amount, etc.
The question is we are looking for all employees who were hired only in 2005, but have not worked since the end of 2005. We are looking to archive records of employee's who have not worked for us for at least three years.
We will then run the query for employees hired in 2004, but have not worked since the end of 2005 and so on. The reason why I am doing it year by year is because we will also be removing the associated paper records and they are stored alphabetically by year.
The query I thought would work is:
Condition 1: Entrydate in applicants is in the year 2005 AND
Condition 2: (Exclude) checkdate is after 12/31/2005
I built the query in the query builder and the resulting query is:
(year(Entrydate) = 2005 ) .and. .not. ((Checkhistory->Checkdate > ctod("12/31/2005") ))
Unfortunately I think this query only removes the records where the checks from employees are after 12/31/2005, it does not give me a list of employees who have not received a check since 2005. The strange thing is the check records from 2006 are displayed in the default form, but the record count did go lower.
The client I am doing this for is an agency which during the good days were employing 20,000 people a year. Even though many of them only worked 1 or two weeks. But we still send out over 20,000 W2's each year. The Information for the W2's are gleaned from the same tables.
Thank you very much for any help that is offered. I hope this is an easy question for someone and I am just blind to the easy answer.
Any help would be greatly appreciated.
I have two tables in a set. The first table has employee personal information and the second table has the employee's check history. The tables are joined by an employee ID in a one to many relationship. (One Employee Information Record, Many Check Records) The information table has the date of hire and the checkhistory table records the checkdate, amount, etc.
The question is we are looking for all employees who were hired only in 2005, but have not worked since the end of 2005. We are looking to archive records of employee's who have not worked for us for at least three years.
We will then run the query for employees hired in 2004, but have not worked since the end of 2005 and so on. The reason why I am doing it year by year is because we will also be removing the associated paper records and they are stored alphabetically by year.
The query I thought would work is:
Condition 1: Entrydate in applicants is in the year 2005 AND
Condition 2: (Exclude) checkdate is after 12/31/2005
I built the query in the query builder and the resulting query is:
(year(Entrydate) = 2005 ) .and. .not. ((Checkhistory->Checkdate > ctod("12/31/2005") ))
Unfortunately I think this query only removes the records where the checks from employees are after 12/31/2005, it does not give me a list of employees who have not received a check since 2005. The strange thing is the check records from 2006 are displayed in the default form, but the record count did go lower.
The client I am doing this for is an agency which during the good days were employing 20,000 people a year. Even though many of them only worked 1 or two weeks. But we still send out over 20,000 W2's each year. The Information for the W2's are gleaned from the same tables.
Thank you very much for any help that is offered. I hope this is an easy question for someone and I am just blind to the easy answer.
Any help would be greatly appreciated.
Comment