Creating a query to count non duplicated patient interactions per day

Question

I am fairly new to Microsoft Access but I recently created a database to better organize our department data. In my database I have a table that our staff can log their interactions with patients. A staff member can have multiple interactions with one patient in a single day but when we report our stats to the boss they only care about "how many patients were seen per staff member per day". And I cannot figure out how to get that done using my query.

To further explain what I need here is an example:

On Monday, employee1 sees patient-A twice, patient-B once and patient-C once. Employee2 sees patient-B once and patient-D once.

On Tuesday employee1 sees patient-A once and employee2 sees patient-C three times.

And the Query should show: employee1 - 4 interactions and employee2 - 3 interactions.

Hopefully I was able to paint a good enough picture.

Thank you, M

Look into grouping and aggregates. For Access SQL you are looking for things like `GROUP BY` and `COUNT`. Depending on your table structure you may also need a sub-query. — SunKnight0, Jul 25 '18 at 20:35

score 0 · Answer 1 · answered Jul 25 '18 at 20:39

It could be something like:

Select
    Employee,
    [Date],
    Count(*) As InteractionsPerDay
From
    (Select 
        Employee,
        [Date]
    From
        YourTable
    Group By
        Employee,
        Patient,
        [Date]) As T
Group By
    Employee,
    [Date]

Creating a query to count non duplicated patient interactions per day

1 Answers1