Access SQL Join and Count query with multiple tables

Question

Scenario:

I have 2 tables, Car Table, and Employee Table. Pretty simple tables, both tables however include a location code:

╔════════════════╗    ╔═══════════╗
║ Employee Table ║    ║ Car Table ║
╠════════════════╣    ╠═══════════╣
║ empID          ║    ║ CarID     ║
║ empName        ║    ║ CarName   ║
║ LocID          ║    ║ LocID     ║
╚════════════════╝    ╚═══════════╝

This is to represent Employees working at specific locations, and also shows which vehicles are in the respective locations.

What I am trying to display with this query is a count of each car, and employee GROUPED BY Location ID, for example:

Example of what I want

Every time I try a query, it always adds multiple location ids in the query, even though I want to group by it. Is this possible with one query?

It's generally frowned on to use external images for your sample output. That alone may account for the down vote somebody gave you. Beyond that it's also ideal that you give us some indication of what you've tried. — shawnt00
– shawnt00, Commented Mar 18, 2018 at 19:55

shawnt00 · Accepted Answer · 2018-03-18 20:28:57Z

select e.LocId, EmpCount, CarCount
from
    (select LocId, count(*) as EmpCount from Employee group by LocId) as e
    inner join
    (select LocId, count(*) as CarCount from Car group by LocId) as c
        on c.LocId = e.LocId

Here's the general idea though I don't think that's perfect syntax for Access as I'm sure it must be missing a pair of parentheses somewhere. Since you have a many to many relationship you'll need to be able to collapse to a single row before joining them up.

I'm also assuming that every location will be represented by both an employee and a car. Perhaps you can get away with a left outer join if that's not completely true. As I believe that Access doesn't allow full outer join you'll need another workaround if you can't make assumptions along those lines.

It might also work to use count(distinct) but I think it'll be faster using the above approach if the tables are large.

select e.LocId, count(distinct e.EmpId) as EmpCount, count(distinct c.CarId) as CarCount
from Employee as e inner join Car as c on c.LocId = e.LocId

cdaiga · Accepted Answer · 2018-03-18 19:58:50Z

0

Try this:

SELECT CASE WHEN (A.LocID IS NULL) THEN B.LocID ELSE A.LocID END
   LocID, Count(A.CarID), Count(B.empID) FROM Car A FULL OUTER JOIN
   Employee B ON A.LocID=B.LocID
GROUP BY A.LocID, B.LocID;

answered Mar 18, 2018 at 19:58

cdaiga

4,9374 gold badges25 silver badges44 bronze badges

Collectives™ on Stack Overflow

Access SQL Join and Count query with multiple tables

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related