Group By Qry

[ItIsHardToProgram]

I have the following SQL in a VBA code

SelectEmp = no_emplo
DoCmd.RunSQL "SELECT JourFtemps.No_projet2, JourFtemps.descProjet, JourFtemps.TotalH, Emp.Nom, Emp.Prenom, JourFtemps.No_employ, Emp.no_emplo INTO TestSQL2" & _
             " FROM Emp INNER JOIN JourFtemps ON Emp.no_emplo=JourFtemps.No_employ" & _
             " WHERE (((JourFtemps.No_employ)=" & SelectEmp & "));"

The table creates correctly but

When I attemp to do a Group by with the table "TestSQL2" it does not let me, here is how I try grouping it

SELECT TestSQL2.No_projet2, TestSQL2.TotalH, TestSQL2.Nom, TestSQL2.Prenom, TestSQL2.no_emplo
FROM TestSQL2
Group By No_projet2;

Any one has any fast idea why it would not let me group it.... probably a general law but it evades me and I don't have my SQL help file with me =/ thx for your help

 

[PHV]

probably a general law
The GROUP BY clause should include all non aggregated/constant fields in the SELECT list.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ItIsHardToProgram]

Just to make sure I get you, it means that every field that are constant should be included in the group by clause....

but that leaves me with one problem... the field I want it to group by is no_projet2 and it is the only fields that varies... the only way it will accept my group by is with:

SELECT TestSQL2.No_projet2, TestSQL2.TotalH, TestSQL2.Nom, TestSQL2.Prenom, TestSQL2.no_emplo
FROM TestSQL2
Group By TestSQL2.No_projet2, TestSQL2.TotalH, TestSQL2.Nom, TestSQL2.Prenom, TestSQL2.no_emplo;

But then it leaves me with one problem... it returns the exact same table I had before.... So, in order to have only 1 project number, and the sum of hours for that project number... do I need another approach??? if so could you point me in the right direction???

Thank you, your help is appreciated

 

[randy700]

Are you sure you don't mean to use ORDER BY?

Randy

 

[PHV]

it means that every field that are constant should be included in the group by clause
No, every field not aggregated nor literal/numeric constant.
in order to have only 1 project number, and the sum of hours
SELECT No_projet2, Sum(TotalH) AS TotalHours
FROM TestSQL2
GROUP BY No_projet2

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ItIsHardToProgram]

No I would like to GROUP all projects togeter summing the total of hours per project, I guess what I will have to do is to create a query per project summing the results and updating it in a table =/

Thanks any way for your help.

 

[randy700]

Your original post including no mention of sums. That's why I thought sorting (order by) might be of use. However, before you give up, look at the most recent post from PHV. He apparently figured out you wanted totals and has provided the necessary code.


Randy

 

[ItIsHardToProgram]

Right, thank you for your help PHV. I should not go so fast about asking question about such simple mather =/

Your help is very appreciated.