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

On Tue, 17 Nov 2009 03:12:59 -0800 (PST), Tia
<tia.abdelkarim@gmail.com> wrote:

[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

Also group by Leave Type.

-- 
Daryl S


"Tom van Stiphout" wrote:

> On Tue, 17 Nov 2009 03:12:59 -0800 (PST), Tia
> <tia.abdelkarim@gmail.com> wrote:
> 
> [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
> .
>