Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations MikeeOK on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL Group By Problems!! 3

Status
Not open for further replies.

jhleblanc

Programmer
Aug 8, 2006
3
CA
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"
 
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]
 
That worked and fixed the error but now I can't call the fldhours to display the total number of hours for each record?
 
You haven't given that field a name and you will need one so that you can refer to it
Code:
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"
 
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
 
Thanks you guys, I'm learning this all on my own and running into some trips here and there! You Guys Rock.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top