Report Output Help

[steve711]

I am having a tough time getting my report format to look like my old Excel results.

I would like to have SalesPerson down the first column and JAN FEB etc across the top. Earch SalesPerson row would contain 2 numbers one for new and used cars sold for that particular month.

Currently I have all the information on my report but it is Grouped by Salesperson then below you have the individual month and new used totals. Ugly looking.

SalesPerson	JAN	FEB	MAR
	             N U        N U         N U
Mark	             1 0	3 2	
Ken	             3 5	4 1

This is how I am trying to get it to look without much luck.
Any nudges in the right direction would be great.

 

[Rich]

Use a crosstab query

 

[steve711]

Thanks Rich.

One small problem. When I input the sales data for the salesperson I select whether the vehicle is NEW or USED. So for each month I need to see the total of NEW and the total of USED. The crosstab worked beautifully but it is totaling the NEW & USED instead of separating the two.

How can I stipulate that into the query? On my other badly formatted report I am using count(iif to get the totals.

 

[steve711]

Figured it out. I added to the crosstab query

Total New: Count(IIf([vehNewUsed]="New",0))
Total Used:Count(IIf([vehNewUsed]="Used",0))

Thanks for the nudge in the right direction.

The only problem with my expression is that it only creates an overall total of NEW and USED and not total per month NEW and USED. Any help on that one?

 

[Rich]

Use the Month function and add the months ie, "Jan","Feb" etc in the Column Headings property