Time intervals from a single record.

[P.Jaimal]

Hello,I have a database that records leaves taken by employees.Every
record has employee name,leavefrom(to record the starting date of
leave) & leaveupto( for the leave end date).My requirement is to find
out monthwise, the no of days of leave taken by an employee.The problem
comes when the end date falls in the subsequent month of start date.Ex:
say an employee Mr. X takes leave from 29-Oct-06 to 03-Nov-06.I need an
output like the following:

Emp Name Month Leaves taken
X Oct2006 3
X Nov2006 3

(for the above dates)

I have tried making two queries with a month criteria but can't get an
output like the one given above.Please help.

[salad]

P.Jaimal wrote:

Hello,I have a database that records leaves taken by employees.Every
record has employee name,leavefrom(to record the starting date of
leave) & leaveupto( for the leave end date).My requirement is to find
out monthwise, the no of days of leave taken by an employee.The problem
comes when the end date falls in the subsequent month of start date.Ex:
say an employee Mr. X takes leave from 29-Oct-06 to 03-Nov-06.I need an
output like the following:

Emp Name Month Leaves taken
X Oct2006 3
X Nov2006 3

(for the above dates)

I have tried making two queries with a month criteria but can't get an
output like the one given above.Please help.

I might add another field to your table called LeaveProcessed. It'd be
a YesNo field, default False. When a leave is recorded, the employee
date from/to would be recorded. Later, you could process it...or at the
end of data entry.

Tnen run a query to select all records that are unprocessed. Something
like:
Select LeaveDays : MakeLeaveRec([EmpID], [Leavestart], LeaveEnd]) _
From LeaveTable WHere LeaveProcessed = False

This query calls a function called MakeLeaveRec and passes the requisite
info to it.

In your function, you parse out the days. You can get the 1st and last
days of the month (see DateSerial functions). Get the first of the
month. x = DateSerial(Year(Date),Month(date),1). Get the last day of
the month. y = DateAdd("m",1,x). Now you can check to see if there are
overlaps.

Now...write/append a record to another table that shows the breakouts.
Most of the times you'll get 1 rec as leaves will be in the same month.
For those that span multiple months you can calc/write them out. This
way you get one rec per month.

Now in your reports you'd select records from this table. Then update
the LeaveProcessed to true. You call a select statement that in reality
ends up as an append query.

[P.Jaimal]

thanks for your reply.But I would like to avoid the manual
intervention/calculation to segregate the record having spanned month
into two.Can we design a query/set of queries to break it up?

salad wrote:

P.Jaimal wrote:

Hello,I have a database that records leaves taken by employees.Every
record has employee name,leavefrom(to record the starting date of
leave) & leaveupto( for the leave end date).My requirement is to find
out monthwise, the no of days of leave taken by an employee.The problem
comes when the end date falls in the subsequent month of start date.Ex:
say an employee Mr. X takes leave from 29-Oct-06 to 03-Nov-06.I need an
output like the following:

Emp Name Month Leaves taken
X Oct2006 3
X Nov2006 3

(for the above dates)

I have tried making two queries with a month criteria but can't get an
output like the one given above.Please help.

I might add another field to your table called LeaveProcessed. It'd be
a YesNo field, default False. When a leave is recorded, the employee
date from/to would be recorded. Later, you could process it...or at the
end of data entry.

Tnen run a query to select all records that are unprocessed. Something
like:
Select LeaveDays : MakeLeaveRec([EmpID], [Leavestart], LeaveEnd]) _
From LeaveTable WHere LeaveProcessed = False

This query calls a function called MakeLeaveRec and passes the requisite
info to it.

In your function, you parse out the days. You can get the 1st and last
days of the month (see DateSerial functions). Get the first of the
month. x = DateSerial(Year(Date),Month(date),1). Get the last day of
the month. y = DateAdd("m",1,x). Now you can check to see if there are
overlaps.

Now...write/append a record to another table that shows the breakouts.
Most of the times you'll get 1 rec as leaves will be in the same month.
For those that span multiple months you can calc/write them out. This
way you get one rec per month.

Now in your reports you'd select records from this table. Then update
the LeaveProcessed to true. You call a select statement that in reality
ends up as an append query.

[salad]

P.Jaimal wrote:

