What is the date when the total of minutes became >=600

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

Céline Brien

Hi there,
The table ActPers contains NoEmploye, DateJour, code of activity (CodeSA)
and the duration of the activity in minutes (DureePers).
For the activities having the code 027, I need the date when the total of
minutes became >=600
I tried the following codes, but I get the minimum date of all the dates and
not the date when the total of minutes became >=600.
Any hint would be appreciate,
Thank you,
Céline

SELECT ActPers.NoEmploye, Min(ActPers.DateJour) AS MinDeDateJour INTO
TPrevention
FROM ActPers
WHERE (((ActPers.CodeSA)="027") AND (((SELECT Sum(X.DureePers) FROM ActPers
As X WHERE X.NoEmploye = ActPers.NoEmploye AND X.DateJour <=
ActPers.DateJour))>=600))
GROUP BY ActPers.NoEmploye;
 
Perhaps something like the following UNTESTED query will give you the desired
results.

SELECT ActPers.*
FROM ActPers INNER JOIN
(SELECT A.NoEmployee, Min(A.DateJour) as FirstDate
FROM ActPers as A LEFT JOIN ActPers As B
ON A.NoEmployee = B.NoEmployee
AND A.DateJour >= B.DateJour
WHERE Code = "027"
GROUP BY A.NoEmployee, A.DateJour
HAVING Sum(A.DureePers) > 600) as Temp
ON ActPers.NoEmployee = Temp.NoEmployee
AND ActPers.DateJour = Temp.FirstDate

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Hi John,
Thank you so much for your answer.
To test your codes, I change the name of my field NoEmploye to NoEmployee,
and the name CodeSA to Code.
I pasted your codes in a new query.
Upon execution, I receive this error message :
----------------------------
Le champ spécifié 'Code' peut désigner plusieurs tables listées dans la
clause FROM de votre instruction SQL.
----------------------------
If I close que query and save and open the SQL, the codes pasted have change
to this :
----------------------------
SELECT ActPers.*
FROM ActPers INNER JOIN [SELECT A.NoEmployee, Min(A.DateJour) as FirstDate
FROM ActPers as A LEFT JOIN ActPers As B
ON A.NoEmployee = B.NoEmployee
AND A.DateJour >= B.DateJour
WHERE Code = "027"
GROUP BY A.NoEmployee, A.DateJour
HAVING Sum(A.DureePers) > 600]. AS Temp ON (ActPers.DateJour =
Temp.FirstDate) AND (ActPers.NoEmployee = Temp.NoEmployee);
----------------------------
Can you help me again ?
If necessary, I could let you use my table.
Thank you,
Céline
 
Instead of changing your field names in your table, you should just change the
SQL statement to use your field and table names.

After reviewing the proposed SQL, I find that I had left DateJour in the GROUP
BY clause. It should not have been there and I needed to specifically refer
to the table for CodeSA. So try the following

SELECT ActPers.*
FROM ActPers INNER JOIN
(SELECT A.NoEmploye, Min(A.DateJour) as FirstDate
FROM ActPers as A LEFT JOIN ActPers As B
ON A.NoEmploye = B.NoEmploye
AND A.DateJour >= B.DateJour
WHERE A.CodeSA = "027"
GROUP BY A.NoEmploye
HAVING Sum(A.DureePers) > 600) as Temp
ON ActPers.NoEmploye = Temp.NoEmploye
AND ActPers.DateJour = Temp.FirstDate

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Céline Brien said:
Hi John,
Thank you so much for your answer.
To test your codes, I change the name of my field NoEmploye to NoEmployee,
and the name CodeSA to Code.
I pasted your codes in a new query.
Upon execution, I receive this error message :
----------------------------
Le champ spécifié 'Code' peut désigner plusieurs tables listées dans la
clause FROM de votre instruction SQL.
----------------------------
If I close que query and save and open the SQL, the codes pasted have change
to this :
----------------------------
SELECT ActPers.*
FROM ActPers INNER JOIN [SELECT A.NoEmployee, Min(A.DateJour) as FirstDate
FROM ActPers as A LEFT JOIN ActPers As B
ON A.NoEmployee = B.NoEmployee
AND A.DateJour >= B.DateJour
WHERE Code = "027"
GROUP BY A.NoEmployee, A.DateJour
HAVING Sum(A.DureePers) > 600]. AS Temp ON (ActPers.DateJour =
Temp.FirstDate) AND (ActPers.NoEmployee = Temp.NoEmployee);
----------------------------
Can you help me again ?
If necessary, I could let you use my table.
Thank you,
Céline



