Page 1 of 2 12 LastLast
Results 1 to 15 of 25

Thread: SQL Query Help

  1. #1
    Join Date
    Oct 2010
    Posts
    14

    Unanswered: SQL Query Help

    I am the access beginner, studying the access database.
    the case like this:
    For all Managers who manage more than one staff
    How to list the manager staff ID and their name and the total number of staff they managed.


    StaffID Name MangerID
    1 Peter
    2 Tony 1
    3 Danny 2
    4 Cherry 2
    5 Monk 1
    6 Akiko 5
    7 Tommy 1
    8 Sarah 7

    Thank you!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    For all Managers = FROM daTable AS m INNER JOIN daTable AS e ON e.MangerID = m.StaffID

    who manage more than one staff = HAVING COUNT(*) > 1

    How to list the manager staff ID and their name = SELECT e.MangerID,m.Name

    and the total number of staff they managed = COUNT(*)

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2010
    Posts
    14
    table name: datable


    SELECT e.MangerID, m.Name, Count(*)
    From datable AS m INNER Join datable AS e ON e.MangerID = m.StaffID
    Having Count(*)>1;


    Please advise the SQL with error? it can not execute

    Suppose the output should be:
    StaffID Name Totle
    1 Peter 3
    2 Tony 2

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ellis2010 View Post
    Please advise the SQL with error?
    yes, it has error

    i left something out, on purpose
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Oct 2010
    Posts
    14
    Oh, ...anyway thanks a lot
    I am a beginner.
    Could advise with correct one.?
    does it need sub-query?
    i can predict the output but not know the SQL.

  6. #6
    Join Date
    Oct 2010
    Posts
    14
    which only one table , why need INNER Join?
    but anyway , i feel upset.

  7. #7
    Join Date
    Oct 2010
    Posts
    14
    Please kindly help to teach me....

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    a) In the SQL statement that r937 kindly provided, you have to replace "datable" by the name of the table that contains the employees in your database, of course!

    b) An Inner Join is used because the manager is also an employee, then he also has an entry in the table.
    Have a nice day!

  9. #9
    Join Date
    Oct 2010
    Posts
    14
    thanks for your sharing.
    but i have failed with different try.
    Sorry about that , could you give me more hints.
    i am really a beginner.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ellis2010 View Post
    could you give me more hints.
    please share your latest query, and explain what happened when you tested it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Oct 2010
    Posts
    14
    SELECT e.MangerID, m.Name, Count(*) as Total
    From datable AS m INNER Join datable AS e
    Wherr e.MangerID = m.StaffID and Count(*)>1;

    Please kindly advise.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ellis2010 View Post
    Please kindly advise.
    you have INNER JOIN (correct) but you forgot the ON clause

    also, you cannot use COUNT(*) in the WHERE clause

    and you still haven't figured out what is missing

    don't you have any reference material, handouts from your course, books, manuals, help files, or tutorials?

    go look up some COUNT(*) queries and see if you can find out te part of the querythat's missing

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Oct 2010
    Posts
    14
    SELECT e.MangerID, m.Name, Count(Distinct MangerID) as Total
    From datable AS m INNER Join datable AS e ON e.MangerID = m.StaffID
    Wherr e.MangerID = m.StaffID and Count(*)>1;
    Group by m.StaffID


    Pls.....again.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    nice try... what happened when you tested that?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Oct 2010
    Posts
    14
    You tried to execute a query that does not include the specified expression ' MangerID' as part of an aggregate function. error

Posting Permissions

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