Crosstab more than a years data??

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

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

Try replacing Month(DateField) with
Format([DateField], "yyyy-mm")

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

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.

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.

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.

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.

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.

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.

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.