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 |
#11
|
|||
|
|||
Crosstab more than a years data??
Please post back with the SQL view of your query. Did you remove the Column
Headings from your previous query? -- Duane Hookom Microsoft Access MVP "Bill" wrote: RowHead: Format([#DateAssign],"yyyy-mm") The "yyy-mm" bit doesn't work. The crosstab still builds but the intersection values are empty!! Any ideas? "Duane Hookom" wrote in message ... If you want a different line for each employee then you should use assignee as the column heading. Your Row Heading would be something like: RowHead: Format([#DateAssign],"yyyy-mm") You could set the value of the crosstab to count CallID. -- Duane Hookom Microsoft Access MVP "Bill" wrote: y axis would be number of calls x axis would be time, probably months. Preferably the graph would have one line for each assignee, hence the need for one timeline, not one for each year. Cheers. Bill. "Duane Hookom" wrote in message ... Do you have an idea what you want your graphs to look like? Do you want each assignee to have their own graph or what? -- Duane Hookom Microsoft Access MVP "Bill" wrote: Well that is helpful but the next step is doing a graph to look at trend. So really it would be better, though I accept not pretty, if the results were in one row. Unless there is a way of creating a graph from the data in the format that this produces?? If possible I want to have to do as little each time as possible because this is going to be a regular monthly report. Thanks. Bill "Duane Hookom" wrote in message ... This all depends on how you want to separate years. This is one solution: TRANSFORM Count([q_Assignments to Tim].CallID) AS CountOfCallID SELECT [q_Assignments to Tim].Assignee, Year([#DateAssign]) as Yr, Count([q_Assignments to Tim].CallID) AS [Total Of CallID] FROM [q_Assignments to Tim] GROUP BY [q_Assignments to Tim].Assignee, Year([#DateAssign]) PIVOT Format([#DateAssign],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec"); -- Duane Hookom Microsoft Access MVP "Bill" wrote: Crosstab query wizard stuff I'm afraid, are there any online resources to help get more from crosstabs? I did try the replacement you suggested, maybe I did it wrong, but it didn't work!! Current SQL:- TRANSFORM Count([q_Assignments to Tim].CallID) AS CountOfCallID SELECT [q_Assignments to Tim].Assignee, Count([q_Assignments to Tim].CallID) AS [Total Of CallID] FROM [q_Assignments to Tim] GROUP BY [q_Assignments to Tim].Assignee PIVOT Format([#DateAssign],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec"); Regards. Bill. "John Spencer" wrote in message ... Post the SQL view of your query. I suspect that you are pivoting on just the month and not the month and year. Try replacing Month(DateField) with Format([DateField], "yyyy-mm") -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County . "Bill" wrote in message ... Hi All, I would like to use a crosstab query to analyse monthly data over a longer period than a year but it doesn't seem to be able to do that, instead the second years (and the rest) data just accumulate under each month total. Any pointers please? Ta. Bill. |
#12
|
|||
|
|||
Crosstab more than a years data??
Hertis!
TRANSFORM Count([q_Assignments to Tim].CallID) AS CountOfCallID SELECT Year([#DateAssign]) AS Expr1, [q_Assignments to Tim].Assignee, Count([q_Assignments to Tim].CallID) AS [Total Of CallID] FROM [q_Assignments to Tim] GROUP BY Year([#DateAssign]), [q_Assignments to Tim].Assignee PIVOT Format([#DateAssign],"yyyy-mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec"); "Duane Hookom" wrote in message ... Please post back with the SQL view of your query. Did you remove the Column Headings from your previous query? -- Duane Hookom Microsoft Access MVP "Bill" wrote: RowHead: Format([#DateAssign],"yyyy-mm") The "yyy-mm" bit doesn't work. The crosstab still builds but the intersection values are empty!! Any ideas? "Duane Hookom" wrote in message ... If you want a different line for each employee then you should use assignee as the column heading. Your Row Heading would be something like: RowHead: Format([#DateAssign],"yyyy-mm") You could set the value of the crosstab to count CallID. -- Duane Hookom Microsoft Access MVP "Bill" wrote: y axis would be number of calls x axis would be time, probably months. Preferably the graph would have one line for each assignee, hence the need for one timeline, not one for each year. Cheers. Bill. "Duane Hookom" wrote in message ... Do you have an idea what you want your graphs to look like? Do you want each assignee to have their own graph or what? -- Duane Hookom Microsoft Access MVP "Bill" wrote: Well that is helpful but the next step is doing a graph to look at trend. So really it would be better, though I accept not pretty, if the results were in one row. Unless there is a way of creating a graph from the data in the format that this produces?? If possible I want to have to do as little each time as possible because this is going to be a regular monthly report. Thanks. Bill "Duane Hookom" wrote in message ... This all depends on how you want to separate years. This is one solution: TRANSFORM Count([q_Assignments to Tim].CallID) AS CountOfCallID SELECT [q_Assignments to Tim].Assignee, Year([#DateAssign]) as Yr, Count([q_Assignments to Tim].CallID) AS [Total Of CallID] FROM [q_Assignments to Tim] GROUP BY [q_Assignments to Tim].Assignee, Year([#DateAssign]) PIVOT Format([#DateAssign],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec"); -- Duane Hookom Microsoft Access MVP "Bill" wrote: Crosstab query wizard stuff I'm afraid, are there any online resources to help get more from crosstabs? I did try the replacement you suggested, maybe I did it wrong, but it didn't work!! Current SQL:- TRANSFORM Count([q_Assignments to Tim].CallID) AS CountOfCallID SELECT [q_Assignments to Tim].Assignee, Count([q_Assignments to Tim].CallID) AS [Total Of CallID] FROM [q_Assignments to Tim] GROUP BY [q_Assignments to Tim].Assignee PIVOT Format([#DateAssign],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec"); Regards. Bill. "John Spencer" wrote in message ... Post the SQL view of your query. I suspect that you are pivoting on just the month and not the month and year. Try replacing Month(DateField) with Format([DateField], "yyyy-mm") -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County . "Bill" wrote in message ... Hi All, I would like to use a crosstab query to analyse monthly data over a longer period than a year but it doesn't seem to be able to do that, instead the second years (and the rest) data just accumulate under each month total. Any pointers please? Ta. Bill. |
#13
|
|||
|
|||
Crosstab more than a years data??
My previous suggestion was to change the row and column headings. Apparently
something was lost in translation. My assumption regarding what you want might best be graphed from: TRANSFORM Count([q_Assignments to Tim].CallID) AS CountOfCallID SELECT Format([#DateAssign],"yyyy-mmm") As YrMth, Count([q_Assignments to Tim].CallID) AS [Total Of CallID] FROM [q_Assignments to Tim] GROUP BY Format([#DateAssign],"yyyy-mmm") PIVOT [q_Assignments to Tim].Assignee; -- Duane Hookom Microsoft Access MVP "Bill" wrote: Hertis! TRANSFORM Count([q_Assignments to Tim].CallID) AS CountOfCallID SELECT Year([#DateAssign]) AS Expr1, [q_Assignments to Tim].Assignee, Count([q_Assignments to Tim].CallID) AS [Total Of CallID] FROM [q_Assignments to Tim] GROUP BY Year([#DateAssign]), [q_Assignments to Tim].Assignee PIVOT Format([#DateAssign],"yyyy-mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec"); "Duane Hookom" wrote in message ... Please post back with the SQL view of your query. Did you remove the Column Headings from your previous query? -- Duane Hookom Microsoft Access MVP "Bill" wrote: RowHead: Format([#DateAssign],"yyyy-mm") The "yyy-mm" bit doesn't work. The crosstab still builds but the intersection values are empty!! Any ideas? "Duane Hookom" wrote in message ... If you want a different line for each employee then you should use assignee as the column heading. Your Row Heading would be something like: RowHead: Format([#DateAssign],"yyyy-mm") You could set the value of the crosstab to count CallID. -- Duane Hookom Microsoft Access MVP "Bill" wrote: y axis would be number of calls x axis would be time, probably months. Preferably the graph would have one line for each assignee, hence the need for one timeline, not one for each year. Cheers. Bill. "Duane Hookom" wrote in message ... Do you have an idea what you want your graphs to look like? Do you want each assignee to have their own graph or what? -- Duane Hookom Microsoft Access MVP "Bill" wrote: Well that is helpful but the next step is doing a graph to look at trend. So really it would be better, though I accept not pretty, if the results were in one row. Unless there is a way of creating a graph from the data in the format that this produces?? If possible I want to have to do as little each time as possible because this is going to be a regular monthly report. Thanks. Bill "Duane Hookom" wrote in message ... This all depends on how you want to separate years. This is one solution: TRANSFORM Count([q_Assignments to Tim].CallID) AS CountOfCallID SELECT [q_Assignments to Tim].Assignee, Year([#DateAssign]) as Yr, Count([q_Assignments to Tim].CallID) AS [Total Of CallID] FROM [q_Assignments to Tim] GROUP BY [q_Assignments to Tim].Assignee, Year([#DateAssign]) PIVOT Format([#DateAssign],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec"); -- Duane Hookom Microsoft Access MVP "Bill" wrote: Crosstab query wizard stuff I'm afraid, are there any online resources to help get more from crosstabs? I did try the replacement you suggested, maybe I did it wrong, but it didn't work!! Current SQL:- TRANSFORM Count([q_Assignments to Tim].CallID) AS CountOfCallID SELECT [q_Assignments to Tim].Assignee, Count([q_Assignments to Tim].CallID) AS [Total Of CallID] FROM [q_Assignments to Tim] GROUP BY [q_Assignments to Tim].Assignee PIVOT Format([#DateAssign],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec"); Regards. Bill. "John Spencer" wrote in message ... Post the SQL view of your query. I suspect that you are pivoting on just the month and not the month and year. Try replacing Month(DateField) with Format([DateField], "yyyy-mm") -- John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County . "Bill" wrote in message ... Hi All, I would like to use a crosstab query to analyse monthly data over a longer period than a year but it doesn't seem to be able to do that, instead the second years (and the rest) data just accumulate under each month total. Any pointers please? Ta. Bill. |
|
Thread Tools | |
Display Modes | |
|
|