Crosstab Query should do this?

[Tails]

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

 

[Duane Hookom]

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"

 

[Tails]

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.

 

[Tails]

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.

 

[Duane Hookom]

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;


--
Duane Hookom
MS Access MVP

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.

 

[Tails]

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.

 

[Duane Hookom]

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.

--
Duane Hookom
MS Access MVP

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.

 

[Tails]

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!

--
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

 

[Duane Hookom]

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.