query or code?

[davidoc]

I have a database containing absence information for employees. The table has a number of fields including employee_num, FromDate, ToDate. there are usually a number of records per employee.

I want to set up a query to pick out an employees records that overlap: i.e. the fromdate of one record is before the todate of another. I want the query to do this for all employees.

Can this be done with a query or do I have to start looping with VBA?

any help would be much appreciated

 

[namliam]

A query can do it...

Simply add the Employee table twice to the query and link the employee ID
Then do where todate of the 1st table <= From date of the second table.

Hope its clear enough

Greetz

 

[davidoc]

I've already tried a few variations on that to no avail.

heres an example of what I am looking for:
empnum start finish
001 02/04/2003 04/04/2003
001 01/04/2004 06/04/2004
001 14/04/2004 16/04/2004
001 19/04/2004 20/04/2004
001 28/04/2004 30/04/2004

I want the query to give me the first two records only (these are the only ones that overlap dates).

The PK is a field called recordnum

 

[namliam]

I created a table using your 4 fields and called it table2

Now use this SQL:

SELECT Table2.Recordnum, Table2_1.Recordnum
FROM Table2 INNER JOIN Table2 AS Table2_1 ON Table2.EmpNum = Table2_1.EmpNum
WHERE (((Table2.Start)<=[table2_1].[start]) AND ((Table2.Finish)>=[table2_1].[start]) AND ((Table2.Recordnum)<>[Table2_1].[recordnum]));
code]

This returns your offending key values only...

I saved that query as "Query5"

Then did this:
[code]SELECT Table2.EmpNum, Table2.Start, Table2.Finish
FROM Table2 INNER JOIN Query5 ON Table2.Recordnum = Query5.Table2_1.Recordnum;
union
SELECT Table2.EmpNum, Table2.Start, Table2.Finish
FROM Query5 INNER JOIN Table2 ON Query5.Table2.Recordnum = Table2.Recordnum;

To return both offending records...

Note: this is for reporting only, because the recordset will be "Not updatable"

Greetz

 

[davidoc]

It seems so simple now...

thanks for your help