Crosstab Query should do this?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tails - 22 Aug 2004 14:58 GMT

Hi

I have been asked to do something for one of the departments in my office -
I thought it would be easier than it's turning out.

They have a years worth of history for calls for each department in the call
center -split up into half hourly intervals:

The fields are

Date (dd/mm/yy)
time (10:30 or 11:00, or whatever)
Team (department)
Date (Monday / tuesday / whatever)
Calls abandoned
Calls < 30 seconds
etc
....

They want to know, over that year, how many of each day of the week there
was per team.

So if Team A don't work bank holiday Mondays then there will have been no
calls coming in that day so no records, and less Mondays than there would be
for Team B which works 365 days a year.

I seem to be hitting a brick wall though because I set up a crosstab query
and it is saying there are 81 mondays a year?

Can anyone help please?

Thanks as always

Signature

Tails (The not so expert 'Expert' of their office)

Reply to this Message

Duane Hookom - 22 Aug 2004 15:38 GMT

I see two fields named Date in your field list. You should be able to set up
your crosstab with a column heading of
Format([DateField],"dddd")
Set the Column Headings property to
Column Headings: "Sunday", "Monday","Tuesday",..."Saturday"

Signature

Duane Hookom
MS Access MVP

> Hi
>
[quoted text clipped - 28 lines]
>
> Thanks as always

Reply to this Message

Tails - 22 Aug 2004 18:52 GMT

> I see two fields named Date in your field list. You should be able to set up
> your crosstab with a column heading of
> Format([DateField],"dddd")
> Set the Column Headings property to
> Column Headings: "Sunday", "Monday","Tuesday",..."Saturday"

Hi,

Thanks for your reply and sorry for the confusion. One is Date and one is
Day.

I need the number of mondays calls came in for each team individually, then
the number of tuesdays calls came in for each team individually, etc...

No matter how I set it up the values come out wierd though?

Thanks in advance.

Signature

Tails

Reply to this Message

Tails - 22 Aug 2004 22:00 GMT

> > I see two fields named Date in your field list. You should be able to set
> up
> > your crosstab with a column heading of
> > Format([DateField],"dddd")
> > Set the Column Headings property to
> > Column Headings: "Sunday", "Monday","Tuesday",..."Saturday"

Just to elaborate this is the SQL for what I have tried

TRANSFORM Count(DATA.PATH) AS CountOfPATH
SELECT DATA.DATE, DATA.TEAM, Count(DATA.PATH) AS [Total Of PATH]
FROM DATA
GROUP BY DATA.DATE, DATA.TEAM
PIVOT DATA.DAY;

It is giving really high values though - as if it is counting monday 1/1/03
(or whatever, made that date up) 9:00am as being 1 instance, then 9:30 as
being another.

I want to IGNORE the times completely - just count any time on monday 1/1/03
as being 1 single occourence, then any time on monday 8/1/03 as being
another single occurence.

Am I explaining what I mean right?

Thanks in advance.

Signature

Tails

Reply to this Message

Duane Hookom - 23 Aug 2004 05:04 GMT

Try this:
TRANSFORM Count(DATA.PATH) AS CountOfPATH
SELECT DATA.TEAM, Count(DATA.PATH) AS [Total Of PATH]
FROM DATA
GROUP BY DATA.TEAM
PIVOT DATA.DAY;

Signature

Duane Hookom
MS Access MVP

> > > I see two fields named Date in your field list. You should be able to
> set
[quoted text clipped - 23 lines]
>
> Thanks in advance.

Reply to this Message

Tails - 23 Aug 2004 07:42 GMT

> Try this:
> TRANSFORM Count(DATA.PATH) AS CountOfPATH
> SELECT DATA.TEAM, Count(DATA.PATH) AS [Total Of PATH]
> FROM DATA
> GROUP BY DATA.TEAM
> PIVOT DATA.DAY;

Thanks again for your help.

That seems to be counting each occurence of monday in the table (so monday
at 9:30 is being counted as well as monday at 10:00) rather than just once
for each particular occasion?

I'm convinced that the data.date needs to be in there as well but I can't
quite get the syntax to get it right?

Thanks again.

Signature

Tails

Reply to this Message

Duane Hookom - 23 Aug 2004 07:56 GMT

How about providing about 10-12 records and how you would expect them to
appear in your results. I don't have a clue how you want to display the
data.