thanks for your reply.But I would like to avoid the manual
intervention/calculation to segregate the record having spanned month
into two.Can we design a query/set of queries to break it up?

I'm wondering why you just don't make a table that breaks them up when
the data is entered.

As far as I know, to select for one record and break it out to one or
more would be difficult in a select query. You maybe could checkout
NextRecord/MoveLayout/PrintRecord in help and present it in a report.

>
salad wrote:

>>P.Jaimal wrote:

>>>Hello,I have a database that records leaves taken by employees.Every
record has employee name,leavefrom(to record the starting date of
leave) & leaveupto( for the leave end date).My requirement is to find
out monthwise, the no of days of leave taken by an employee.The problem
comes when the end date falls in the subsequent month of start date.Ex:
say an employee Mr. X takes leave from 29-Oct-06 to 03-Nov-06.I need an
output like the following:

Emp Name Month Leaves taken
X Oct2006 3
X Nov2006 3

(for the above dates)

I have tried making two queries with a month criteria but can't get an
output like the one given above.Please help.

I might add another field to your table called LeaveProcessed. It'd be
a YesNo field, default False. When a leave is recorded, the employee
date from/to would be recorded. Later, you could process it...or at the
end of data entry.

Tnen run a query to select all records that are unprocessed. Something
like:
Select LeaveDays : MakeLeaveRec([EmpID], [Leavestart], LeaveEnd]) _
From LeaveTable WHere LeaveProcessed = False

This query calls a function called MakeLeaveRec and passes the requisite
info to it.

In your function, you parse out the days. You can get the 1st and last
days of the month (see DateSerial functions). Get the first of the
month. x = DateSerial(Year(Date),Month(date),1). Get the last day of
the month. y = DateAdd("m",1,x). Now you can check to see if there are
overlaps.

Now...write/append a record to another table that shows the breakouts.
Most of the times you'll get 1 rec as leaves will be in the same month.
For those that span multiple months you can calc/write them out. This
way you get one rec per month.

Now in your reports you'd select records from this table. Then update
the LeaveProcessed to true. You call a select statement that in reality
ends up as an append query.

[P.Jaimal]

Now it works!The table structure had to be modified as you
suggested.Thanks a lot.
salad wrote:

P.Jaimal wrote:

thanks for your reply.But I would like to avoid the manual
intervention/calculation to segregate the record having spanned month
into two.Can we design a query/set of queries to break it up?

I'm wondering why you just don't make a table that breaks them up when
the data is entered.

As far as I know, to select for one record and break it out to one or
more would be difficult in a select query. You maybe could checkout
NextRecord/MoveLayout/PrintRecord in help and present it in a report.

salad wrote:

>P.Jaimal wrote:

Hello,I have a database that records leaves taken by employees.Every
record has employee name,leavefrom(to record the starting date of
leave) & leaveupto( for the leave end date).My requirement is to find
out monthwise, the no of days of leave taken by an employee.The problem
comes when the end date falls in the subsequent month of start date.Ex:
say an employee Mr. X takes leave from 29-Oct-06 to 03-Nov-06.I need an
output like the following:

Emp Name Month Leaves taken
X Oct2006 3
X Nov2006 3

(for the above dates)

I have tried making two queries with a month criteria but can't get an
output like the one given above.Please help.
I might add another field to your table called LeaveProcessed. It'd be
a YesNo field, default False. When a leave is recorded, the employee
date from/to would be recorded. Later, you could process it...or at the
end of data entry.

Tnen run a query to select all records that are unprocessed. Something
like:
Select LeaveDays : MakeLeaveRec([EmpID], [Leavestart], LeaveEnd]) _
From LeaveTable WHere LeaveProcessed = False

This query calls a function called MakeLeaveRec and passes the requisite
info to it.

In your function, you parse out the days. You can get the 1st and last
days of the month (see DateSerial functions). Get the first of the
month. x = DateSerial(Year(Date),Month(date),1). Get the last day of
the month. y = DateAdd("m",1,x). Now you can check to see if there are
overlaps.

Now...write/append a record to another table that shows the breakouts.
Most of the times you'll get 1 rec as leaves will be in the same month.
For those that span multiple months you can calc/write them out. This
way you get one rec per month.

Now in your reports you'd select records from this table. Then update
the LeaveProcessed to true. You call a select statement that in reality
ends up as an append query.