Results 1 to 5 of 5
  1. #1
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    101

    I need a query to remove all rows with the same ID based on whether a single column has info in it


    Hi, very specific question and probably an even more confusing title. I've attached an excel doc to represent an example query. Basically, if there's any information in any in the ACTION columns, I need the query to filter out all entries regarding that customer. So for instance, since customer 1 and 3 have details in one of the action cells, all rows for 1 and 3 need to be removed by the query, which leaves only customer 2 in the query. It's not as simple as just putting "null" in the criteria of the action column, cause that only removes the entries with info. Any ideas?
    Attached Files Attached Files

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    2,319
    Maybe try:
    "SELECT tblCustomer.* FROM tblCustomer WHERE CustomerID IN (Select tblCustomer.CustomerID FROM tblCustomer Group By [CustomerID] HAVING Max([Action]) Is Null);"
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,958
    you need two queries

    the first to identify the customers who have details

    the second joins your table to this query with a left join and a criteria to only include records from the table where the customer is null


    qry1

    Code:
    SELECT DISTINCT ID 
    FROM myTable 
    WHERE action is not null
    qry2

    Code:
    SELECT *
    FROM myTable LEFT JOIN qry1 ON myTable.ID=qry1.ID
    WHERE qry1.ID is Null

  4. #4
    templeowls is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    101
    Quote Originally Posted by Ajax View Post
    you need two queries

    the first to identify the customers who have details

    the second joins your table to this query with a left join and a criteria to only include records from the table where the customer is null


    qry1

    Code:
    SELECT DISTINCT ID 
    FROM myTable 
    WHERE action is not null
    qry2

    Code:
    SELECT *
    FROM myTable LEFT JOIN qry1 ON myTable.ID=qry1.ID
    WHERE qry1.ID is Null
    THANK YOU!!! This worked perfectly. I greatly greatly appreciate it

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    2,319
    Great to hear you solved, just curious if you tried my suggestion which was
    shorter?

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Replies: 1
    Last Post: 05-14-2015, 06:38 PM
  2. Replies: 4
    Last Post: 03-10-2015, 08:26 AM
  3. Replies: 4
    Last Post: 04-22-2014, 08:23 AM
  4. Replies: 1
    Last Post: 05-26-2013, 09:45 PM
  5. Replies: 8
    Last Post: 02-28-2013, 02:21 PM

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