Create a crosstab query grouped by month over 3 years

G

Guest

Hi
I'm an Access newbie. I have a sales table that has sales date for the last
4 years. I need to create a crosstab query by month for 3 years. The col
headings would be Jan 2003, Jan 2004, Jan 2005, Feb 2003 etc. When I use the
format function to display both month and year, then the months are sorted
alphabetically.
Any ideas?
Thanks
Heather
 
M

[MVP] S.Clark

The 'ColumnHeadings' property of the crosstab query can help.

The drawback is that you have to enter every possible value from Jan 2001 to
Dec 2005. If the query has to slide across a four year period,(i.e. Go back
4 years from any given current day or month), then this becomes a real
maintenance problem. But, like all things in Access, if there's a problem,
VBA can solve it.

So, try the property, and post other questions if needed.

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
(e-mail address removed)
www.fmsinc.com/consulting
 
W

Wolfgang Kais

Hi HEather:

HEather said:
I have a sales table that has sales date for the last 4 years. I need
to create a crosstab query by month for 3 years. The col headings
would be Jan 2003, Jan 2004, Jan 2005, Feb 2003 etc.
When I use the format function to display both month and year,
then the months are sorted alphabetically. Any ideas?

The format function is ok for the column headings. You can use a
property of the crosstab query in the properties window: ColumnHeadings.
Set this property to: "Jan 2003", "Jan 2004", "Jan 2005", "Feb 2003" etc.
This will cause Access to display these headings in the specified order.
 
G

Guest

Wolfgang Kais said:
Hi HEather:



The format function is ok for the column headings. You can use a
property of the crosstab query in the properties window: ColumnHeadings.
Set this property to: "Jan 2003", "Jan 2004", "Jan 2005", "Feb 2003" etc.
This will cause Access to display these headings in the specified order.

--
Regards,
Wolfgang


Thanks for the response, however that is going to be tedious as the need would be to query different date ranges for different types of sales analyses.
Thanks
Heather
 
G

Guest

Thany you for your response, but as you point out, if we want to continually
query on new or different date ranges then this will become tedious. What
would be the VBA solution?
Thanks
Heather
 
M

[MVP] S.Clark

The following crosstab is an example from the Northwind database:

TRANSFORM Sum(CCur([Order
Details].UnitPrice*[Quantity]*(1-[Discount])/100)*100) AS ProductAmount
SELECT Products.ProductName, Orders.CustomerID, Year([OrderDate]) AS
OrderYear
FROM Products INNER JOIN (Orders INNER JOIN [Order Details] ON
Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order
Details].ProductID
WHERE (((Orders.OrderDate) Between #1/1/1997# And #12/31/1997#))
GROUP BY Products.ProductName, Orders.CustomerID, Year([OrderDate])
PIVOT "Qtr " & DatePart("q",[OrderDate],1,0) In ("Qtr 1","Qtr 2","Qtr
3","Qtr 4");

Notice the important pieces:

TRANSFORM blah, blah, blah
SELECT blah, blah, blah
FROM blah, blah, blah
GROUP BY blah, blah, blah
PIVOT ... IN ("Qtr 1","Qtr 2","Qtr 3","Qtr 4")

The VBA solution would be to deduce what should be in parenthesis of IN
clause, after the PIVOT. (Note that the Transform PIVOT really make a
crosstab a crosstab.) You can do this for either the SQL directly, meaning
open the QueryDef and change the SQL property, or change the ColumnHeading
property directly.

In the case that you would like to make the out go to a report, then you
could change the label captions on the report.


--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
(e-mail address removed)
www.fmsinc.com/consulting
 

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


Top