Results 1 to 3 of 3
  1. #1
    arturomq is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    Nov 2018
    Posts
    1

    Filter out group based on variables in a column

    Hi,
    I have a query that is pulling data from two different tables. The possible values on the Events column are: A, B, C, D, E. I need to create a report that is grouped by User ID and listing its events. How can I removed an entire group (User ID and its events) if the group has A or B ?



    User ID ..... Events
    1 ................ B
    1 ................ B
    1 ................ C
    1 ................ E
    1 ................ A
    1 ................ D

    2 ................ C
    2 ................ E
    2 ................ E
    2 ................ C
    2 ................ D
    2 ................ D


    Thanks in advanced!

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    How can I removed an entire group
    No idea what that means. You don't remove records from a grouping, otherwise there'd be nothing to group on. Maybe you are getting ahead of yourself. You create a query that returns the needed fields/records. Then create a report. Then do sorting and grouping in the report design.

    If you have 2 or more tables you'd need to join them in query design. If you can't, you will probably have to create a UNION query to base the report on. Unless I'm just testing something, I'll always start with a working query before creating a form or report otherwise the foundation is no good.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You want to exclude all records for a user if they have event A or B in any record? Filtering records based on value of another record is tricky, something like:

    SELECT * FROM table WHERE UserID NOT IN (SELECT UserID FROM table WHERE Event = "A" OR Event = "B");
    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.

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

Similar Threads

  1. Replies: 3
    Last Post: 09-27-2018, 11:54 AM
  2. Replies: 10
    Last Post: 03-16-2018, 08:26 AM
  3. Query based on three variables
    By JST2017 in forum Queries
    Replies: 4
    Last Post: 01-25-2017, 08:47 AM
  4. Replies: 3
    Last Post: 04-28-2015, 02:16 AM
  5. Replies: 2
    Last Post: 12-05-2011, 04:53 AM

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