Solved

Microsoft access 2003 count distinct field with group by / sum

Posted on 2006-06-23
3
396 Views
Last Modified: 2008-01-09
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
Comment
Question by:ctater
  • 2
3 Comments
 
LVL 9

Accepted Solution

by:
ajkamp earned 500 total points
Comment Utility
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
 

Author Comment

by:ctater
Comment Utility
To be honest, I am not sure how to go about doing a crosstab...
Do you mean subquery?
0
 

Author Comment

by:ctater
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now