Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2008

    Unanswered: Ranking two differnet reports

    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.

  2. #2
    Join Date
    Nov 2007
    Adelaide, South Australia
    If this gets no replies, it will be because you've made it hard to follow. Not only is your SQL (full((of uesless( brackets)))) making it hard to follow (copied from the SQL view directly no doubt), there is no mention of table structure and it is also hard to understand what exactly it is you want to do. On top of that your data sample is really hard to follow.

    Perhaps start with your table structure and a clear definition of what you want to do.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!

    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts