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

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;
 
J

John Spencer

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

Céline Brien

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
 
J

John Spencer

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...
 
C

Céline Brien

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...
 
V

vanderghast

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...
 
J

John Spencer

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...
 
V

vanderghast

No problem :) I was not sure you would have time to see the OP message
before tomorrow :)

Vanderghast, Access MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

FS LARGE FORMAT scanners , INKJET printers , inkjet plotters (hp designjet) , 11x17 color laser $199 0
FS inkjet plotters AND continous feed 36 inch scanners 0
HP color laser $299 includes jetdirect ethernet card - extra toners NEW $20, also hp 8500 $700 8550n 1
FS PRINTER PARTS trays fusers drums printheads -- oki fujitsu hp genicom epson ibm dec jetdirect las 3
FS PRINTER PARTS trays fusers drums printheads -- oki fujitsu hp genicom epson ibm dec jetdirect las 1
FS epson receipt printer $99 , seiko label printers $49 symbol barcode guns $75 intermec wireless ba 0
FS PRINTER PARTS trays fusers drums printheads -- oki fujitsu hp genicom epson ibm dec jetdirect las 0
FWT Newsletter - Weekly - October 4, 2004 2

Top