John Spencer said:
Perhaps something like the following UNTESTED query will give you the
desired results.

SELECT ActPers.*
FROM ActPers INNER JOIN
(SELECT A.NoEmployee, Min(A.DateJour) as FirstDate
FROM ActPers as A LEFT JOIN ActPers As B
ON A.NoEmployee = B.NoEmployee
AND A.DateJour >= B.DateJour
WHERE Code = "027"
GROUP BY A.NoEmployee, A.DateJour
HAVING Sum(A.DureePers) > 600) as Temp
ON ActPers.NoEmployee = Temp.NoEmployee
AND ActPers.DateJour = Temp.FirstDate

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Click to expand...
Click to expand...
 
Hi John,
Thanks again for your answer.
The result is not what I expected.
I don't get the date where the sun of minutes for an employee became greater
then 600.
Plus I get other codes then 027, very weird !
I have to leave now, if you are kind enough to suggest something else, I
will follow up on your answer tomorrow.
I am sure we will find a solution.
Many thanks again !
Céline

John Spencer said:
Instead of changing your field names in your table, you should just change
the SQL statement to use your field and table names.

After reviewing the proposed SQL, I find that I had left DateJour in the
GROUP BY clause. It should not have been there and I needed to
specifically refer to the table for CodeSA. So try the following

SELECT ActPers.*
FROM ActPers INNER JOIN
(SELECT A.NoEmploye, Min(A.DateJour) as FirstDate
FROM ActPers as A LEFT JOIN ActPers As B
ON A.NoEmploye = B.NoEmploye
AND A.DateJour >= B.DateJour
WHERE A.CodeSA = "027"
GROUP BY A.NoEmploye
HAVING Sum(A.DureePers) > 600) as Temp
ON ActPers.NoEmploye = Temp.NoEmploye
AND ActPers.DateJour = Temp.FirstDate

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Céline Brien said:
Hi John,
Thank you so much for your answer.
To test your codes, I change the name of my field NoEmploye to
NoEmployee, and the name CodeSA to Code.
I pasted your codes in a new query.
Upon execution, I receive this error message :
----------------------------
Le champ spécifié 'Code' peut désigner plusieurs tables listées dans la
clause FROM de votre instruction SQL.
----------------------------
If I close que query and save and open the SQL, the codes pasted have
change to this :
----------------------------
SELECT ActPers.*
FROM ActPers INNER JOIN [SELECT A.NoEmployee, Min(A.DateJour) as
FirstDate
FROM ActPers as A LEFT JOIN ActPers As B
ON A.NoEmployee = B.NoEmployee
AND A.DateJour >= B.DateJour
WHERE Code = "027"
GROUP BY A.NoEmployee, A.DateJour
HAVING Sum(A.DureePers) > 600]. AS Temp ON (ActPers.DateJour =
Temp.FirstDate) AND (ActPers.NoEmployee = Temp.NoEmployee);
----------------------------
Can you help me again ?
If necessary, I could let you use my table.
Thank you,
Céline



John Spencer said:
Perhaps something like the following UNTESTED query will give you the
desired results.

SELECT ActPers.*
FROM ActPers INNER JOIN
(SELECT A.NoEmployee, Min(A.DateJour) as FirstDate
FROM ActPers as A LEFT JOIN ActPers As B
ON A.NoEmployee = B.NoEmployee
AND A.DateJour >= B.DateJour
WHERE Code = "027"
GROUP BY A.NoEmployee, A.DateJour
HAVING Sum(A.DureePers) > 600) as Temp
ON ActPers.NoEmployee = Temp.NoEmployee
AND ActPers.DateJour = Temp.FirstDate

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Céline Brien wrote:
Hi there,
The table ActPers contains NoEmploye, DateJour, code of activity
(CodeSA) and the duration of the activity in minutes (DureePers).
For the activities having the code 027, I need the date when the total
of minutes became >=600
I tried the following codes, but I get the minimum date of all the
dates and not the date when the total of minutes became >=600.
Any hint would be appreciate,
Thank you,
Céline

SELECT ActPers.NoEmploye, Min(ActPers.DateJour) AS MinDeDateJour INTO
TPrevention
FROM ActPers
WHERE (((ActPers.CodeSA)="027") AND (((SELECT Sum(X.DureePers) FROM
ActPers As X WHERE X.NoEmploye = ActPers.NoEmploye AND X.DateJour <=
ActPers.DateJour))>=600))
GROUP BY ActPers.NoEmploye;
Click to expand...
Click to expand...
Click to expand...
 
The HAVING SUM(a.DureePers) is probably HAVING SUM(b.DureePers) since
alias 'a' is GROUPed and it is 'b' which is 'floating' to make a running
SUM,

