1

I am working with my lab database and close to complete it. But i am stuck in a query and a few similar queries which all give back the similar results.

Here is the Query in design mode Query in design mode

and this is what it gives out

this is what it gives out

This query is counting the number of ID values in table PatientTestIDs whereas I want to count the number of unique PatientID values grouped by each department

I have even tried Unique Values and Unique Records properties but all the times it gives the same result.

Pham X. Bach
  • 5,284
  • 4
  • 28
  • 42
junaid bashir
  • 17
  • 3
  • 2
    Most people here want sample data and the expected result as formatted text, not as images (or links to images.) – jarlh Jan 03 '19 at 07:41

1 Answers1

1

What you want requires two queries.

Query1:

SELECT DISTINCT PatientID, DepartmentID FROM PatientTestIDs;

Query2:

SELECT Count(*) AS PatientsPerDept, DepartmentID FROM Query1 GROUP BY DepartmentID;

Nested all in one:

SELECT Count(*) AS PatientsPerDept, DepartmentID FROM (SELECT DISTINCT PatientID, DepartmentID FROM PatientTestIDs) AS Query1 GROUP BY DepartmentID;

You can include the Departments table in query 2 (or the nested version) to pull in descriptive fields but will have to include those additional fields in the GROUP BY.

June7
  • 19,874
  • 8
  • 24
  • 34
  • Thank you very much june7, exactly that was what i needed. I must've given you a bundle of thanks a long ago, but i got just a bit busy in other things and could not come here. This is very appreciating that you guys put your precious time for society. Thank you again from all the people being helped here – junaid bashir Jan 22 '19 at 07:24