Trying to find totals between dates

[HMJ]

I am not sure if this is the right forum, but I will try here first. We have a table that includes the fields:
full_name
employee_ID
start_date
end_date
What we are tracking - or trying to track - is leave information and how many are on leave for a particular day. We can only allow so many individuals off per day in order to maintain operations.

What we would like to do is:
(1) Have a supervisor input a start date and end date for a requested leave.
(2) Display the total number of individuals that will have off during these dates. (i.e. june 24 = 4, June 25 = 1, etc.) This info comes from the table above.

How can we do this with only a start and end date given in the table and the request?

Thank You in advance. Harry Jessen
HMJessen@Yahoo.com

 

[FancyPrairie]

Try a query something like this:

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT Count(*) AS Total
FROM Table1
WHERE (((Table1.dtestart)>=[Start Date]) AND ((Table1.dteend)<=[End Date]));

 

[HMJ]

FancyPrairie

Thank You for the info; however, how do I do this?

I am not as familar with Access as some, and this one got away from me.

Thanks Again. Harry Jessen
HMJessen@Yahoo.com

 

[FancyPrairie]

Open a New query via the Query Analyzer and switch to SQL view. Then highlight the SQL statement in my previous post, copy it (ctrl+C), and then paste it (ctrl+V) into the query.

The Parameter statement is there to prompt the user for a &quot;Start Date&quot; and an &quot;End Date&quot;.

Select Count(*) as Total returns a value that indicates the number of records that fall within the date range specified.

From Table1 (Instead of Table1, put the name of your table)

WHERE (((Table1.dtestart)>=[Start Date]) AND ((Table1.dteend)<=[End Date])); will get only those records that fall within the dates specified. Change Table1.dteStart to the name of your table and field name. Also, change Table1.dteEnd to the name of your table and field name.

Now run the query. It should prompt you for a begin and end date and then display the number of records that match.

 

[HMJ]

FancyPrairie

Tried your SQL script, and it works - somewhat. If I ask for dates June 1 through June 15, it tells me the total number of individuals on leave during that time frame. However, I need to know the number for each individual date.

If I sent you a short version of what I am doing, would it help? If so, I need your email address. You can contact me as indicated in my signature below.

THANKS!! Harry Jessen
HMJessen@Yahoo.com