and you have to repeat the condition for CodeSA = "027", which can be done
in many way, such as adding a

AND ActPers.CodeSA= Temp.CodeSA


at the end of the ON clause ( at the end of the query).


I would also change the ON clause of the sub-query to

ON A.NoEmploye = B.NoEmploye
AND A.CodeSA=B.CodeSA
AND A.DateJour >= B.DateJour


to be sure to keep only the records with the required CodeSA, for both
alias.




Vanderghast, Access MVP



Céline Brien said:
Hi John,
Thanks again for your answer.
The result is not what I expected.
I don't get the date where the sun of minutes for an employee became
greater then 600.
Plus I get other codes then 027, very weird !
I have to leave now, if you are kind enough to suggest something else, I
will follow up on your answer tomorrow.
I am sure we will find a solution.
Many thanks again !
Céline

John Spencer said:
Instead of changing your field names in your table, you should just
change the SQL statement to use your field and table names.

After reviewing the proposed SQL, I find that I had left DateJour in the
GROUP BY clause. It should not have been there and I needed to
specifically refer to the table for CodeSA. So try the following

SELECT ActPers.*
FROM ActPers INNER JOIN
(SELECT A.NoEmploye, Min(A.DateJour) as FirstDate
FROM ActPers as A LEFT JOIN ActPers As B
ON A.NoEmploye = B.NoEmploye
AND A.DateJour >= B.DateJour
WHERE A.CodeSA = "027"
GROUP BY A.NoEmploye
HAVING Sum(A.DureePers) > 600) as Temp
ON ActPers.NoEmploye = Temp.NoEmploye
AND ActPers.DateJour = Temp.FirstDate

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Céline Brien said:
Hi John,
Thank you so much for your answer.
To test your codes, I change the name of my field NoEmploye to
NoEmployee, and the name CodeSA to Code.
I pasted your codes in a new query.
Upon execution, I receive this error message :
----------------------------
Le champ spécifié 'Code' peut désigner plusieurs tables listées dans la
clause FROM de votre instruction SQL.
----------------------------
If I close que query and save and open the SQL, the codes pasted have
change to this :
----------------------------
SELECT ActPers.*
FROM ActPers INNER JOIN [SELECT A.NoEmployee, Min(A.DateJour) as
FirstDate
FROM ActPers as A LEFT JOIN ActPers As B
ON A.NoEmployee = B.NoEmployee
AND A.DateJour >= B.DateJour
WHERE Code = "027"
GROUP BY A.NoEmployee, A.DateJour
HAVING Sum(A.DureePers) > 600]. AS Temp ON (ActPers.DateJour =
Temp.FirstDate) AND (ActPers.NoEmployee = Temp.NoEmployee);
----------------------------
Can you help me again ?
If necessary, I could let you use my table.
Thank you,
Céline



"John Spencer" <[email protected]> a écrit dans le message de (e-mail address removed)...
Perhaps something like the following UNTESTED query will give you the
desired results.

SELECT ActPers.*
FROM ActPers INNER JOIN
(SELECT A.NoEmployee, Min(A.DateJour) as FirstDate
FROM ActPers as A LEFT JOIN ActPers As B
ON A.NoEmployee = B.NoEmployee
AND A.DateJour >= B.DateJour
WHERE Code = "027"
GROUP BY A.NoEmployee, A.DateJour
HAVING Sum(A.DureePers) > 600) as Temp
ON ActPers.NoEmployee = Temp.NoEmployee
AND ActPers.DateJour = Temp.FirstDate

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Céline Brien wrote:
Hi there,
The table ActPers contains NoEmploye, DateJour, code of activity
(CodeSA) and the duration of the activity in minutes (DureePers).
For the activities having the code 027, I need the date when the total
of minutes became >=600
I tried the following codes, but I get the minimum date of all the
dates and not the date when the total of minutes became >=600.
Any hint would be appreciate,
Thank you,
Céline

SELECT ActPers.NoEmploye, Min(ActPers.DateJour) AS MinDeDateJour INTO
TPrevention
FROM ActPers
WHERE (((ActPers.CodeSA)="027") AND (((SELECT Sum(X.DureePers) FROM
ActPers As X WHERE X.NoEmploye = ActPers.NoEmploye AND X.DateJour <=
ActPers.DateJour))>=600))
GROUP BY ActPers.NoEmploye;
Click to expand...
Click to expand...
Click to expand...
 
Thanks for the backup.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
The HAVING SUM(a.DureePers) is probably HAVING SUM(b.DureePers) since
alias 'a' is GROUPed and it is 'b' which is 'floating' to make a running
SUM,

