If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Crosstab Query should do this?
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 -- Tails (The not so expert 'Expert' of their office) |
#2
|
|||
|
|||
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" -- Duane Hookom MS Access MVP "Tails" wrote in message ... 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 -- Tails (The not so expert 'Expert' of their office) |
#3
|
|||
|
|||
"Duane Hookom" wrote in message ... 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" -- Duane Hookom MS Access MVP 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 |
#4
|
|||
|
|||
"Tails" wrote in message ... "Duane Hookom" wrote in message ... 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 |
#5
|
|||
|
|||
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 "Tails" wrote in message news "Tails" wrote in message ... "Duane Hookom" wrote in message ... 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 |
#6
|
|||
|
|||
"Duane Hookom" wrote in message ... 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 |
#7
|
|||
|
|||
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 "Tails" wrote in message ... "Duane Hookom" wrote in message ... 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 |
#8
|
|||
|
|||
"Duane Hookom" wrote in message ... 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 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 |
#9
|
|||
|
|||
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. -- Duane Hookom MS Access MVP "Tails" wrote in message ... "Duane Hookom" wrote in message ... 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 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
crosstab query | chris morrison | Running & Setting Up Queries | 2 | July 12th, 2004 08:57 PM |
Crosstab query oddity | Duane Hookom | Running & Setting Up Queries | 2 | July 4th, 2004 07:41 PM |
Crosstab query with same VALUE in multiple columns | ChrisJ | Running & Setting Up Queries | 1 | June 20th, 2004 10:41 PM |
Showing all subrows in crosstab query | Ragnar Midtskogen | Running & Setting Up Queries | 3 | May 26th, 2004 08:16 PM |
Dates In a CrossTab query | Box 666 | New Users | 1 | May 21st, 2004 04:01 AM |