asked on

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)

"

ASKER CERTIFIED SOLUTION

ajkamp

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ctater

ASKER

To be honest, I am not sure how to go about doing a crosstab...
Do you mean subquery?

ctater

ASKER

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
"