and you have to repeat the condition for CodeSA = "027", which can be
done in many way, such as adding a

AND ActPers.CodeSA= Temp.CodeSA


at the end of the ON clause ( at the end of the query).


I would also change the ON clause of the sub-query to

ON A.NoEmploye = B.NoEmploye
AND A.CodeSA=B.CodeSA
AND A.DateJour >= B.DateJour


to be sure to keep only the records with the required CodeSA, for both
alias.




Vanderghast, Access MVP



Céline Brien said:
Hi John,
Thanks again for your answer.
The result is not what I expected.
I don't get the date where the sun of minutes for an employee became
greater then 600.
Plus I get other codes then 027, very weird !
I have to leave now, if you are kind enough to suggest something else,
I will follow up on your answer tomorrow.
I am sure we will find a solution.
Many thanks again !
Céline

John Spencer said:
Instead of changing your field names in your table, you should just
change the SQL statement to use your field and table names.

After reviewing the proposed SQL, I find that I had left DateJour in
the GROUP BY clause. It should not have been there and I needed to
specifically refer to the table for CodeSA. So try the following

SELECT ActPers.*
FROM ActPers INNER JOIN
(SELECT A.NoEmploye, Min(A.DateJour) as FirstDate
FROM ActPers as A LEFT JOIN ActPers As B
ON A.NoEmploye = B.NoEmploye
AND A.DateJour >= B.DateJour
WHERE A.CodeSA = "027"
GROUP BY A.NoEmploye
HAVING Sum(A.DureePers) > 600) as Temp
ON ActPers.NoEmploye = Temp.NoEmploye
AND ActPers.DateJour = Temp.FirstDate

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Céline Brien wrote:
Hi John,
Thank you so much for your answer.
To test your codes, I change the name of my field NoEmploye to
NoEmployee, and the name CodeSA to Code.
I pasted your codes in a new query.
Upon execution, I receive this error message :
----------------------------
Le champ spécifié 'Code' peut désigner plusieurs tables listées dans
la clause FROM de votre instruction SQL.
----------------------------
If I close que query and save and open the SQL, the codes pasted
have change to this :
----------------------------
SELECT ActPers.*
FROM ActPers INNER JOIN [SELECT A.NoEmployee, Min(A.DateJour) as
FirstDate
FROM ActPers as A LEFT JOIN ActPers As B
ON A.NoEmployee = B.NoEmployee
AND A.DateJour >= B.DateJour
WHERE Code = "027"
GROUP BY A.NoEmployee, A.DateJour
HAVING Sum(A.DureePers) > 600]. AS Temp ON (ActPers.DateJour =
Temp.FirstDate) AND (ActPers.NoEmployee = Temp.NoEmployee);
----------------------------
Can you help me again ?
If necessary, I could let you use my table.
Thank you,
Céline



"John Spencer" <[email protected]> a écrit dans le message de (e-mail address removed)...
Perhaps something like the following UNTESTED query will give you
the desired results.

SELECT ActPers.*
FROM ActPers INNER JOIN
(SELECT A.NoEmployee, Min(A.DateJour) as FirstDate
FROM ActPers as A LEFT JOIN ActPers As B
ON A.NoEmployee = B.NoEmployee
AND A.DateJour >= B.DateJour
WHERE Code = "027"
GROUP BY A.NoEmployee, A.DateJour
HAVING Sum(A.DureePers) > 600) as Temp
ON ActPers.NoEmployee = Temp.NoEmployee
AND ActPers.DateJour = Temp.FirstDate

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Céline Brien wrote:
Hi there,
The table ActPers contains NoEmploye, DateJour, code of activity
(CodeSA) and the duration of the activity in minutes (DureePers).
For the activities having the code 027, I need the date when the
total of minutes became >=600
I tried the following codes, but I get the minimum date of all the
dates and not the date when the total of minutes became >=600.
Any hint would be appreciate,
Thank you,
Céline

SELECT ActPers.NoEmploye, Min(ActPers.DateJour) AS MinDeDateJour
INTO TPrevention
FROM ActPers
WHERE (((ActPers.CodeSA)="027") AND (((SELECT Sum(X.DureePers)
FROM ActPers As X WHERE X.NoEmploye = ActPers.NoEmploye AND
X.DateJour <= ActPers.DateJour))>=600))
GROUP BY ActPers.NoEmploye;
Click to expand...
Click to expand...
Click to expand...
 
No problem :-) I was not sure you would have time to see the OP message
before tomorrow :-)

Vanderghast, Access MVP
 
Back
Top