Records that fall between two dates? 1

[LD1010]

I posted this question last week. Still having problems.

I have a database that stores information on vacation time taken by employees. The two Date/Time data type fields [FirstDayOff] and [ReturningOn] record the vacation days. Days between those two dates are counted vacation days taken by the employee.
I need help writing the criteria for a query that will return the records of all employees who were off between any two date parameters, [Report Start Date] and [Report End Date] entered by the user.
Any help would be much appreciated.

Golom (Programer) responded with the following post.

There are several possibilities for this.

The employee days off may

- begin before, and end after the report interval.
- begin between the report dates but end after the end of the report.
- begin before the report start but end within the report interval.
- begin and end within the report interval.


CODE
Select EmpID
, DateDiff("d", [Report Start Date], [Report End Date]) As Days
From myTable
Where [Report Start Date] >= [FirstDayOff]
AND [Report End Date] <= [ReturningOn]

UNION

Select EmpID
, DateDiff("d", [FirstDayOff], [Report End Date]) As Days
From myTable
Where [Report Start Date] <= [FirstDayOff]
AND [Report End Date] < [ReturningOn]

UNION

Select EmpID
, DateDiff("d", [Report Start Date], [ReturningOn]) As Days
From myTable
Where [Report Start Date] > [FirstDayOff]
AND [Report End Date] >= [ReturningOn]

UNION

Select EmpID
, DateDiff("d", [FirstDayOff], [ReturningOn]) As Days
From myTable
Where [Report Start Date] < [FirstDayOff]
AND [Report End Date] > [ReturningOn]

This was helpful to me in that it provided a count of the number of vacation days for each employee who had taken time off. But this returned all the records and I need to limit the records returned to only those employees who were off during the report interval. If for example the parameters for the report were [Report Start Date] 10/2/2006, [Report End Date] 10/6/2006.

It would return records like the following:
EmpID FirstDayOff ReturningOn
1 9/30/2006 10/4/2006
(Off on the 2nd, 3rd)
2 10/2/2006 10/7/2006
(Off on the 2nd, 3rd, 4th, 5th)
3 10/3/2006 10/5/2006
(Off on the 4th)
But it would exclude records like the following:
4 9/30/2006 10/1/2006
(Dates are before report interval)
5 10/7/2006 10/12/2006
(Dates are after report interval)

 

[dhookom]

I would create a table of dates [tblDates] with a single date field [TheDate] and add records for all possible dates. This can be done with a little code or entry into Excel and then paste appending into Access. You can then create a query of all days off for all employees like:

SELECT myTable.EmpID, myTable.FirstDayOff, myTable.ReturningOn, tblDates.TheDate
FROM myTable, tblDates
WHERE (((tblDates.TheDate) Between [FirstDayOff] And [ReturningOn]))
ORDER BY myTable.EmpID, tblDates.TheDate;

You can then use this query as the source of a query that filters the records to between two dates.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[LD1010]

Thanks! dhookom

That worked great. Only one problem now. If the employee was off 2 days during the report period it will return 2 duplicate records, if off 4 days, 4 dupicate records, ect. I'm just learing Access and I can't figure it out. I tried to add DISTINCT and also DISTINCTROW to the SQL as per faq701-3274, but either way it still returns the duplicate records.

Thanks again for any help you could offer.

 

[dhookom]

I'm not sure what you want the result to look like. My suggested SQL will return one record per day that a person has off. If you want a count, you will need to use something like the following where the query mentioned earlier is named "qcarEmpDaysOff":

SELECT qcarEmpDaysOff.EmpID, qcarEmpDaysOff.FirstDayOff, qcarEmpDaysOff.ReturningOn, Count(qcarEmpDaysOff.EmpID) AS CountOfEmpID
FROM qcarEmpDaysOff
WHERE (((qcarEmpDaysOff.TheDate) Between [Enter Start] And [Enter End]))
GROUP BY qcarEmpDaysOff.EmpID, qcarEmpDaysOff.FirstDayOff, qcarEmpDaysOff.ReturningOn;

or

SELECT qcarEmpDaysOff.EmpID, Count(qcarEmpDaysOff.EmpID) AS CountOfEmpID
FROM qcarEmpDaysOff
WHERE (((qcarEmpDaysOff.TheDate) Between [Enter Start] And [Enter End]))
GROUP BY qcarEmpDaysOff.EmpID;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[LD1010]

Thanks again! dhookom

The first SELECT statements is exactly what I needed. I know I've got a lot to learn, but I'm begining to have hope. You guys are a tremendous resource!!