Results 1 to 5 of 5
  1. #1
    Hursan is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Posts
    19

    Query to prevent duplicates with criteria

    I need help with a query.




    My query generates a list of the opened orders that shows the tech where to find the item/serial number requested by the client, the thing is that 1 serial number can be found in multiple locations.


    In the table below, Serial number ABC12378944 is located in 4 locations, the serial number from Rack MAIN-1-3-5 Loc # in the Dept MAIN was not collected and is marked as No, but the tech found the serial number in Rack Main-1-2-1 in the Dept MAIN and marked it as collected “Yes” - Third image


    My query will stop showing the entries marked as Yes or No. but will continue to show the rest of the entries, as you can see on the screenshot below which now shows only 2 entries. I want the query to stop showing the serial number entries from the same account if one of the serial numbers from the same account is marked as Yes.

    The first image is the Query, second is the query result.




    Thanks in advance.
    Attached Thumbnails Attached Thumbnails Query1.png   QueryResult.png   Table.png  

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    3,337
    Create a separate query where you group by serial and choose Min([Collected]) and add Is Null in the criteria row; this would give you the list of serials you want. Now simply add it to your original query and join it by serial. You can also use a subquery to do that but the separate query is easier to grasp.
    Code:
    SELECT Picklist.Serial, Min(Picklist.Collected) AS MinOfCollectedFROM Picklist
    GROUP BY Picklist.Serial
    HAVING (((Min(Picklist.Collected)) Is Null));
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    NoellaG's Avatar
    NoellaG is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    901
    To create picking lists we use temp tables.

  4. #4
    Hursan is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Posts
    19
    Thank you so much, your code works great.

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    3,337
    I assume you tried my code\SQL so happy to hear you're making progress!

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

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

Similar Threads

  1. Replies: 4
    Last Post: 04-12-2018, 03:26 AM
  2. Prevent Duplicates
    By Koolaid in forum Access
    Replies: 4
    Last Post: 03-31-2016, 09:37 AM
  3. Replies: 3
    Last Post: 09-11-2015, 03:22 PM
  4. Dlookup to prevent duplicates
    By arothacker in forum Access
    Replies: 16
    Last Post: 02-12-2014, 11:40 AM
  5. How to prevent duplicates in said example
    By raymondbeckham in forum Forms
    Replies: 1
    Last Post: 04-11-2012, 03:29 PM

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