Month to Date Total by Date by Staff Member problem 1

[Mikeauz]

Hi,

In Access 2000, I have a table that contains:

Date Staff Calls Handled DateNumeric
01/01/2004 A 100 20040101
02/01/2004 A 89 20040102
03/01/2004 B 100 20040103

and I'm trying to acheive a select qeuery that will give me:

Date Staff MonthToDateCallsHandled DateNumeric
01/01/2004 A 100 20040101
02/01/2004 A 189 20040102
03/01/2004 B 100 20040103

So far I have:
MTDHld: Format(DSum("InHld","tblM8AHT","[DateNumeric]<=" & [DateNumericAlias] & ""),'#')

Where [DateNumeric] is a field I added to the table which is val(format$([Date],'yyyymmdd'))

But this only gives me a culmlative daily figure for Calls Handled

Anyone have any idea how I can produce a figure that is Month to Date total of Calls handled by staff member?

Its a tough one thanks for any help!

 

[TonyJollans]

Hi Mikeauz,

I would use a sub-select rather than a DSum Function as native SQL should be more efficient, but whichever way you do it, you should not need your DateNumeric field - Access can compare dates.

[blue][tt]SELECT [Date], [Staff], [Calls Handled],

( SELECT SUM([Calls Handled])
FROM YourTable T2
WHERE T2.[Date] <= YourTable.[Date]
AND T2.[Staff] = YourTable.[Staff] ) AS CumulativeCalls

FROM YourTable;[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[Mikeauz]

Tony,

This is fantastic and well worthy of a star, but I still have the problem of the CumulativeCalls resetting after the end of the month (To produce a Month to Date figure) any ideas on how to do that?

 

[TonyJollans]

Hi Mikeauz,

You should just need to add an extra check to the sub-select criteria ..

[blue][tt]WHERE ..
AND Year(T2.[Date]) = Year(YourTable.[Date])
AND Month(T2.[Date]) = Month(YourTable.[Date])
AND ..[/tt][/blue]

.. or ..

[blue][tt]WHERE ..
AND Format(T2.[Date],"yymm") = Format(YourTable.[Date],"yymm")
AND ..[/tt][/blue]

One final point; it is generally good practice to avoid column names like Date which are words with special meaning to Access. In the above, the (square) brackets are essential to stop it being misinterpreted.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[Mikeauz]

Tony,

you are a champion! This is really great code.

I'm going to have to learn more about sub-select queries

Mike

 

[Mikeauz]

Tony or anybody out there I have another step for you...

At the moment if I use this code in an excel pivot table it will give me a Month to Date figure for each day that a staff has taken calls (Been at work)

The next Step I would like to do is retain the month to date figure for each staff for each day regardless of whether they miss or day or not.

For example the staff did this:

Date Staff Calls Handled
01/01/2000 A 10
03/01/2000 A 10

And I want the query to show:

Date Staff Month To Date Calls Handled
01/01/2000 A 10
02/01/2000 A 10
03/01/2000 A 20

Thanks for your help

 

[Mikeauz]

Just bringing this post to the top.

Thanks for any help

 

[JonFer]

Does your version of Access support subqueries in the FROM clause like the following?

Select [Date], Staff
From (Select Distinct Staff, [Date]
From YourTable)

 

[Mikeauz]

JonFer,

Thanks for the help but I'm not sure on how to test using the select distinct function, I'm using Access 2000 if that helps.

 

[JonFer]

Access 2000 does support using a sub-query in the FROM clause. You type the SQL in the SQL view of a query.

See this thread (thread701-907218 Aug 26th post) for how to create additional records for dates not in your table. You can adapt it to your situation by only creating dates in months that the Staff had calls. Try it out and then post your SQL if you run into difficulty.


John

http://www.interautomateconsulting.com

 

[wvmbark]

Looking at your first example, it appears your wanting Year to date numbers, not Month to date. Month to date numbers would start a new count on 2/1/04, where yours added Jan and Feb numbers. Is that correct?

 

[JonFer]

I believe the European date format is used in the example: dd/mm/yyyy

John

http://www.interautomateconsulting.com

 

[Mikeauz]

JonFer,

I'll keep trying your suggestion, but haven't had much luck so far. I've changed what I'm trying to do slightly in that I'm now trying to get Month to Date Averages.

For example:

tblMaster
Date(dd/mm/yy) Staff Adherence%
01/01/01 A 97
02/01/01 A 100
02/01/01 B 95
04/01/01 A 75

So the queries result should be:

Date(dd/mm/yy) Staff MTD Adherence%
01/01/01 A 97
02/01/01 A 98.5
02/01/01 B 95
03/01/01 A 98.5
03/01/01 B 95
04/01/01 A 90.7
04/01/01 B 95

My sql so far (That only shows results for dates that the staff member worked is)

MTDAD%: (SELECT AVG([Adherence Percentage]) FROM [tblMaster] T2 WHERE T2.[Date] <= [tblMaster].[Date] AND T2.[Agent] =[tblMaster].[Agent] AND Format(T2.[Date],"yymm")= Format([tblMaster].[Date],"yymm"))

 

[JonFer]

If you have a Sequence table with the 32 rows (0-31 inclusive), then this SQL should generate all of the days in the month for each user that had any calls that month.

Select Distinct Staff, 
    DateSerial(Year([Date]),Month([Date]),SeqNum) as RptDate
From YourTable, Sequence
Where SeqNum Between 1 and Day(DateSerial(Year([Date]),Month([Date])+1,0)

Then build a query on top of this with the subquery that calculates the sum or avg.


John

http://www.interautomateconsulting.com