Count days with entries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm brain dead at the moment!

I need to count the number of days that have an entry in my data. Some days
may have multiple entries so if I pull a count I get a sum of all of the
entries.

IE. I pulled a report for one day to test, I had 4 entries on that day so
the count I got was "4", I want it to be "1".
 
Plug in the correct table and field names.

SELECT Count(*) AS CountOfDays
FROM (SELECT DISTINCT tblDates.DateField
FROM tblDates) AS SubCount;
 
You can see that I've got a lot going on and I need to add the count entry:

SELECT [Dashboard Data - Table].Division, [Dashboard Data -
Table].Department, [Dashboard Data - Table].[1/2 Hr], Sum([Dashboard Data -
Table].[Calls Offered]) AS [SumOfCalls Offered], Sum([Dashboard Data -
Table].[Abn over 20 secs]) AS [SumOfAbn over 20 secs], Sum([Dashboard Data -
Table].[Inbound Calls]) AS [SumOfInbound Calls], Sum([Dashboard Data -
Table].[In Standard]) AS [SumOfIn Standard], Sum([Calculations - Query].[Ans
Time]) AS [SumOfAns Time], Sum([Calculations - Query].[Talk Time]) AS
[SumOfTalk Time], Sum([Calculations - Query].[Handle Time]) AS [SumOfHandle
Time], [SumOfIn Standard]/[SumOfCalls Offered] AS [Cust Svc Lvl], [SumOfAbn
over 20 secs]/[SumOfCalls Offered] AS [% Abn], [SumOfAns Time]/[SumOfCalls
Offered] AS [Avg Speed Ans], [SumOfTalk Time]/[SumOfCalls Offered] AS [Avg
Handle Time]
FROM [Dashboard Data - Table] INNER JOIN [Calculations - Query] ON
([Dashboard Data - Table].Date = [Calculations - Query].Date) AND ([Dashboard
Data - Table].[1/2 Hr] = [Calculations - Query].[1/2 Hr]) AND ([Dashboard
Data - Table].Team = [Calculations - Query].Team) AND ([Dashboard Data -
Table].Department = [Calculations - Query].Department) AND ([Dashboard Data -
Table].Division = [Calculations - Query].Division)
WHERE ((([Dashboard Data - Table].Date) Between [Forms]![Master Control -
Form]![Text1] And [Forms]![Master Control - Form]![Text3]))
GROUP BY [Dashboard Data - Table].Division, [Dashboard Data -
Table].Department, [Dashboard Data - Table].[1/2 Hr]
HAVING ((([Dashboard Data - Table].Division)=[Forms]![Master Control -
Form]![List5]));


I couldn't follow your response, I'm not that good, yet!?
 
Back
Top