select query for active personell during certain period (1 Viewer)

[wilkob]

I would like to make a query which is based on a table that holds information on employees (past and current)

I take the fields:

Name
Entry Date
Exit Date


and now would like to write a query where I can select a start and end date and the outcome should then be the "active" employees during this time.
for example between 1-7-2007 and 31-7-2007

I can not get my head around the criteria as the dates lie in 2 fields.

The exit date field can be empty or have dates that lie outside the range (see above) like the end of a contract at 30-10-2007

Hope someone can give me a tip to get me on my way.

 

[ajetrumpet]

and now would like to write a query where I can select a start and end date and the outcome should then be the "active" employees during this time.
for example between 1-7-2007 and 31-7-2007

SELECT()
FROM()
WHERE [employees]="active" AND 
[start date]>=[parameter1] AND 
[exit date]<=[parameter2];

The exit date field can be empty or have dates that lie outside the range (see above) like the end of a contract at 30-10-2007

...Conflicts with your first quote...to include empty exit date fields...

([exit date]<=[parameter2] OR [exit date] IS NULL)

OR...

Nz([exit date]<=[parameter2])

 

[wilkob]

SELECT()
FROM()
WHERE [employees]="active" AND 
[start date]>=[parameter1] AND 
[exit date]<=[parameter2];

...Conflicts with your first quote...to include empty exit date fields...

([exit date]<=[parameter2] OR [exit date] IS NULL)

OR...

Nz([exit date]<=[parameter2])

Sorry I was probably not clear. Let me use an example to further explain:

Name: John Entry Date: 1-1-1994 Exit Date:
Name: Jack Entry Date: 1-8-2007 Exit Date:
Name: Jill Entry Date: 1-1-2007 Exit Date: 15-7-2007

What I would like to see is that when I ask for the employees that were working for use in the period 1-7-2007 till 31-7-2007 that the query would show John and Jill

This way I can build a table that stores all the employees that have ever worked for our company and I can keep track of how many employees I had (on certain departments) during a period.

 

[ajetrumpet]

Name: John Entry Date: 1-1-1994 Exit Date:
Name: Jack Entry Date: 1-8-2007 Exit Date:
Name: Jill Entry Date: 1-1-2007 Exit Date: 15-7-2007

What I would like to see is that when I ask for the employees that were working for use in the period 1-7-2007 till 31-7-2007 that the query would show John and Jill

Then you would manipulate the criteria SQL to reflect what you want...

WHERE [entry date]<=[parameter1] AND
[exit date>=[parameter2]

parameter = query parameter/control reference

 

[Jon K]

I take the fields:

Name
Entry Date
Exit Date

and now would like to write a query where I can select a start and end date ..................................

The exit date field can be empty or have dates that lie outside the range (see above) like the end of a contract at 30-10-2007

To deal with empty exit dates, you can use the Nz() function.

If by Exit Date you mean the contract end date 30-10-2007 i.e. the last date of employee, the following query should do the job:-

PARAMETERS [Enter start date] DateTime;
SELECT *
FROM [TableName]
WHERE [Entry Date]<=[Enter end date] and Nz([Exit Date],[Enter end date])>=[Enter start date]


However, if Exit Date is stored with 31-10-2007, you need to adjust for 1 day in the criteria, though for this kind of database, we normally store the contract end date 30-10-2007 as the LastDate of employee because 31-10-2007 is unrelated to the employment.
.

 

[wilkob]

Jon,

This does the trick.
Thanks !!