Sorting, Grouping, Ordering on Reports

[84hickman]

I have a rather large database.
One table contains Area Manager names, Divisions, and numerous statistics/totals. These statistics are labeled with the month from which they are from.

I am trying to make a report that will show the top 25 managers, sorted by one column of statistics. A Third column will show the same statistic from last year.

Ex:
Manager X ManagerX'sTotal1Jan10 ManagerX'sTotal1Jan09
Manager G ManagerG'sTotal1Jan10 ManagerG'sTotal1Jan09
Manager A ManagerA'sTotal1Jan10 ManagerA'sTotal1Jan09

The issue that I am having is that I cannot get the report to order the rows by the second column.

The query that I am using contains the area manager name, date(Criteria, Jan09 orJan10), and the following two columns:

Total1: IIf([date]=#Jan09#,[Statistic1],0)
Total2: IIf([date]=#Jan10#,[Statistic1],0)


I have edited all of the Table and Field names and formatting only to simplify the asking of this question.

Any help would REALLY be appreciated!
Thank you!

 

[84hickman]

Currently my query gives me 50 records.
25 with Statistic1 for Jan10 and 0s in the column for Jan 09 and
25 record with Statistic1 for Jan09 and 0s in the column for Jan10.

If I could edit my query so that it would return only 25 records I think that the major problem would be solved.

Thanks again!

 

[CT Witter]

Please post your query SQL.

 

[84hickman]

SELECT AMgrTotals.s_division, AMgrTotals.amgrname, IIf([m_month]=#1/1/2010#,[SumOfytd_NetIncome$],0) AS [Actual YTD Contribution], IIf([m_month]=#1/1/2009#,[sumofytd_NetIncome$],0) AS [2009 YTD Contribution]


FROM AMgrTotals


GROUP BY AMgrTotals.s_division, AMgrTotals.amgrname, IIf([m_month]=#1/1/2010#,[SumOfytd_NetIncome$],0), IIf([m_month]=#1/1/2009#,[sumofytd_NetIncome$],0), AMgrTotals.m_month, AMgrTotals.s_division


HAVING (((AMgrTotals.s_division)<>"84 components") AND ((AMgrTotals.amgrname)<>"installed sales") AND ((AMgrTotals.m_month)=#1/1/2010#)) OR (((AMgrTotals.s_division)<>"84 components") AND ((AMgrTotals.amgrname)<>"installed sales") AND ((AMgrTotals.m_month)=#1/1/2009#))


ORDER BY IIf([m_month]=#1/1/2010#,[SumOfytd_NetIncome$],0) DESC;

 

[84hickman]

Any help would really be appreciated... this report really is driving me a bit insane!!