Results 1 to 3 of 3
  1. #1
    darls15 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    8

    Query to include rows that don't exist in table

    Hi All

    I have a table with a list of domains and the groups and users that have accessed them. Not all groups/users have accessed every domain. I'm trying to create a query which will list each domain and include all 4 groups (Staff, Student, Generic, Other) - and 0 in the "User" count column where there was no access by users. For example...

    Table...



    DOMAIN GROUP USER
    greattrains.com Staff abcde1
    greattrains.com Student abcde2
    greattrains.com Staff abcde5
    greattrains.com Generic abcde3
    greattrains.com Other abcde4
    thinkific.com Staff abcde5
    thinkific.com Generic abcde6
    thinkific.com Other abcde7
    wiley.com Other abcde4
    wiley.com Staff abcde8
    wiley.com Other abcde9

    Expected query results...

    DOMAIN GROUP USER
    greattrains.com Staff 2
    greattrains.com Student 1
    greattrains.com Generic 1
    greattrains.com Other 1
    thinkific.com Staff 1
    thinkific.com Student 0
    thinkific.com Generic 1
    thinkific.com Other 1
    wiley.com Staff 1
    wiley.com Student 0
    wiley.com Generic 0
    wiley.com Other 2

    I have tried to find something in search but I'm having no luck.

    Can someone please help me with achieving this?

    Many thanks
    darls15

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Need a dataset of all possible domain/group pairs. Do you have a table of Domains and table of Groups?

    SELECT Domains.*, Groups.* FROM Domains, Groups;

    Join that query to data.

    SELECT Q1.Domain, Q1.Group, Count(DomainUsers.User) AS CntUser
    FROM DomainUsers RIGHT JOIN (SELECT Domains.*, Groups.* FROM Domains, Groups) AS Q1
    ON (DomainUsers.Domain = Q1.Domain) AND (DomainUsers.Group = Q1.Group)
    GROUP BY Q1.Domain, Q1.Group;
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    darls15 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    8
    Thanks June7 that worked a treat!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 02-22-2021, 07:40 AM
  2. Replies: 7
    Last Post: 05-25-2018, 05:56 PM
  3. Replies: 4
    Last Post: 07-10-2015, 07:51 AM
  4. Replies: 4
    Last Post: 01-24-2013, 12:11 AM
  5. Replies: 2
    Last Post: 09-18-2012, 06:20 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums