Share via

Please can someone help me with the following script

Anonymous
2025-04-10T11:25:39+00:00

DELETE FROM PICKING WHERE([Alternate User ID (Locus)], [Date Of Shift]) IN (SELECT[Alternate User ID (Locus)], [Date Of Shift]FROMPICKINGGROUP BY[Alternate User ID (Locus)], [Date Of Shift]HAVINGCOUNT(*) > 1)AND(SELECT COUNT(*)FROM PICKING AS P2WHERE P2.[Alternate User ID (Locus)] = PICKING.[Alternate User ID (Locus)]AND P2.[Date Of Shift] = PICKING.[Date Of Shift]) > 1;

Microsoft 365 and Office | Access | Other | Other

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2025-04-10T11:26:32+00:00

    Peter,

    What do you need help with exactly? Please explain in detail how we can assist you.

    0 comments
  2. George Hepworth 21,805 Reputation points Volunteer Moderator
    2025-04-10T13:00:52+00:00

    What happens when you open that query (and it's SQL in a query, not a "script")?

    An error is raised? If so, show us the error message itself.

    The wrong results are returned? If so what do you expect and what is returned?

    No results at all?

    Thank you.

    0 comments
  3. Anonymous
    2025-04-10T15:38:41+00:00

    You are referencing two columns in the WHERE clause, and returning the same two columns in the first subquery.  As you are applying the IN operator to the subquery, however, this expects a single column as both operands.  Instead of using the IN operator use the EXISTS predicate and correlate the subquery with the outer query:

    DELETE *

FROM PICKING AS P1

WHERE EXISTS

    (SELECT

         [Alternate User ID (Locus)], [Date Of Shift]

     FROM

         PICKING AS P2

     WHERE

         P2.[Alternate User ID (Locus)] =  P1.[Alternate User ID (Locus)]

         AND P2.[Date Of Shift] = P1.[Date Of Shift]

     GROUP BY

         [Alternate User ID (Locus)], [Date Of Shift]

     HAVING

         COUNT(*) > 1);
    0 comments
  4. Anonymous
    2025-04-10T16:05:34+00:00

    PS:  You do realise that what you are attempting will delete all rows which are duplicated on the two columns in question?  Is this what is intended? For examples of how to delete all bar one of duplicate rows see DeleteDemo.zip in my Dropbox public databases folder at:

    https://www.dropbox.com/scl/fo/0scigd3r48hx5xrev2jrf/AB0-GMdTgMAO5O1cGdr3QW0?rlkey=ib6bs6g9jqcrywwzivur3265t&dl=0

    This little demo file illustrates two queries which will delete all duplicates bar one, in one case using the primary key to determine which row to retain, in the other using the value of a non-key column.

    0 comments