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