0

I am super new to access and having difficulty with a query. My table (tblpacks) is the following:

 id user_id group_id quota_id
 1    1        1       1
 2    1        2       1
 3    2        1       1
 4    3        1       1

Ideally, what I now is to get hte number of unique users and groups for quota_id=1

The result will be:

       total_users = 3
       total_groups = 2
Nie Selam
  • 1,343
  • 2
  • 24
  • 56

1 Answers1

1

If you only wanted to count one field, there would by a simple solution, but since you want to count 2 separate fields, you in fact need at least 2 separate queries.

My answer is to use a UNION query as the source for counting. This UNION query returns the distinct user_id values (with Null as group_id) and the distinct group_id values (with Null as user_id). I omitted the DISTINCT keyword, because UNION (without ALL) does a DISTINCT query automatically. As the datatypes where not recognized correctly when using a constant Null field in the first SELECT statement of the UNION query, I added a third SELECT statement as the first one, which selects both fields from the table but returns no records:

SELECT Count(user_id) AS total_users, Count(group_id) AS total_groups
FROM (
  SELECT user_id, group_id FROM tblpacks WHERE Yes=No
  UNION
  SELECT user_id, Null FROM tblpacks WHERE quota_id=1
  UNION
  SELECT Null, group_id FROM tblpacks WHERE quota_id=1
) AS qsub;
Wolfgang Kais
  • 4,010
  • 2
  • 10
  • 17
  • Just rewrote it as null as group_id / null as client_id to avoid the first Yes=No query and it yields the same result and not bad speed wise too. Thanks Kais – Nie Selam Oct 21 '18 at 10:38
  • Wellcome, and good to know that counting works without the Yes=No query. I tested the UNION query alone and did not see the `group_id` values, thats why I added that auxiliary query. :-) – Wolfgang Kais Oct 21 '18 at 11:43