total leave

[Tia]

DEAR,

i have a table with employee ID name date from, date to, total days,
type of leave"annual, sick"
i have a query for total days
how can i get total annual taken by ID
and total sick taken by ID

ID from Till Total Days Leave Type
5001 1/1/2009 1/5/2009 Annual
5002 1/11/2009 1/20/2009 Sick
5001 1/11/2009 1/20/2009 Annual


Best Regards

 

[Tom van Stiphout]

On Tue, 17 Nov 2009 03:12:59 -0800 (PST), Tia

[Total Days] is a calculated column and does not belong in a
relational database. Rather you should delete this field, and then
create a query on this table with an extra column for [Total Days]
(which I will call TotalDays because I hate these extra brackets) with
an expression to calculate the days:
TotalDays: DateDiff('d', From, Till)

I think "From" may also be a reserved word; let's rename those two
fields to LeaveFrom and LeaveTill
TotalDays: DateDiff('d', LeaveFrom, LeaveTill)

Now you can create a new query based on this query and turn on the
Group By feature with the Sigma button, and group by ID and sum by
TotalDays.

-Tom.
Microsoft Access MVP

 

[Daryl S]

Also group by Leave Type.

--
Daryl S

On Tue, 17 Nov 2009 03:12:59 -0800 (PST), Tia

[Total Days] is a calculated column and does not belong in a
relational database. Rather you should delete this field, and then
create a query on this table with an extra column for [Total Days]
(which I will call TotalDays because I hate these extra brackets) with
an expression to calculate the days:
TotalDays: DateDiff('d', From, Till)

I think "From" may also be a reserved word; let's rename those two
fields to LeaveFrom and LeaveTill
TotalDays: DateDiff('d', LeaveFrom, LeaveTill)

Now you can create a new query based on this query and turn on the
Group By feature with the Sigma button, and group by ID and sum by
TotalDays.

-Tom.
Microsoft Access MVP

DEAR,

i have a table with employee ID name date from, date to, total days,
type of leave"annual, sick"
i have a query for total days
how can i get total annual taken by ID
and total sick taken by ID

ID from Till Total Days Leave Type
5001 1/1/2009 1/5/2009 Annual
5002 1/11/2009 1/20/2009 Sick
5001 1/11/2009 1/20/2009 Annual


Best Regards

.