Cumulative Sum

  • Thread starter Thread starter Céline Brien
  • Start date Start date
C

Céline Brien

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
 
Change the name of your field to something like StartDate as Date is a
reserved word in Access.
Use this as criteria --
 
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
 
Céline Brien said:
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.

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
Click to expand...

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
 
Back
Top