Count how many times certain fields in records with repeating values are found (or not found) in another table

First of all, I wish to say hi to the community here. The posts here have been a great help with VBA but this is my first question ever. I have a task that I need to solve in SQL (in MS Access) but it’s sort of new to me and the task seems to be too complex.

I have a table in Access with the following structure(let’s call it Tinvoices):

invoice | year | date       | company | step | agent
5110001 | 2019 | 15/01/2019 | 1201    | 0    | John
5110001 | 2019 | 15/01/2019 | 1201    | 1    | Jack
5110002 | 2019 | 10/02/2019 | 1202    | 0    | John
5110002 | 2019 | 10/02/2019 | 1202    | 1    | Jack
5110002 | 2019 | 10/02/2019 | 1202    | 2    | Daniel
5110002 | 2019 | 10/02/2019 | 1202    | 3    | John
5110003 | 2019 | 12/03/2019 | 1205    | 0    | Jack
5110003 | 2019 | 12/03/2019 | 1205    | 1    | Daniel
5110003 | 2019 | 12/03/2019 | 1205    | 2    | David

This table relates to actions on invoices. Invoices and their related data are repeated with each step. There is another table, which contains agents belonging to a certain department (let’s call it Tdeptusers):

agent
John
Jack

What I need to do is the following. Have distinct lines for the invoices (the most unique key is combining the invoice, year and company) and counting in separate steps have been done by users in the Tdeptusers table and how many by users who are not in Tdeptusers. Something like this:

invoice | year | month | company | actionsByOurDept | actionsByOthers
5110001 | 2019 | 1     | 1201    | 2                | 0
5110002 | 2019 | 2     | 1202    | 3                | 1
5110003 | 2019 | 3     | 1205    | 1                | 2

I’m kind of a beginner, so you’ll have to excuse me in providing usable codes. Being a complete beginner, I got stuck after the absolute basics. I have stuff like this:

SELECT 
invoice, 
year, 
DatePart("m", Date) AS month, 
company, 
Sum(IIf(i.agent IN(d.agent), 1, 0)) AS actionsByOurDept, 
Sum(IIf(i.agent IN(d.agent), 0, 1)) AS actionsByOthers
FROM Tinvoices AS i, Tdeptusers AS d
GROUP BY invoice, year, DatePart("m", Date), company;

This doesn’t give back the desired result, mostly not in actionsByOthers, instead I get huge numbers. Maybe something similar to this solution might work but I haven’t been able to do it. Much appreciation for the help, folks.

Answer

Use proper standard explicit JOIN syntax :

SELECT i.invoice, year, DatePart("m", i.Date) AS month, i.company, 
      SUM( IIF(d.agent IS NOT NULL, 1, 0) ) AS actionsByOurDept, 
      SUM( IIf(d.agent IS NULL, 1, 0) ) AS actionsByOthers
FROM Tinvoices AS i LEFT JOIN
     Tdeptusers AS d
     ON d.agent = i.agent
GROUP BY i.invoice, i.year, DatePart("m", i.Date), i.company;

Advertisement

Answer