Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2008
    Posts
    120

    Unanswered: Count using an Inner Join !!! - Help

    Hi all....
    I'm trying to figure out how to 'count' the number of records returned from my select statement. The statement uses and INNER JOIN which makes it a bit more complex.
    Can anyone help with this one please?
    Thanks
    Andy


    Code:
    SELECT Actions.ActionID, Actions.Date_Raised, Actions.Department
    FROM Actions INNER JOIN Department ON Actions.Department = Department.Department
    WHERE (((Actions.Date_Raised) Between #2/1/2012# And #2/29/2012#) AND ((Actions.Department)="Administration"));

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    How about (fast but Null will be counted):
    Code:
    SELECT COUNT (*)
    FROM Actions INNER JOIN Department ON Actions.Department = Department.Department
    WHERE (((Actions.Date_Raised) Between #2/1/2012# And #2/29/2012#) AND ((Actions.Department)="Administration"));
    Or (slower but Null in [ActionID] won't be counted):
    Code:
    SELECT COUNT ([ActionID])
    FROM Actions INNER JOIN Department ON Actions.Department = Department.Department
    WHERE (((Actions.Date_Raised) Between #2/1/2012# And #2/29/2012#) AND ((Actions.Department)="Administration"));
    Or even:
    Code:
    SELECT COUNT (*)
    FROM ( SELECT Actions.ActionID, 
                  Actions.Date_Raised, 
                  Actions.Department
             FROM Actions INNER JOIN Department 
               ON Actions.Department = Department.Department
            WHERE ((Actions.Date_Raised Between #2/1/2012# And #2/29/2012#) AND
                   (Actions.Department="Administration"))
         );
    Have a nice day!

Posting Permissions

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