Signature

Duane Hookom
MS Access MVP

> > Try this:
> > TRANSFORM Count(DATA.PATH) AS CountOfPATH
[quoted text clipped - 13 lines]
>
> Thanks again.

Reply to this Message

Tails - 23 Aug 2004 21:50 GMT

> How about providing about 10-12 records and how you would expect them to
> appear in your results. I don't have a clue how you want to display the
> data.

I have posted below some examples of the data.

I would like to see in the report that there was TWO tuesdays for CSV and 1
for WEB, QLEAP and CSV

Then 1 wednesday for QLEAP and ABC and 2 for BTC

The times should be ignored, I just want to count the number of unique days
that calls came in for each team.

Hope that clarifies it and thanks again for taking the time to look at this!

Signature

Tails
DATA PATH TIME OFFER ANS ABN DATE DAY TEAM
7679 08:00 2 2 0 14/01/2003 TUESDAY CSV
7679 08:30 9 9 0 14/01/2003 TUESDAY CSV
7679 09:00 8 7 12.5 14/01/2003 TUESDAY CSV
7679 09:30 15 10 33.3 14/01/2003 TUESDAY CSV
7679 10:00 3 3 33.3 14/01/2003 TUESDAY CSV
7679 10:30 9 6 33.3 14/01/2003 TUESDAY CSV
7679 08:00 5 0 100 21/01/2003 TUESDAY CSV
7679 08:30 7 3 57.1 21/01/2003 TUESDAY CSV
7679 09:00 6 0 100 21/01/2003 TUESDAY CSV
7679 09:30 12 5 58.3 21/01/2003 TUESDAY CSV

DATA PATH TIME OFFER ANS ABN DATE DAY TEAM
7527 10:00 1 1 0 14/01/2003 TUESDAY WEB
7527 11:00 1 0 100 14/01/2003 TUESDAY WEB
7527 11:30 2 0 100 14/01/2003 TUESDAY WEB
7527 12:30 1 0 100 14/01/2003 TUESDAY WEB
7527 13:00 2 1 50 14/01/2003 TUESDAY WEB
7527 14:30 1 0 100 14/01/2003 TUESDAY QLEAP
7527 16:00 1 0 100 14/01/2003 TUESDAY CSV
7527 17:00 2 2 0 14/01/2003 TUESDAY CSV
7527 18:00 1 0 100 14/01/2003 TUESDAY CSV

DATA PATH TIME OFFER ANS ABN DATE DAY TEAM
7676 09:00 1 1 0 15/01/2003 WEDNESDAY QLEAP
7676 13:30 1 1 0 15/01/2003 WEDNESDAY QLEAP
7676 15:30 1 1 0 15/01/2003 WEDNESDAY QLEAP
7676 16:30 4 0 100 15/01/2003 WEDNESDAY QLEAP
7562 11:30 1 1 0 15/01/2003 WEDNESDAY ABC
7562 15:00 1 1 0 15/01/2003 WEDNESDAY ABC
7562 16:30 1 1 0 15/01/2003 WEDNESDAY ABC
7566 10:00 1 1 0 15/01/2003 WEDNESDAY BTC
7566 13:00 1 0 100 22/01/2003 WEDNESDAY BTC
7566 13:30 1 2 0 22/01/2003 WEDNESDAY BTC

Reply to this Message

Duane Hookom - 24 Aug 2004 05:27 GMT

The key is "I just want to count the number of unique days that calls came
in for each team."

I would first create a totals query of unique dates so that
7679 08:00 2 2 0 14/01/2003 TUESDAY CSV
7679 08:30 9 9 0 14/01/2003 TUESDAY CSV
7679 09:00 8 7 12.5 14/01/2003 TUESDAY CSV
7679 09:30 15 10 33.3 14/01/2003 TUESDAY CSV
7679 10:00 3 3 33.3 14/01/2003 TUESDAY CSV
7679 10:30 9 6 33.3 14/01/2003 TUESDAY CSV
results in only one record. You can create a crosstab based on this totals
query.

Signature

Duane Hookom
MS Access MVP

> > How about providing about 10-12 records and how you would expect them to
> > appear in your results. I don't have a clue how you want to display the
[quoted text clipped - 11 lines]
>
> Hope that clarifies it and thanks again for taking the time to look at this!

Reply to this Message