Crosstab : PIVOT ... IN (...) problem !

A

Arnaud Lesauvage

Hi group !
I have a problem with a crosstab query in Access 2000.

I want to pivot by month.
The table contains only data fromthe last 12 months (including the
current one), so there are only 12 values for Format([date_month],'mmmm
yyyy').
For the moment, is ranges from 'January 2006' to 'December 2006').

If I run the query with only PIVOT Format([date_month],'mmmm yyyy'), the
columns are in akphabetical order (april 2006, august 2006, ...).
I want them in chronological order of course, so I tried adding IN (...)
to the query, but if I run :

TRANSFORM Nz(Sum(l.total),0) AS [SommeDeTotal Annuel]
SELECT l.page, l.sortindex, Sum(l.total) AS [Total Annuel]
FROM log_analyse_croisee AS l
GROUP BY l.page, l.sortindex
PIVOT Format([date_month],'mmmm yyyy')
In (Format(DateAdd(m,-11,Now()) 'mmmm yyyy'),
Format(DateAdd('m',-10,Now()), 'mmmm yyyy'),
Format(DateAdd('m',-9,Now()), 'mmmm yyyy'),
Format(DateAdd('m',-8,Now()), 'mmmm yyyy'),
Format(DateAdd('m',-7,Now()), 'mmmm yyyy'),
Format(DateAdd('m',-6,Now()), 'mmmm yyyy'),
Format(DateAdd('m',-5,Now()), 'mmmm yyyy'),
Format(DateAdd('m',-4,Now()), 'mmmm yyyy'),
Format(DateAdd('m',-3,Now()), 'mmmm yyyy'),
Format(DateAdd('m',-2,Now()), 'mmmm yyyy'),
Format(DateAdd('m',-1,Now()), 'mmmm yyyy'), Format(Now(), 'mmmm yyyy'))


