Crosstab more than a years data??

  • Thread starter Thread starter Bill
  • Start date Start date
B

Bill

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.
 
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
..
 
Try replacing Month(DateField) with
Format([DateField], "yyyy-mm")
Click to expand...

Which will give you 24 columns if your data covers 2 years. Great, if that's
what you want.

You could also add Year([DateField]) to the Cross tab as a RowHeader field,
giving you separate 2006 & 2007 lines for each of the 12 monthly columns.

HTH,


John Spencer said:
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 said:
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.
Click to expand...
Click to expand...
 
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 said:
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 said:
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.
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
.

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.
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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.


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
.

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.
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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


:

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.


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
.

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.
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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


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


:

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.


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
.

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.
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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


:

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


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


:

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.


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
.

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.
Click to expand...
Click to expand...
Click to expand...
 
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 said:
RowHead: Format([#DateAssign],"yyyy-mm")
Click to expand...

The "yyy-mm" bit doesn't work. The crosstab still builds but the
intersection values are empty!!
Any ideas?


Duane Hookom said:
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 said:
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.



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


:

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


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


:

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.


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
.

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.
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
RowHead: Format([#DateAssign],"yyyy-mm")
Click to expand...

The "yyy-mm" bit doesn't work. The crosstab still builds but the
intersection values are empty!!
Any ideas?


Duane Hookom said:
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


:

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.



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


:

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


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


:

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.


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
.

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.
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
RowHead: Format([#DateAssign],"yyyy-mm")

The "yyy-mm" bit doesn't work. The crosstab still builds but the
intersection values are empty!!
Any ideas?


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


:

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.



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


:

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


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


:

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.


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
.

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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top