Prev: Back-up
Next: Page (tabbesturingselement)
From: Al on 4 Jul 2008 07:09
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
From: scubadiver on 4 Jul 2008 08:29

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" wrote:

> 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
From: Al on 4 Jul 2008 09:26
Top man. Perfect fix.

"scubadiver" wrote:

>
> 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" wrote:
>
> > 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
From: John Spencer on 4 Jul 2008 09:37
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
'====================================================


scubadiver wrote:
> 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" wrote:
>
>> 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
 | 
Pages: 1
Prev: Back-up
Next: Page (tabbesturingselement)