Search code examples
sqlms-accessleft-joindistinct

Counting Unique IDs In a LEFT/RIGHT JOIN Query in Access

I am working on a database to track staff productivity. Two of the ways we do that is by monitoring the number of orders they fulfil and by tracking their error rate.

Each order they finish is recorded in a table. In one day they can complete many orders. It is also possible for a single order to have multiple errors.

I am trying to create a query that provides a summary of their results. This query should have one column with "TotalOrders" and another with "TotalErrors".

I connect the two tables with a LEFT/RIGHT join since not all orders will have errors.

The problem comes when I want to total the number of orders. If someone made multiple mistakes on an order, that order gets counted multiple times; once for each error.

I want to modify my query so that when counting the number of orders it only counts records with distinct OrderID's; yet, in the same query, also count the total errors without losing any.

Is this possible?

Here is my SQL

SELECT Count(tblTickets.TicketID) AS TotalOrders, 
       Count(tblErrors.ErrorID) AS TotalErrors 
FROM tblTickets 
LEFT JOIN tblErrors ON tblTickets.TicketID = tblErrors.TicketID;

I have played around with SELECT DISTINCT and UNION but am struggling with the correct syntax in Access. Also, a lot of the examples I have seen are trying to total a single field rather than two fields in different ways.

To be clear when totalling the OrderCount field I want to only count records with DISTINCT TicketID's. When totalling the ErrorCount field I want to count ALL errors.

Ticket = Order.

Query Result: Order Count Too High

Ticket/Order Table: Total of 14 records

Error Table: You can see two errors for the same order on 8th

Solution

    1. do a query that counts orders by staff from Orders table and a query that counts errors by staff from Errors table then join those two queries to Staff table (queries can be nested for one long SQL statement)

    2. correlated subqueries

    SELECT Staff.*, 
    (SELECT Count(*) FROM Orders WHERE StaffID = Staff.ID) AS CntOrders, 
    (SELECT Count(*) FROM Errors WHERE StaffID = Staff.ID) AS CntErrors
FROM Staff;
    1. use DCount() domain aggregate function

    Option 1 is probably the most efficient and option 3 the least.