Crosstab Date Range Problem

[Al]

Hi hope someone can help with an annoying crosstab problem.

I have set up a crosstab query to show sales qty to each customer, by month,
over a consecutive period January 2006-July 2008. I use the DatePart function
to look at the sales date and get the month number. The problem is when I run
my query it sums April 2006 sales with April 2007 sales and April 2008 sales
etc because it sees them all as month 4 irrespective of year. So I end up
with only months 1-12 as my column headings when I really want them to read
left to right in chronological order Jan 2006, Feb 2006 ...... July 2008.

TIA

 

[scubadiver]

If you use month number in isolation then it will compile the months from
each year. You haven't stated your set up but I would probably use

expr1: format([sales date], "mm-yy")

 

[Al]

Top man. Perfect fix.

If you use month number in isolation then it will compile the months from
each year. You haven't stated your set up but I would probably use

expr1: format([sales date], "mm-yy")

Hi hope someone can help with an annoying crosstab problem.

I have set up a crosstab query to show sales qty to each customer, by month,
over a consecutive period January 2006-July 2008. I use the DatePart function
to look at the sales date and get the month number. The problem is when I run
my query it sums April 2006 sales with April 2007 sales and April 2008 sales
etc because it sees them all as month 4 irrespective of year. So I end up
with only months 1-12 as my column headings when I really want them to read
left to right in chronological order Jan 2006, Feb 2006 ...... July 2008.

TIA

 

[John Spencer]

Or if you want the months in chronological order instead of April 2005
next to April 2006, use the format

Format([Sales Date], "yyyy mm")



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

If you use month number in isolation then it will compile the months from
each year. You haven't stated your set up but I would probably use

expr1: format([sales date], "mm-yy")

Hi hope someone can help with an annoying crosstab problem.

I have set up a crosstab query to show sales qty to each customer, by month,
over a consecutive period January 2006-July 2008. I use the DatePart function
to look at the sales date and get the month number. The problem is when I run
my query it sums April 2006 sales with April 2007 sales and April 2008 sales
etc because it sees them all as month 4 irrespective of year. So I end up
with only months 1-12 as my column headings when I really want them to read
left to right in chronological order Jan 2006, Feb 2006 ...... July 2008.

TIA