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 offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,722
    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.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,030
    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 - Senior Forums