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:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Employee_ID[/TD]
[TD]Employee_Name[/TD]
[TD]Dept_Start_Date[/TD]
[TD]Dept_End_Date[/TD]
[/TR]
[TR]
[TD]EE101[/TD]
[TD]James Simpson[/TD]
[TD]01/01/2017[/TD]
[TD]27/03/2017[/TD]
[/TR]
[TR]
[TD]EE101[/TD]
[TD]James Simpson[/TD]
[TD]28/03/2017[/TD]
[TD]07/06/2014[/TD]
[/TR]
[TR]
[TD]EE325[/TD]
[TD]Michelle Brown[/TD]
[TD]01/01/2017[/TD]
[TD]22/01/2017[/TD]
[/TR]
[TR]
[TD]EE325[/TD]
[TD]Michelle Brown[/TD]
[TD]19/01/2017[/TD]
[TD]07/06/2017[/TD]
[/TR]
[TR]
[TD]EE687[/TD]
[TD]John Smith[/TD]
[TD]01/01/2017[/TD]
[TD]06/05/2017[/TD]
[/TR]
[TR]
[TD]EE687[/TD]
[TD]John Smith[/TD]
[TD]07/05/2017[/TD]
[TD]07/05/2017[/TD]
[/TR]
[TR]
[TD]EE687[/TD]
[TD]John Smith[/TD]
[TD]08/05/2017[/TD]
[TD]07/06/2017[/TD]
[/TR]
</tbody>[/TABLE]



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!