ranking two reports

[guidop12]

I have a report that runs off the below query

SELECT TOP 50 dbo_tblCustomers.Company, Sum(nz([RevenueTotal],0)) AS [Total
Revenue]
FROM (dbo_tblCustomers INNER JOIN dbo_tblOrders ON
dbo_tblCustomers.CustomerID = dbo_tblOrders.CustomerID) INNER JOIN
qryRevenueDetailTotals ON dbo_tblOrders.OrderID =
qryRevenueDetailTotals.OrderID
WHERE (((dbo_tblOrders.CloseDate) Between [Forms]![frmReportsMenu]![Date1]
And [Forms]![frmReportsMenu]![Date2] And (dbo_tblOrders.CloseDate) Between
[Forms]![frmReportsMenu]![Date1] And [Forms]![frmReportsMenu]![Date2] And
(dbo_tblOrders.CloseDate) Between [Forms]![frmReportsMenu]![Date1] And
[Forms]![frmReportsMenu]![Date2] And (dbo_tblOrders.CloseDate) Between
[Forms]![frmReportsMenu]![Date1] And [Forms]![frmReportsMenu]![Date2]))
GROUP BY dbo_tblCustomers.Company
ORDER BY Sum(nz([RevenueTotal],0)) DESC;

The report actually has two subreports attached to it where the user will
enter a date range for the first and then enter a date range for the second.
The report basically looks like this

Revenue First Year Revenue Second Year
Comp Total Revenue Comp Total Revenue
College 1,234,444 College 934,345
Food Market 943,456 Press 856,499
Press 924,000 Food Market 800,000

Is it possible to make a query to rank each company from one year to the
next. Ex: Both Colleges will be one, Food MArket will be 2 for the first yr
but 3 for the second yr. and so on

Any help will be appreciated.
Please ask me any questions if this doesn't make sense.

 

[KARL DEWEY]

Try this ---
SELECT TOP 50 dbo_tblCustomers.Company, IIF(dbo_tblOrders.CloseDate
<DateAdd(“yyyyâ€, 1, [Forms]![frmReportsMenu]![Date1]),
Sum(nz([RevenueTotal],0))) AS [Revenue First Year],
IIF(dbo_tblOrders.CloseDate >=DateAdd(“yyyyâ€, 1,
[Forms]![frmReportsMenu]![Date1]), Sum(nz([RevenueTotal],0))) AS [Revenue
Second Year]
FROM (dbo_tblCustomers INNER JOIN dbo_tblOrders ON
dbo_tblCustomers.CustomerID = dbo_tblOrders.CustomerID) INNER JOIN
qryRevenueDetailTotals ON dbo_tblOrders.OrderID =
qryRevenueDetailTotals.OrderID
WHERE (((dbo_tblOrders.CloseDate) Between [Forms]![frmReportsMenu]![Date1]
And [Forms]![frmReportsMenu]![Date2]
GROUP BY dbo_tblCustomers.Company
ORDER BY IIF(dbo_tblOrders.CloseDate <DateAdd(“yyyyâ€, 1,
[Forms]![frmReportsMenu]![Date1]), Sum(nz([RevenueTotal],0))) DESC,
IIF(dbo_tblOrders.CloseDate >=DateAdd(“yyyyâ€, 1,
[Forms]![frmReportsMenu]![Date1]), Sum(nz([RevenueTotal],0))) DESC;