I am trying to custom-format a report in Microsoft Access.  This
should be a snap for anyone familiar with formatting reports and
creating them outside of the wizard, but I don?t have time to teach
myself.

All of the information I want to represent is located in one table
(along with other data that I do not need in the report)

I am having trouble grouping the report such that information is
displayed in a tabular way while also showing multiple data elements
in the detail field.

Long story short, I have shown the data table below, along with my
ideal report format.  There are many companies, and many years? worth
of data, but hopefully this will give you an idea?

Can anyone show me the steps required to format a
query/crosstab/report that will display the information in this way?

Raw Data Table:
Company	        Month	     Marketing 	Materials HR
Microsoft	1/1/2005	$343	$23	$23
Microsoft	2/1/2005	$56	$4	$96
Microsoft	3/1/2005	$4	$643	$896
Microsoft	4/1/2005	$534	$756	$45
Microsoft	5/1/2005	$6	$724	$8
Microsoft	6/1/2005	$4	$3	$95
Microsoft	7/1/2005	$35	$3	$976
Microsoft	8/1/2005	$243	$53	$6
Microsoft	9/1/2005	$3	$554	$58
Microsoft	10/1/2005	$56	$64	$674
Microsoft	11/1/2005	$45	$674	$5
Microsoft	12/1/2005	$6	$567	$66
McSorley's	1/1/2005	$32	$45	$79
McSorley's	2/1/2005	$2	$64	$6
McSorley's	3/1/2005	$343	$234	$458
McSorley's	4/1/2005	$65	$34	$8
McSorley's	5/1/2005	$456	$53	$4
McSorley's	6/1/2005	$47	$645	$456
McSorley's	7/1/2005	$56	$6	$456
McSorley's	8/1/2005	$75	$467	$57
McSorley's	9/1/2005	$6	$456	$56
McSorley's	10/1/2005	$7	$75	$678
McSorley's	11/1/2005	$46	$67	$84
McSorley's	12/1/2005	$345	$56	$45
McDonald's	1/1/2005	$23	$78	$3
McDonald's	2/1/2005	$453	$6	$7
McDonald's	3/1/2005	$64	$754	$43
McDonald's	4/1/2005	$745	$64	$5
McDonald's	5/1/2005	$7	$56	$6
McDonald's	6/1/2005	$458	$5	$64
McDonald's	7/1/2005	$3	$63	$645
McDonald's	8/1/2005	$674	$5	$564
McDonald's	9/1/2005	$56	$343	$45
McDonald's	10/1/2005	$3	$234	$5
McDonald's	11/1/2005	$345	$523	$3
McDonald's	12/1/2005	$3	$3	$2


Desired Report Format:

Company		        Total	Q1	Q2	Q3	Q4

Microsoft						
	Marketing Cost	$1,335	$403	$544	$281	$107
	Materials Cost	$4,068	$670	$1,483	$610	$1,305
	HR Cost	        $2,948	$1,015	$148	$1,040	$745
	Total Cost	$8,351	$2,088	$2,175	$1,931	$2,157
						
McSorley's						
	Marketing Cost	$1,480	$377	$568	$137	$398
	Materials Cost	$2,202	$343	$732	$929	$198
	HR Cost	        $2,387	$543	$468	$569	$807
	Total Cost	$6,069	$1,263	$1,768	$1,635	$1,403
						
McDonald's						
	Marketing Cost	$2,834	$540	$1,210	$733	$351
	Materials Cost	$2,134	$838	$125	$411	$760
	HR Cost	        $1,392	$53	$75	$1,254	$10
	Total Cost	$6,360	$1,431	$1,410	$2,398	$1,121

well, I would do this...

1st create a query or two to mainpulate the data into a more usable format
tblData being the data table

---------qryData1
SELECT tblData.Company, DatePart("q",[Mnth]) AS Quarter,
Sum(tblData.Marketing) AS SumOfMarketing, Sum(tblData.Materials) AS
SumOfMaterials, Sum(tblData.HR) AS SumOfHR,
Sum((tblData.Marketing+tblData.Materials+tblData.HR)) AS Totals
FROM tblData
GROUP BY tblData.Company, DatePart("q",[Mnth]);
-----------
this would give you output like 
Company		Quarter	SumOfMarketing	SumOfMaterials	SumOfHR	Totals
McDonald's	1	£540.00		£838.00		£53.00	£1431.00
McDonald's	2	£1,210.00	£125.00		£75.00	£1410.00
McDonald's	3	£733.00		£411.00		£1254.00 £2398.00
McDonald's	4	£351.00		£760.00		£10.00	£1121.00

