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.

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.

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.