Crosstab Date Range Problem

  • Thread starter Thread starter Al
  • Start date Start date
A

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
 
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")
 
Top man. Perfect fix.

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



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