Microsoft access 2003 count distinct field with group by / sum

I am using access 2003 as the back end on a asp.net 2.0 project. I am using a grid view to display the data.
I am trying to get a count of the distinct client Ids who grouping by the service. Also sum direct hours, direct hours and grouping by month and year.
I am aware that Access isn't the ideal solution for web apps and also doesn't directly support count(distinct fieldname). However, I am restricted because I want non technical employees to be able if necessary be able to manipulate the data if the need should arise. I am aout to get grey hairs. I have tried different subqueries etc to pull the distinct ids but nothing seems to work; HELP!!!

the code follows:
SelectCommand =  "select count(TblDailyHours.ClientId) as TotalClients, tblDailyHours.ServiceType, sum(tblDailyHours.HoursDirect) as HoursDirect, sum(tblDailyHours.HoursIndirect) as HoursIndirect,
sum(tblDailyHours.HoursDirect + tblDailyHours.HoursIndirect) as tempTotalHours, month(tblDailyHours.HOSDate) & '/' & year(tblDailyHours.HOSDate) as tempMonthYear
 from tblDailyHours
 where HOSDate BETWEEN @StartDate AND @EndDate
 
 group by TblDailyHours.ServiceType, month(tblDailyHours.HOSDate) & '/' & year(TblDailyHours.HOSDate)
   
                 "
ctaterAsked:
Who is Participating?
 
ajkampConnect With a Mentor Commented:
I would do this in 2 steps, get a distinct list of groups, then do a crosstab with the other data. Access will look at each record that is returned and check against other records for dupes in all fields, not just one.
0
 
ctaterAuthor Commented:
To be honest, I am not sure how to go about doing a crosstab...
Do you mean subquery?
0
 
ctaterAuthor Commented:
I finally see the light. Thanks to your prompting I got it to finally work!!!!! I was just missing a small component.

Solution:
SelectCommand =  "select count(tmpTblDailyHours.ClientId) as TotalClients, tmptblDailyHours.tmpServiceType as ServiceType, sum(tmpTblDailyHours.HoursDirect) as HoursDirect, sum(tmpTblDailyHours.HoursIndirect) as HoursIndirect,
sum(tmpTblDailyHours.HoursDirect + tmpTblDailyHours.HoursIndirect) as tempTotalHours, tmpTblDailyHours.tempMonthYear as tempMonthYear
 from (select TblDailyHours.ClientId , tblDailyHours.ServiceType as tmpServiceType, sum(tblDailyHours.HoursDirect) as HoursDirect, sum(tblDailyHours.HoursIndirect) as HoursIndirect,
sum(tblDailyHours.HoursDirect + tblDailyHours.HoursIndirect) as tempTotalHours, month(tblDailyHours.HOSDate) & '/' & year(tblDailyHours.HOSDate) as tempMonthYear
 from tblDailyHours
 where HOSDate BETWEEN @StartDate AND @EndDate
 
 group by tblDailyHours.ClientId, TblDailyHours.ServiceType, month(tblDailyHours.HOSDate) & '/' & year(TblDailyHours.HOSDate)
  ) tmpTblDailyHours
 
 group by tmpTblDailyHours.tmpServiceType, tmpTblDailyHours.TempMonthYear
                    " 
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.