• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 408
  • Last Modified:

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)
   
                 "
0
ctater
Asked:
ctater
  • 2
1 Solution
 
ajkampCommented:
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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now