Create report with three rows of data representing years

[Guest]

Hi,

I have a fairly complex report that I am trying to design. I have a query
called SalesByMonth which lists the CustomerName and the fields for all
months since January 2005. I want to create a report that has CustomerName
and Year as row headers and the months as column headers. The report would
look like the following"

CustomerName Year January February March April May June................
ABC Corp. 2005 $100 $200 $300 $400 $500 $600
2006 $150 $250 $350 $450 $550 $650
2007 $225 $325 $425 $525 $625 $725

Can anyone help me with this?

Thanks,

 

[Guest]

Hi,
Did you try CROSS-TAB QUERY?

 

[Guest]

Hi,

Thanks for your help. I have not but could use some help. Based on my
query which has CustomerName, Jan05, Feb05, Mar05.....Feb07, how would you do
a cross tab query? Right now I don't have the field Year for 2005, 2006 and
2007.

Thanks,

 

[Guest]

If you actually have field names that are months then you can normalize the
table with a union query and then create a crosstab based on your union query.

It would help if you told us exactly how your data is stored.

 

[Guest]

Duane,

I have a customer table called Customer with fields that include
CustomerName, CustomerID and CustomerType. I then have created a table for
every month since January 2005 that includes CustomerID and Monthly Sales.
The Monthly Sale field name is different for every table (ie Jan05, Feb05,
Mar05...Feb07). I then create a long series of queries that join all of the
tables based on CustomerID using Left Joins. The final query so far called
Feb07query has CustomerName, CustomerId and the every month as a field. But
I want to format things as described below. I a a novice user. I know a
little about cross tabs queries but could use some help.

Thanks,

 

[Guest]

You should not generally have a table for every month or a field with the
name of a month. I would scratch your current monster query and build a union
query.

SELECT CustomerID, Jan05 as Sales, #1/1/2005# as SalesDate
FROM tblJan2005
UNION ALL
SELECT CustomerID, Feb05, #2/1/2005#
FROM tblFeb2005
UNION ALL
SELECT CustomerID, Mar05, #3/1/2005#
FROM tblMar2005
UNION ALL
....
SELECT CustomerID, Feb07, #2/1/2007#
FROM tblFeb2007;

You can then base this a crosstab query on this union query with your
customer table. Use Customer Name and Year(SalesDate) as Row Headings,
Format(SalesDate,"mmmm") as the Column Heading, and Sum of Sales as the Value.

 

[Guest]

Duane,

Thanks. This has been very helpful. One problem though is with sorting my
columns by month. In my SQL code I have the field Format(SalesDate,"mmmm")
AS [Month] which correctly assigns the month based on the SalesDate field. I
have chosen this field as my column header but it is placing the months in
alphabetical rather than chronological order. So it goes April, August,
December rather than January, February, March. Is there a way to have it
format chronologically?

Thanks,

 

[Guest]

The months should generate column headings so sorting should not be an issue.
Add every month name to the Column Headings property to ensure you get all
the months generated in your crosstab.
--
Duane Hookom
Microsoft Access MVP

Duane,

Thanks. This has been very helpful. One problem though is with sorting my
columns by month. In my SQL code I have the field Format(SalesDate,"mmmm")
AS [Month] which correctly assigns the month based on the SalesDate field. I
have chosen this field as my column header but it is placing the months in
alphabetical rather than chronological order. So it goes April, August,
December rather than January, February, March. Is there a way to have it
format chronologically?

Thanks,
--
Chuck W

Duane,

I have a customer table called Customer with fields that include
CustomerName, CustomerID and CustomerType. I then have created a table for
every month since January 2005 that includes CustomerID and Monthly Sales.
The Monthly Sale field name is different for every table (ie Jan05, Feb05,
Mar05...Feb07). I then create a long series of queries that join all of the
tables based on CustomerID using Left Joins. The final query so far called
Feb07query has CustomerName, CustomerId and the every month as a field. But
I want to format things as described below. I a a novice user. I know a
little about cross tabs queries but could use some help.

Thanks,