CODEWITHSUNDEEP
sqlms-access
Darkestlyrics
Darkestlyrics

Reputation: 310

Ms-Access: counting from 2 tables

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.

Upvotes: 0

Views: 237

Answers (4)

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112772

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.

Upvotes: 0

Walter Mitty
Walter Mitty

Reputation: 18950

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.

Upvotes: 0

Kiran Hegde
Kiran Hegde

Reputation: 3681

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

Upvotes: 0

Rono
Rono

Reputation: 3381

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

Upvotes: 3

Related Questions