SQL Group By Problems!! 3

[jhleblanc]

I'm converting from VBA to ASP both in Access and I'm having a problem with Grouping it's just not the same when you use the GROUP BY everything I read says I have to Group By all the fields that I have in the query?

This is useless for me as I need to group by the customers name or number etc... And total up the hours but by grouping both it gives me several small entries like all the ones with one hour then all the ones with two hours etc... Driving me crazy.

Here is the SQL Query:
strSQL = "SELECT tblservices.fldcontact, tblservices.fldhours, Sum(tblservices.fldhours), tblcontacts.fldcontact, tblcontacts.fldfirstname FROM tblservices INNER JOIN tblcontacts ON tblservices.fldcontact=tblcontacts.fldcontact GROUP BY tblservices.fldcontact, tblservices.fldhours, tblcontacts.fldcontact, tblcontacts.fldfirstname"

 

[Remou]

I do not think you need to group by both fields in the above example. Have you tried (?):

[tt]strSQL = "SELECT tblservices.fldcontact, tblcontacts.fldfirstname, Sum(tblservices.fldhours) FROM tblservices INNER JOIN tblcontacts ON tblservices.fldcontact=tblcontacts.fldcontact GROUP BY tblservices.fldcontact, tblcontacts.fldfirstname"[/tt]

 

[jhleblanc]

That worked and fixed the error but now I can't call the fldhours to display the total number of hours for each record?

 

[Golom]

You haven't given that field a name and you will need one so that you can refer to it

strSQL = "SELECT tblservices.fldcontact, tblcontacts.fldfirstname, Sum(tblservices.fldhours)[COLOR=red] As [SumOfFldHours][/color] FROM tblservices INNER JOIN tblcontacts ON tblservices.fldcontact=tblcontacts.fldcontact GROUP BY tblservices.fldcontact, tblcontacts.fldfirstname"

 

[jebry]

Hi!

You need to rename the sum:

Sum(tblservices.fldhours) As TotalHours

Now you can display it using TotalHours as the field.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[jhleblanc]

Thanks you guys, I'm learning this all on my own and running into some trips here and there! You Guys Rock.

 

[lespaul]

check out thread701-1258387 for an explanation of the GROUP BY clause

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi