0

I have two tables in a Database

Employee and Stafflink

I need to retrieve the number of staff per manager in the following format

Output

I've been trying to adapt an answer to another question 

SELECT bankNo AS "Bank Number", 
COUNT (*) AS "Total Branches" 
FROM BankBranch 
GROUP BY bankNo

As

SELECT COUNT (*) AS StaffCount , 
Employee.Name AS Name 
FROM Employee, Stafflink 
GROUP BY Name

As I look at the Group BY I'm thinking I should be grouping by The ManID in the Stafflink Table.

My output with this query looks like this

enter image description here

So it is counting correctly but as you can see it's far off the output I need to get. Any advice would be appreciated.

Improve this question
1
  • What are you trying to do? Is your query working, or? Commented Jun 12, 2014 at 12:21

4 Answers 4

Reset to default
3

You need to join the Employee and Stafflink tables. It appears that your FROM clause should look like this:

FROM Employee INNER JOIN StaffLink ON Employee.ID = StaffLink.ManID
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I really need to learn more about Joins, thank you for the help.
@Darkestlyrics: The join is very obvious: You have to link the employee record containing the name of the manager to the corresponding StaffLink record. Otherwise Access does not know how the records of the two tables have to be paired.
0

You have to join the Eployee table twice to get the summary of employees under manager 

select count(*) as StaffCount,Manager.Name
from Employee join Stafflink on employee.Id = StaffLink.EmpId
join Employee as Manager on StaffLink.ManId = Manager.Id
Group by Manager.Name
Improve this answer

5 Comments

Why join the on EmpId? We don't need any additional data for the employees. We just need the Name of the mamanger from the Employee table. The number of employees can be calculated from the number of records in the StaffLink table alone.
yes you are right, more easily can be done as below. select count(*) as StaffCount,Manager.Name from Stafflink join Employee as Manager on StaffLink.ManId = Manager.Id Group by Manager.Name
I would not group on the manager name but on StaffLink.ManId, since only the id is known to be unique.
Since you need Manager name as the output, for the group by you need to include Mnaager.Name
Ok, you are right, you would have to apply a aggregate function to it: SELECT COUNT(*) AS StaffCount, FIRST(Manager.Name) AS ManagerName FROM Stafflink INNER JOIN Employee AS Manager ON StaffLink.ManId = Manager.Id GROUP BY StaffLink.ManId
0

The answers that advise you on how to join are correct, assuming that you want to learn how to use SQL in MS Access. But there is a way to accomplish the same thing using the ACCESS GUI for designing queries, and this involves a shorter learning curve than learning SQL.

The key to using the GUI when more than one table is involved is to realize that you have to define the relationships between tables in the relationship manager. Once you do that, designing the query you are after is a piece of cake, just point and click.

The tricky thing in your case is that there are two relationships between the two tables. One relationship links EmpId to ID and the other links ManId to ID.

If, however, you want to learn SQL, then this shortcut will be a digression.

Improve this answer

Comments

0

If you don't specify a join between the tables, a so called Cartesian product will be built, i.e., each record from one table will be paired with every record from the other table. If you have 7 records in one table and 10 in the other you will get 70 pairs (i.e. rows) before grouping. This explains why you are getting a count of 7 per manager name.

Besides joining the tables, I would suggest you to group on the manager id instead of the manager name. The manager id is known to be unique per manager, but not the name. This then requires you to either group on the name in addition, because the name is in the select list or to apply an aggregate function on the name. Each additional grouping slows down the query; therefore I prefer the aggregate function.

SELECT
    COUNT(*) AS StaffCount,
    FIRST(Manager.Name) AS ManagerName
FROM
    Stafflink
    INNER JOIN Employee AS Manager
        ON StaffLink.ManId = Manager.Id
GROUP BY
    StaffLink.ManId

I don't know if it makes a performance difference, but I prefer to group on StaffLink.ManId than on Employee.Id, since StaffLink is the main table here and Employee is just used as lookup table in this query.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.