I have an error :
), [ or element absent in expression 'Format([date_month],'mmmm yyyy')
In (Format(DateAdd(m,-11,Now()'

What is the problem here ?
Thanks for your help !
 
J

John Spencer

Your posted SQL seems to be missing a comma in the line
In (Format(DateAdd(m,-11,Now()) 'mmmm yyyy'),
that should be
In (Format(DateAdd(m,-11,Now()), 'mmmm yyyy'),


TRANSFORM Nz(Sum(l.total),0) AS [SommeDeTotal Annuel]
SELECT l.page, l.sortindex, Sum(l.total) AS [Total Annuel]
FROM log_analyse_croisee AS l
GROUP BY l.page, l.sortindex
PIVOT Format([date_month], 'mmmm yyyy')
In (Format(DateAdd(m,-11,Now()), 'mmmm yyyy'),
Format(DateAdd('m',-10,Now()), 'mmmm yyyy'),
Format(DateAdd('m',-9,Now()), 'mmmm yyyy'),
Format(DateAdd('m',-8,Now()), 'mmmm yyyy'),
Format(DateAdd('m',-7,Now()), 'mmmm yyyy'),
Format(DateAdd('m',-6,Now()), 'mmmm yyyy'),
Format(DateAdd('m',-5,Now()), 'mmmm yyyy'),
Format(DateAdd('m',-4,Now()), 'mmmm yyyy'),
Format(DateAdd('m',-3,Now()), 'mmmm yyyy'),
Format(DateAdd('m',-2,Now()), 'mmmm yyyy'),
Format(DateAdd('m',-1,Now()), 'mmmm yyyy'),
Format(Now(), 'mmmm yyyy'))
 
A

Arnaud Lesauvage

John Spencer a écrit :
Your posted SQL seems to be missing a comma in the line
In (Format(DateAdd(m,-11,Now()) 'mmmm yyyy'),
that should be
In (Format(DateAdd(m,-11,Now()), 'mmmm yyyy'),
Click to expand...

Sorry, it was a typo.
The query giving the error is :

TRANSFORM Nz(Sum(l.total),0) AS [SommeDeTotal Annuel]
SELECT l.page, l.sortindex, Sum(l.total) AS [Total Annuel]
FROM log_analyse_croisee AS l
GROUP BY l.page, l.sortindex
PIVOT Format([date_month], 'mmmm yyyy')
In (Format(DateAdd(m,-11,Now()), 'mmmm yyyy'),
Format(DateAdd('m',-10,Now()), 'mmmm yyyy'),
Format(DateAdd('m',-9,Now()), 'mmmm yyyy'),
Format(DateAdd('m',-8,Now()), 'mmmm yyyy'),
Format(DateAdd('m',-7,Now()), 'mmmm yyyy'),
Format(DateAdd('m',-6,Now()), 'mmmm yyyy'),
Format(DateAdd('m',-5,Now()), 'mmmm yyyy'),
Format(DateAdd('m',-4,Now()), 'mmmm yyyy'),
Format(DateAdd('m',-3,Now()), 'mmmm yyyy'),
Format(DateAdd('m',-2,Now()), 'mmmm yyyy'),
Format(DateAdd('m',-1,Now()), 'mmmm yyyy'),
Format(Now(), 'mmmm yyyy'));

So the comma IS present !

Regards
 
J

John Spencer

That same line has another error in it that I didn't see the first time.
There are no apostrophe marks around the "M" in the DateAdd function.
If this is also, a typo then may I recommend that you copy the actual SQL
statement and paste it into your message. At least, that way we won't be
fixing the wrong things.
In (Format(DateAdd(m,-11,Now()), 'mmmm yyyy'),
becomes
In (Format(DateAdd('m',-11,Now()), 'mmmm yyyy'),

Try troubleshooting, by adding just the first calculated value. If that
works then add the next five, etc.

If it fails with just the one calculated value, then I would check and make
sure that you have no missing references. Perhaps that is the problem.

An aside, I don't like using a number as an alias for a table. I would use
either letters or at least a letter followed by a number. This is a
personal preference, but I think it might be too easy for confusion to
develop in the query if I were doing some math and decided to subtract 1
from some field. Is the SQL engine going to treat that 1 as a numeric value
or is it going to treat it as a reference to a table. And even if the SQL
engine doesn't get confused, I probably will.


Arnaud Lesauvage said:
John Spencer a écrit :
Your posted SQL seems to be missing a comma in the line
In (Format(DateAdd(m,-11,Now()) 'mmmm yyyy'),
that should be
In (Format(DateAdd(m,-11,Now()), 'mmmm yyyy'),
Click to expand...

Sorry, it was a typo.
The query giving the error is :

TRANSFORM Nz(Sum(l.total),0) AS [SommeDeTotal Annuel]
SELECT l.page, l.sortindex, Sum(l.total) AS [Total Annuel]
FROM log_analyse_croisee AS l
GROUP BY l.page, l.sortindex
PIVOT Format([date_month], 'mmmm yyyy')
In (Format(DateAdd(m,-11,Now()), 'mmmm yyyy'),
Format(DateAdd('m',-10,Now()), 'mmmm yyyy'),
Format(DateAdd('m',-9,Now()), 'mmmm yyyy'),
Format(DateAdd('m',-8,Now()), 'mmmm yyyy'),
Format(DateAdd('m',-7,Now()), 'mmmm yyyy'),
Format(DateAdd('m',-6,Now()), 'mmmm yyyy'),
Format(DateAdd('m',-5,Now()), 'mmmm yyyy'),
Format(DateAdd('m',-4,Now()), 'mmmm yyyy'),
Format(DateAdd('m',-3,Now()), 'mmmm yyyy'),
Format(DateAdd('m',-2,Now()), 'mmmm yyyy'),
Format(DateAdd('m',-1,Now()), 'mmmm yyyy'),
Format(Now(), 'mmmm yyyy'));

So the comma IS present !

Regards
Click to expand...
 
A

Arnaud Lesauvage

John Spencer a écrit :
That same line has another error in it that I didn't see the first time.
There are no apostrophe marks around the "M" in the DateAdd function.
If this is also, a typo then may I recommend that you copy the actual SQL
statement and paste it into your message. At least, that way we won't be
fixing the wrong things.
In (Format(DateAdd(m,-11,Now()), 'mmmm yyyy'),
becomes
In (Format(DateAdd('m',-11,Now()), 'mmmm yyyy'),

Try troubleshooting, by adding just the first calculated value. If that
works then add the next five, etc.

If it fails with just the one calculated value, then I would check and make
sure that you have no missing references. Perhaps that is the problem.
Click to expand...


The query without the "IN" part works fine :

TRANSFORM Nz(Sum(l.total),0) AS [SommeDeTotal Annuel]
SELECT l.page, l.sortindex, Sum(l.total) AS [Total Annuel]
FROM log_analyse_croisee AS l
GROUP BY l.page, l.sortindex
PIVOT Format([date_month],"mmmm yyyy");


Just adding the IN breaks the query ! :

TRANSFORM Nz(Sum(l.total),0) AS [SommeDeTotal Annuel]
SELECT l.page, l.sortindex, Sum(l.total) AS [Total Annuel]
FROM log_analyse_croisee AS l
GROUP BY l.page, l.sortindex
PIVOT Format([date_month],"mmmm yyyy")
IN (Format(DateAdd("m", -11, Now), "mmmm yyyy");

I tried some variations, and just adding :
IN (Format(Now(),"mmmm yyyy"))
breaks the query.

I think that any parenthesis in the "IN" part breaks the query !

An aside, I don't like using a number as an alias for a table. I would use
either letters or at least a letter followed by a number. This is a
personal preference, but I think it might be too easy for confusion to
develop in the query if I were doing some math and decided to subtract 1
from some field. Is the SQL engine going to treat that 1 as a numeric value
or is it going to treat it as a reference to a table. And even if the SQL
engine doesn't get confused, I probably will.
Click to expand...


The alias is not a "1" (one), it is the letter "L" in lower case. ;)

Regards
 
J

John Spencer

Yes, I get the same results. Should have tested it earlier.

The only thing I can think of is to Pivot using something like the
following.

Pivot Format(Date_Month, '(yymm) mmmm yyyy')

That is a bit kludgy, but I don't see any other way to do this in a crosstab

or perhaps use a relative month - something like the following - which would
work well in a report, since you could set the caption of the column labels
(of the report) based on the current date.

Pivot "Month" & Format( 12 - DateDiff('m',Date_Month, Date()),'00') In
('Month01','Month02',..., 'Month12')

Arnaud Lesauvage said:
John Spencer a écrit :
That same line has another error in it that I didn't see the first time.
There are no apostrophe marks around the "M" in the DateAdd function.
If this is also, a typo then may I recommend that you copy the actual SQL
statement and paste it into your message. At least, that way we won't be
fixing the wrong things.
In (Format(DateAdd(m,-11,Now()), 'mmmm yyyy'),
becomes
In (Format(DateAdd('m',-11,Now()), 'mmmm yyyy'),

Try troubleshooting, by adding just the first calculated value. If that
works then add the next five, etc.

If it fails with just the one calculated value, then I would check and
make sure that you have no missing references. Perhaps that is the
problem.
Click to expand...


The query without the "IN" part works fine :

TRANSFORM Nz(Sum(l.total),0) AS [SommeDeTotal Annuel]
SELECT l.page, l.sortindex, Sum(l.total) AS [Total Annuel]
FROM log_analyse_croisee AS l
GROUP BY l.page, l.sortindex
PIVOT Format([date_month],"mmmm yyyy");


Just adding the IN breaks the query ! :

TRANSFORM Nz(Sum(l.total),0) AS [SommeDeTotal Annuel]
SELECT l.page, l.sortindex, Sum(l.total) AS [Total Annuel]
FROM log_analyse_croisee AS l
GROUP BY l.page, l.sortindex
PIVOT Format([date_month],"mmmm yyyy")
IN (Format(DateAdd("m", -11, Now), "mmmm yyyy");

I tried some variations, and just adding :
IN (Format(Now(),"mmmm yyyy"))
breaks the query.

I think that any parenthesis in the "IN" part breaks the query !

An aside, I don't like using a number as an alias for a table. I would
use either letters or at least a letter followed by a number. This is a
personal preference, but I think it might be too easy for confusion to
develop in the query if I were doing some math and decided to subtract 1
from some field. Is the SQL engine going to treat that 1 as a numeric
value or is it going to treat it as a reference to a table. And even if
the SQL engine doesn't get confused, I probably will.
Click to expand...


The alias is not a "1" (one), it is the letter "L" in lower case. ;)

Regards
Click to expand...
 
A

Arnaud Lesauvage

John Spencer a écrit :
Yes, I get the same results. Should have tested it earlier.

The only thing I can think of is to Pivot using something like the
following.

Pivot Format(Date_Month, '(yymm) mmmm yyyy')

That is a bit kludgy, but I don't see any other way to do this in a crosstab

or perhaps use a relative month - something like the following - which would
work well in a report, since you could set the caption of the column labels
(of the report) based on the current date.

Pivot "Month" & Format( 12 - DateDiff('m',Date_Month, Date()),'00') In
('Month01','Month02',..., 'Month12')
Click to expand...

Hi John, thanks for investigating this further.
It seems that using functions in column headings is not well supported in Access.
I circumvented this problem by adding some code in my report's onopen event. It
dynamically changes the report's recordsource and the column headings.

Thanks anyway for helping me on this !

Regards
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Cross-Tab Query 2
PIVOT Question 6
Sort by Month in a query 3
Report Year Problem 3
Consecutive months 2
QueryHelp 5
Access shuts down, when i try to save this query 12
Crosstab Query - Column sorting problem - Ascending 3

Top