Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2002
    Posts
    6

    Unanswered: How find the record count from SQL output ?

    Hi, I want to find out how many records return from the SQL, I have tried this but didn't work, please help. Thanks Matt

    Select Count(CountOfQuote_ID) From (SELECT Count(tbl_Quotes.Quote_ID) AS CountOfQuote_ID, Format(tbl_Quotes.Quote_Date, "mmm yyyy") AS MonthYear, Year(tbl_Quotes.Quote_Date) AS YearDate, Month(tbl_Quotes.Quote_Date) AS MonthDate
    FROM tbl_Quotes
    GROUP BY Format(tbl_Quotes.Quote_Date, "mmm yyyy"), Year(tbl_Quotes.Quote_Date), Month(tbl_Quotes.Quote_Date)
    ORDER BY Year(tbl_Quotes.Quote_Date), Month(tbl_Quotes.Quote_Date)))

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

  3. #3
    Join Date
    Jul 2002
    Posts
    6
    Sorry Rudy, I don't get it. Do you mind using your way to change my code and show me this solution ?

    Thanks
    Matt

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
       SELECT Count(tbl_Quotes.Quote_ID) 
                AS CountOfQuote_ID
            , Format(tbl_Quotes.Quote_Date, "mmm yyyy") 
                AS MonthYear
            , Year(tbl_Quotes.Quote_Date) 
                AS YearDate
            , Month(tbl_Quotes.Quote_Date) 
                AS MonthDate 
         FROM tbl_Quotes 
     GROUP BY Format(tbl_Quotes.Quote_Date, "mmm yyyy")
            , Year(tbl_Quotes.Quote_Date)
            , Month(tbl_Quotes.Quote_Date) 
    
    UNION ALL
    
       SELECT Count(tbl_Quotes.Quote_ID) 
            , "---Total"
            , 9999
            , 99
         FROM tbl_Quotes 
    
     ORDER BY 3
            , 4
    note that ORDER BY 3,4 is year, month -- this is deprecated but should work in most databases

  5. #5
    Join Date
    Aug 2002
    Posts
    1

    Lightbulb known

    Originally posted by matkwan
    Sorry Rudy, I don't get it. Do you mind using your way to change my code and show me this solution ?

    Thanks
    Matt

Posting Permissions

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