Interrogating a subset within a dataset

[Ivan Howard]

Hello all,

I'm struggling with an Access (2010) issue and if someone could help me I'd really appreciate it as it is driving me nuts!

I have a 10000+ record dataset that has multiple records per person. The dataset looks like this:

Employee_ID	Employee_Name	Dept_Start_Date	Dept_End_Date
EE101	James Simpson	01/01/2017	27/03/2017
EE101	James Simpson	28/03/2017	07/06/2014
EE325	Michelle Brown	01/01/2017	22/01/2017
EE325	Michelle Brown	19/01/2017	07/06/2017
EE687	John Smith	01/01/2017	06/05/2017
EE687	John Smith	07/05/2017	07/05/2017
EE687	John Smith	08/05/2017	07/06/2017

<tbody>
</tbody>

What I need to do for EACH person is validate that the "Dept_Start_Date" on record 2 is before the "Dept_End_Date"s on record 1, and so forth. One person could have 12 records.

In the case of Michelle Brown above, the second record has an earlier start date so would like to be able to show those "errors" in a query so that they can be manually checked and updated if needed. Happy to have an additional TRUE/FALSE field to update if an "error" is found so that I can isolate the records to check.

I'm not sure if standard queries can be used or if VBA has to be used as it is easier and probably more efficient... I'm happy with VBA but just don't know how to get to the results.

Any help would be fantastic.

Thanks very much.

 

[ranman256]

you need a series of validation queries, in a macro

Q1:
SELECT tData.Employee_ID, tData.Employee_Name, tData.Dept_Start_Date, tData.Dept_End_Date, "EndDate is earlier thatn StartDate" AS [Note]
FROM tData
WHERE (((tData.Dept_End_Date)<[Dept_Start_Date]));


Q2:
SELECT tData.Employee_ID, tData.Employee_Name, tData.Dept_Start_Date, tData.Dept_End_Date, tData_1.Dept_Start_Date, tData_1.Dept_End_Date, "Overlapping Ranges" AS [Note]
FROM tData INNER JOIN tData AS tData_1 ON tData.Employee_ID = tData_1.Employee_ID
WHERE (((tData.Dept_Start_Date)<[tData_1].[Dept_Start_Date]) AND ((tData.Dept_End_Date)>[tData_1].[Dept_Start_Date]));

etc...

 

[Ivan Howard]

Thanks very much Ranman256. This is what I needed! All the best!