How to compare data by month 1

[Marshall2000]

I am attempting to create a report that compares data from 2002 to 2003 by month. I have a query that has the 2002 data with field names h1amount, h1count. it also has 2003 data in fieds cyamount and cycount. I want to compare the data side by side by month.

Example
Month hiamount cyamount h1count cycount
January 1000.00 123 1200.00 112
February 1233.00 122 999.00 99
March 3434.00 343 4353.00 242

What happens now is the 2002 data is displayed and then the 2003 starts at the end of the 2002 data and not side by side.

Thanks in advance for your help.

Marshall

 

[PaulBricker]

You information looks like it should display side by side if you have different field for different years. Can you post a little of what your query data looks like.

Paul

 

[Marshall2000]

SELECT [All retail by year].account, [All retail by year].name, [All retail by year].date, [All retail by year].CYamount, [All retail by year].CYstatunits, [All retail by year].CYCount, [All retail by year].H1amount, [All retail by year].H1statunits, [All retail by year].H1Count
FROM [All retail by year]
GROUP BY [All retail by year].account, [All retail by year].name, [All retail by year].date, [All retail by year].CYamount, [All retail by year].CYstatunits, [All retail by year].CYCount, [All retail by year].H1amount, [All retail by year].H1statunits, [All retail by year].H1Count
HAVING ((([All retail by year].name)=[Forms]![Retail Eyechart Customer]![cboName]))
ORDER BY [All retail by year].name;

 

[PaulBricker]

I was more interested in what the physical records looked like but the sql does help. I seems that in your report, you should Group by Month on your Date field. To do this, you would open the Report in Design view. On the menu bar go to View...Sorting and Grouping. In the Fields/Expression box, select your Date field. Then in the Group Properties set the Header to Yes and set the Group On to Month. Then put your Date field in the Header and your H1 and CY fields in the Detail section. That should give you the info side by side.
Try it out and post back with problems.

Paul

 

[Marshall2000]

Sorry but that didn't work. I still have the month showing twice. The first time shows 2002 data in the h1 fields and the second pass shows the 2003 data in the cy fields as shown.

Month hiamount cyamount h1count cycount
January 1000.00 1200.00
February 1233.00 999.00
March 3434.00 4353.00

Month hiamount cyamount h1count cycount
January 123 112
February 122 99
March 343 242

The problem is the month is being sorted by the entire date and not just the month.

 

[PaulBricker]

Sorry, the site seemed to go down last night as I got ready to post back. What I ended up doing was adding another field to the underlying query before my date field(you can insert a column to the left of your date field in query design view).
MySortatePart("m",[DateField[)

Then I took all the other sorting in the query out and sorted this field Ascending. In the report, I add this field to the Month GroupHeader, and set the GroupOn property to Each Value. It's now an integer so Month doesn't come into play here. Then in the Sorting and Grouping box, I sorted this field ascending. If you want to actually see your data as a text string like January then you can add another field to the GroupHeader and format it =Format([DateField],"mmmm&quot. I think you can then set the Integer field, visible property to No and just display the formatted textbox. If that screws up the sorting, just slide the Formatted textbox over the Integer textbox so it doesn't show.
Try it out and post back with problems.

Paul

 

[Marshall2000]

Thanks for the help Paul. It works perfectly and my data is formatted just the way I wanted it.

Marshall

 

[PaulBricker]

Excellent. Good way to end the Holiday.

Paul