then
--------qryData2
SELECT qryData1.Company, Sum(IIf([Quarter]=1,[SumOfMarketing],0)) AS
MarketingQ1, Sum(IIf([Quarter]=2,[SumOfMarketing],0)) AS MarketingQ2,
Sum(IIf([Quarter]=3,[SumOfMarketing],0)) AS MarketingQ3,
Sum(IIf([Quarter]=4,[SumOfMarketing],0)) AS MarketingQ4,
Sum(qryData1.SumOfMarketing) AS MarketingTotal,
Sum(IIf([Quarter]=1,[SumOfMaterials],0)) AS MaterialsQ1,
Sum(IIf([Quarter]=2,[SumOfMaterials],0)) AS MaterialsQ2,
Sum(IIf([Quarter]=3,[SumOfMaterials],0)) AS MaterialsQ3,
Sum(IIf([Quarter]=4,[SumOfMaterials],0)) AS MaterialsQ4,
Sum(qryData1.SumOfMaterials) AS MaterialsTotal,
Sum(IIf([Quarter]=1,[SumOfHR],0)) AS HRQ1,
Sum(IIf([Quarter]=2,[SumOfHR],0)) AS HRQ2,
Sum(IIf([Quarter]=3,[SumOfHR],0)) AS HRQ3,
Sum(IIf([Quarter]=4,[SumOfHR],0)) AS HRQ4, Sum(qryData1.SumOfHR) AS
HRTotal
FROM qryData1
GROUP BY qryData1.Company;
-----------
For an output like this
Company		MarketingQ1	MarketingQ2	MarketingQ3	....
McDonald's	£540.00		£1,210.00	£733.00		....
McSorley's	£377.00		£568.00		£137.00		....
Microsoft	£403.00		£544.00		£281.00		....


Then you can base your report on that collated data.

There are two ways to get the report result you want:
1. by re-aling your table as such that $ figure on the same column of
the table, and creat a label for the department.
2. by getting different department separately with a query.
I recommend the first approach.

Email me I'll send you the Access file.

gjsfus

Attachment:
	RptTable2
	Company	Dept	Total Of 	Qtr 1	Qtr 2	Qtr 3	Qtr 4
	McDonald's
	HR	$1,392.00	$53.00	$75.00	$1,254.00	$10.00
	Marketing	$2,834.00	$540.00	$1,210.00	$733.00	$351.00
	Materials	$2,134.00	$838.00	$125.00	$411.00	$760.00
	McSorley's
	HR	$2,387.00	$543.00	$468.00	$569.00	$807.00
	Marketing	$1,480.00	$377.00	$568.00	$137.00	$398.00
	Materials	$2,202.00	$343.00	$732.00	$929.00	$198.00
	Microsoft
	HR	$2,948.00	$1,015.00	$148.00	$1,040.00	$745.00
	Marketing	$1,335.00	$403.00	$544.00	$281.00	$107.00
	Materials	$4,068.00	$670.00	$1,483.00	$610.00	$1,305.00

Hi,
The following are the steps to create the Query and report.

First I have to get the raw data table in a format where each row represents 
the cost of  Marketing , Material, Hours  is separate rows 
for example
Company	        Month	     Marketing 	Materials HR
Microsoft	1/1/2005	$343	$23	$23

In this I need a query output as follows


Company   , Year , Quarter , Cost  Mktg
Microsoft   2005    1        343   Marketing
Microsoft   2005    1        23    Materials
Microsoft   2005    1        23    HR

To do this I make a small union query. You can actually cut and paste
this in the Query design

Steps
1 Click on the Query Tab

2 Create Query in Design View

3 A show table will come. Click Close

4 Click on the View Menu

5 Click on SQL

6  In the Screen you will get the following text
------------
  SELECT
WITH OWNERACCESS OPTION;
-----------

   Delete this whole section

7  Paste the following SQL

-----------------------------------------------------------
SELECT Company.Company, DatePart("yyyy",[Company].[QMonth]) AS QYear,
DatePart("q",[Company].[QMonth]) AS Quarter, Sum(Company.Marketing) AS
SumOfMarketing, "Marketing" AS Mktg
FROM Company
GROUP BY Company.Company, Company.QMonth,
DatePart("yyyy",[Company].[QMonth]), DatePart("q",[Company].[QMonth])
HAVING (((Company.Company) Is Not Null))
ORDER BY Company.Company, DatePart("yyyy",[Company].[QMonth]),
DatePart("q",[Company].[QMonth])
union
SELECT Company.Company, DatePart("yyyy",[Company].[QMonth]) AS QYear,
DatePart("q",[Company].[QMonth]) AS Quarter, Sum(Company.Materials) AS
SumOfMarketing, "Materials" AS Mktg
FROM Company
GROUP BY Company.Company, Company.QMonth,
DatePart("yyyy",[Company].[QMonth]), DatePart("q",[Company].[QMonth])
HAVING (((Company.Company) Is Not Null))
ORDER BY Company.Company, DatePart("yyyy",[Company].[QMonth]),
DatePart("q",[Company].[QMonth])
union
SELECT Company.Company, DatePart("yyyy",[Company].[QMonth]) AS QYear,
DatePart("q",[Company].[QMonth]) AS Quarter, Sum(Company.HR) AS
SumOfMarketing, "HR" AS Mktg
FROM Company
GROUP BY Company.Company, Company.QMonth,
DatePart("yyyy",[Company].[QMonth]), DatePart("q",[Company].[QMonth])
HAVING (((Company.Company) Is Not Null))
ORDER BY Company.Company, DatePart("yyyy",[Company].[QMonth]),
DatePart("q",[Company].[QMonth])
----------------------------------------------


8  Save the Query. Save the Query as Say Quarter

9  Close the window.

10 Click on the Query Tab

11 Click on Create Query by using wizard
    
12 Click on the New Icon

13 Choose Cross Tab Query Wizard

14 In the View . Click on the Radio Button for Queries.

15 Choose Quarter

16 Click on Next

17  Choose Company, QYear,MKTG columns

18  Click on the Next button

19  Click on Quarter . ( This gives the headings)

20  click on Sum of Marketting

21 Click on Sum on the functions list box

22  Click Next

23 Click on Save.


This will give you a query in your format.



20