Counting Personnel on Leave

[Guest]

Hello all,

I use an Access 2000 database to track the comings and goings of military
personnel while they are assigned to my office. When one of my staff members
requests to take leave (vacation), I create an entry in my 'Authorized Leave'
table that tracks the name of the person, the date/time their leave will
begin and the date/time their leave will end.

I would like to create a query (to be the basis of a line graph) that tells
me, for each day over the next 90 days, how many personnel will be on leave
for that particular day. That way I can set an arbitrary threshold (say, 25%
of all personnel) so that I can see at a glance whether or not I can
authorize somebody's leave request and still have enough people around the
office.

No idea how to write this query. Any suggestions are appreciated!

Thank you,

LT J. B. Stroble

 

[Tom Ellison]

Dear Lt. Stroble:

The query to do this would require a table you may not yet have. This would
be a table containing all the dates over that 90 day period (actually this
table would eventually contain more dates than that.)

A cross product of this table with a table of all staff members forms the
framework into which a COUNT() of all personnel to be absent on the dates
specified in the table specifiying leaves would complete the query. Making
a graph from this we can cover later.

I could work with you to provide this, but I would need considerable details
in doing so. Would you like to send me the database you have so far? It
does not need to contain details of the actual personnel, but some kind of
sample data would be useful.

Please let me know so I can give you instructions before sending anything.
My private email is the one used in this post.

Tom Ellison

 

[Darren]

You could create a query that returns a 90 day range of data using Union
queries, then use it to query your leave data.

The "90 day" query would look quite clumsy, but effective.

ex

Select aDate from
(
Select top 1 Date() as aDate from [myTable]
Union
Select top 1 Date()+1 from [myTable]
Union
Select top 1 Date()+3 from [myTable]
Union
Select top 1 Date()+4 from [myTable]
)

etc.

Just add unions for as many days as you want.

 

[John Vinson]

Hello all,

I use an Access 2000 database to track the comings and goings of military
personnel while they are assigned to my office. When one of my staff members
requests to take leave (vacation), I create an entry in my 'Authorized Leave'
table that tracks the name of the person, the date/time their leave will
begin and the date/time their leave will end.

I would like to create a query (to be the basis of a line graph) that tells
me, for each day over the next 90 days, how many personnel will be on leave
for that particular day. That way I can set an arbitrary threshold (say, 25%
of all personnel) so that I can see at a glance whether or not I can
authorize somebody's leave request and still have enough people around the
office.

No idea how to write this query. Any suggestions are appreciated!

Thank you,

LT J. B. Stroble

You will probably want an auxiliary table, AllDays, with one date/time
field. Use Access or some simple VBA code to fill this table with one
record per day for the next ten years or so.

You can create a "Non Equi Join" query joining this table to your
Authorized Leave table:

