Access Date Query

[Erm]

I am sure this isn't terribly complicated, but I can't even think where to start at the minute! Basically I have a database which consists of two tables. One with data about employees and another which may have several related rows about an employees absence. Below are the fields in each table that matter for this purpose:

EmpData
EmpNo (Employee Number)
Name
DOB
etc.

AbsenceData
RefNo
EmpNo (Employee Number)
StartDate
EndDate

The primary keys are in italic and the table names are in bold. They are linked by EmpNo and there can be several records in the AbsenceData table for each Employee.

I need to be able to enter a start and end date into a query eg. 01/06/04 and 30/06/04 and have a list provided which summarises all employees who have had time off in this period and how many days absence they have had (based on a working work of Mon-Fri) excluding weekends.

Any ideas?

 

[Exodus]

Check out Pat Hartman's database example Useful date functions. In the sample forum.

 

[Erm]

I already did, it doesn't have anything like this from what I can see. Thanks anyway. Any other ideas?

 

[Mile-O]

EmpData
EmpNo (Employee Number)
Name

Name? That's a reserved word in Access.

Forename
Surname

 

[Erm]

oh right, lets make it forename and surname then...

 

[Mile-O]

Personally, I wouldn't have StartDate and EndDate for Absence. I'd store each day of an absence as an individual record.

 

[Erm]

But couldn't you end up with hundreds of records for a member of staff on long term sick leave?

 

[Exodus]

Over time you could and will but access can handle it.

 

[Erm]

I need to stick to the same format if at all pos. Could it be done something like this:

If [Enter start date]>=SickStart and [Enter end date]<=SickEnd then AbsentDays = ([Enter end date]-[Enter start date])
else if [Enter start date]>=SickStart and [Enter end date]>=SickEnd then AbsentDays = (SickEnd - [Enter start date])
else if etc...for the other 2 combinations

I am trying do do this for a query...any ideas?

 

[Mile-O]

Are you counting non working days as absence?

 

[KenHigg]

Personally, I wouldn't have StartDate and EndDate for Absence. I'd store each day of an absence as an individual record.

This sounds like a good idea.

 

[Erm]

mon-fri only counted as absence

 

[KenHigg]

Personally, I wouldn't have StartDate and EndDate for Absence. I'd store each day of an absence as an individual record.

If you don't do this, you will be coding work arounds for ever (Only my humble opion).

 

[Erm]

I totally agree that that would be best Ken, but unfortunately I do not have the option open to me to change the design.

 

[Mile-O]

I totally agree that that would be best Ken, but unfortunately I do not have the option open to me to change the design.

Why not? If this is some ignorant superior saying they want it to look some way then it can still look that way but be engineered to work properly.

i.e.

Put start and end date in an run a function to input all dates in that range into the table.

 

[Erm]

The system is already in place and I cannot change the table structure.
...
The query I have done is looking for records where the SickStart and SickEnd are both >[Enter start date] And <[Enter end date]. Which will obviously exclude records with dates outside of these.

If I do a varity of queries that will each give me results I need, how can I tie these together?

 

[KenHigg]

At least document the issue and get it on a 'want list'. And advise the powers to be so you can fall back on it when you spend time coding work arounds...

 

[KenHigg]

The system is already in place and I cannot change the table structure.
...
The query I have done is looking for records where the SickStart and SickEnd are both >[Enter start date] And <[Enter end date]. Which will obviously exclude records with dates outside of these.

If I do a varity of queries that will each give me results I need, how can I tie these together?

The only way I can think of at the moment is to append them together in a temp table...

 

[Mile-O]

Then do one query that select Between Start And End on the sickstart date and one query that selects Between Start And End on the sickend date.

UNION them together.

You'll probably get a few duplicates as the same record can be selected twice. You'll be able to then base a Find Duplicates query on these based on the absence's primary key.