Solved

Microsoft access 2003 count distinct field with group by / sum

Posted on 2006-06-23
3
398 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
ID: 16971381
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
ID: 16971575
To be honest, I am not sure how to go about doing a crosstab...
Do you mean subquery?
0
 

Author Comment

by:ctater
ID: 17001865
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
vba DCount with 2 criteria 3 34
Reset Active Directory Password via MS Access 9 52
Operation must use an updatable query 4 24
how to link subforms ms/access VBA 6 26
This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

932 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

13 Experts available now in Live!

Get 1:1 Help Now