SELECT Count(*)
FROM AllDays INNER JOIN [Authorized Leave]
ON AllDays.TheDate >= DateValue([Authorized Leave].[StartDate])
AND AllDays.TheDate < DateAdd("d", 1, DateValue([Authorized
Leave].[EndDate])
GROUP BY AllDays.TheDate
WHERE TheDate BETWEEN [Enter start date:] AND [Enter end date:];

This will count all records where the leave begins or ends at any time
during each day in the range you select.

John W. Vinson[MVP]

 

[Guest]

John et al,

Thank you for your replies, they were very helpful. I was able to use your
suggestions to make the query I was looking for (in part). My query so far
looks like this:

SELECT DAY_DATE, count(*) AS [Personnel on Leave]
FROM [Lookup: All Days 2006-2016] AS Days, [Event: Leave Authorization] AS
Leave
where DAY_DATE between now() and (now()+90)
and (Days.DAY_DATE >= Leave.LV_START and Days.DAY_DATE <= Leave.LV_END)
group by DAY_DATE
order by DAY_DATE;

This query provides accurate counts for every day within the next 90 days
for which there is at least one person on leave. However, it does not return
records where the count equals zero. Is it possible to modify my query so
that it returns exactly 90 records, including those days with zero personnel
on leave? I tried toying around with both a 'left join' and a 'union all'
but was not successful.

Thanks again,

Jon.

Hello all,

I use an Access 2000 database to track the comings and goings of military
personnel while they are assigned to my office. When one of my staff members
requests to take leave (vacation), I create an entry in my 'Authorized Leave'
table that tracks the name of the person, the date/time their leave will
begin and the date/time their leave will end.

I would like to create a query (to be the basis of a line graph) that tells
me, for each day over the next 90 days, how many personnel will be on leave
for that particular day. That way I can set an arbitrary threshold (say, 25%
of all personnel) so that I can see at a glance whether or not I can
authorize somebody's leave request and still have enough people around the
office.

No idea how to write this query. Any suggestions are appreciated!

Thank you,

LT J. B. Stroble

You will probably want an auxiliary table, AllDays, with one date/time
field. Use Access or some simple VBA code to fill this table with one
record per day for the next ten years or so.

You can create a "Non Equi Join" query joining this table to your
Authorized Leave table:

SELECT Count(*)
FROM AllDays INNER JOIN [Authorized Leave]
ON AllDays.TheDate >= DateValue([Authorized Leave].[StartDate])
AND AllDays.TheDate < DateAdd("d", 1, DateValue([Authorized
Leave].[EndDate])
GROUP BY AllDays.TheDate
WHERE TheDate BETWEEN [Enter start date:] AND [Enter end date:];

This will count all records where the leave begins or ends at any time
during each day in the range you select.

John W. Vinson[MVP]

 

[John Vinson]

This query provides accurate counts for every day within the next 90 days
for which there is at least one person on leave. However, it does not return
records where the count equals zero. Is it possible to modify my query so
that it returns exactly 90 records, including those days with zero personnel
on leave? I tried toying around with both a 'left join' and a 'union all'
but was not successful.

You'll need a LEFT JOIN, and you'll also need to do the link between
tables in the JOIN clause rather than the WHERE clause:

SELECT DAY_DATE, count(*) AS [Personnel on Leave]
FROM [Lookup: All Days 2006-2016] AS Days
LEFT JOIN [Event: Leave Authorization] AS Leave
ON (Days.DAY_DATE >= Leave.LV_START and Days.DAY_DATE <= Leave.LV_END)
WHERE DAY_DATE between Date() and DateAdd("d", 90, Date())
group by DAY_DATE
order by DAY_DATE;

Note that Now() doesn't return today's date - it returns the system
clock date and time accurate to microseconds. If that's what you want
- leave for the rest of this afternoon, and for the morning of the
90th day up to the current time - you can use Now() but it will be
subtly different for leaves beginning or ending today or on the 90th
day.

John W. Vinson[MVP]

 

[Guest]

Thanks John, this worked like a charm! Incidentally, I had to make a slight
change, from count(*) to count(Name), because count(*) was returning 1 for
those days which should have been 0 with no personnel on leave. Thanks again,

LT Stroble.

This query provides accurate counts for every day within the next 90 days
for which there is at least one person on leave. However, it does not return
records where the count equals zero. Is it possible to modify my query so
that it returns exactly 90 records, including those days with zero personnel
on leave? I tried toying around with both a 'left join' and a 'union all'
but was not successful.

You'll need a LEFT JOIN, and you'll also need to do the link between
tables in the JOIN clause rather than the WHERE clause:

SELECT DAY_DATE, count(*) AS [Personnel on Leave]
FROM [Lookup: All Days 2006-2016] AS Days
LEFT JOIN [Event: Leave Authorization] AS Leave
ON (Days.DAY_DATE >= Leave.LV_START and Days.DAY_DATE <= Leave.LV_END)
WHERE DAY_DATE between Date() and DateAdd("d", 90, Date())
group by DAY_DATE
order by DAY_DATE;

Note that Now() doesn't return today's date - it returns the system
clock date and time accurate to microseconds. If that's what you want
- leave for the rest of this afternoon, and for the morning of the
90th day up to the current time - you can use Now() but it will be
subtly different for leaves beginning or ending today or on the 90th
day.

John W. Vinson[MVP]