Cumulative Sum

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Céline Brien - 29 May 2006 15:21 GMT

Hi there !

With a table call Historic,

and containing the fields NoEmployee, Date, Seniority

I would like to make a query that will tell me the date that an employee
reach 60 days in is seniority.

The field Seniority contents numbers 0 or 1 or 2

Thank you for your help and have a good day,

Céline

Reply to this Message

KARL DEWEY - 29 May 2006 17:57 GMT

Change the name of your field to something like StartDate as Date is a
reserved word in Access.
Use this as criteria --
>=Date()-60

> Hi there !
>
[quoted text clipped - 10 lines]
>
> Céline

Reply to this Message

Céline Brien - 29 May 2006 20:25 GMT

Hi Karl,

Hi everybody,

Thank you for your answer.

Thanks for reminding me not to use the name Date for a field.

In fact the field name is DateJour, but I translated fast. I work in French
and translate the names of the field to ease the understanding of my
questions.

Now, coming back to my question, I will try to explain better with the
tables below.

Any ideas are welcome,

Céline

Table
NoEmploye DateWorking Seniority
102345 may 1, 2006 1
102345 may 2, 2006 1
102345 may 3, 2006 1
102345 may 4, 2006 1
102345 may 5, 2006 1
101834 may 1, 2006 1
101834 may 2, 2006 1
101834 may 3, 2006 1
101834 may 4, 2006 1
101834 may 5, 2006 1

Result of the Query if possible
NoEmploye DateWorking Seniority SumSeniority
102345 may 1, 2006 1 1
102345 may 2, 2006 1 2
102345 may 3, 2006 1 3
102345 may 4, 2006 1 4
102345 may 5, 2006 1 5
101834 may 1, 2006 1 1
101834 may 2, 2006 1 2
101834 may 3, 2006 1 3
101834 may 4, 2006 1 4
101834 may 5, 2006 1 5

> Change the name of your field to something like StartDate as Date is a
> reserved word in Access.
[quoted text clipped - 15 lines]
>>
>> Céline

Reply to this Message

Marshall Barton - 29 May 2006 22:34 GMT

>In fact the field name is DateJour, but I translated fast. I work in French
>and translate the names of the field to ease the understanding of my
[quoted text clipped - 37 lines]
>>>
>>> The field Seniority contents numbers 0 or 1 or 2

Try this:

SELECT NoEmploye,
Min(DateWorking) As SeniorityDate
FROM Historic
WHERE (SELECT Sum(X.Rate)
FROM Historic As X
WHERE X.NoEmploye = Historic.NoEmploye
AND X.DateWorking <= Historic.DateWorking
) >= 60
GROUP BY NoEmploye

Signature

Marsh
MVP [MS Access]

Reply to this Message

Céline Brien - 30 May 2006 00:11 GMT

Hi Marshall,
You made my day !
Many, many thanks !
Céline

>>In fact the field name is DateJour, but I translated fast. I work in
>>French
[quoted text clipped - 51 lines]
> ) >= 60
> GROUP BY NoEmploye

Reply to this Message