none
Return only 1 record from each group of recs that have a duplicate value in one field

    Question

  • SELECT tblCellSearches.SearchID, tblCellSearches.LockID, tblCellSearches.InmateID,  tblCellSearches.LstName, tblCellSearches.StaffName, tblLockAllocations.Lock
    FROM tblCellSearches LEFT JOIN tblLockAllocations ON tblCellSearches.LockID = tblLockAllocations.LockID
    ORDER BY tblCellSearches.LockID;

    the above query returns the following:

    79  1 123456  Hall Smith 1-101
    81  2 654321  Roberts Jones 1-102
    80  3 234567  Thomas Smith 1-103
    46  4 765432  William Smith 1-104
    55  4 456789  Taylor Brown 1-104
    82  5 897654  Carmon Johnson 1-105

    Notice there are two records with LockID 4 (there could be more). I want the query to return only one of them; it doesn't matter which one. I tried using DISTINCT but it doesn't work with so many fields. How can I do it?

    Many thanks,

    Rip

    Thursday, February 28, 2013 1:53 PM

Answers

  • As you are restricting the outer query on the primary key SearchID you should only need to apply the restriction on the search date to the subquery.  If we take out the unnecessary garbage which Access throws in when query design view is used, and lay out the SQL statement so we can see what's going on, we get:

    PARAMETERS [Forms]![frmCellSearches]![cboMonthNumber] SHORT,
    [Forms]![frmCellSearches].[cboYearNumber] SHORT;
    SELECT CS1.SearchID, CS1.LockID, CS1.InmateID, CS1.LstName, CS1.StaffName, tblLockAllocations.Lock, CS1.SearchDateTime
    FROM tblCellSearches  AS CS1 LEFT JOIN tblLockAllocations
    ON CS1.LockID = tblLockAllocations.LockID
    WHERE CS1.SearchID =
        (SELECT TOP 1 SearchID
          FROM tblCellSearches AS CS2
          WHERE CS2.LockID = CS1.LockID
          AND MONTH(CS2.[SearchDateTime]) = [Forms]![frmCellSearches]![cboMonthNumber]
          AND YEAR(CS2.[SearchDateTime]) = [Forms]![frmCellSearches].[cboYearNumber]
          ORDER BY CS2.SearchID)
    ORDER BY CS1.LockID;

    The subquery is correlated with the outer query on the LockID column, with the two instances of the tblCellSearches table being differentiated by the aliases CS1 and CS2, so in effect the subquery runs independently for each row returned by the outer query. By using the TOP 1 option it will return the first row in the SearchID sort order where the LockID value is the same as that returned by the current row of the outer query, and the year and month of the SearchDateTime column's value match the values of the two parameters respectively.  The outer query is then restricted by the value returned by the subquery to the first SearchID per LockID within the month/year in question.

    Note that this is predicated on SearchID being the primary key of tblCellSearches or otherwise uniquely indexed, so that its values must be distinct within the table.

    Ken Sheridan, Stafford, England

    • Marked as answer by ripperT Tuesday, March 05, 2013 12:57 PM
    Saturday, March 02, 2013 7:01 PM

All replies

  • Did you try it like this?

    SELECT DISTINCTROW tblCellSearches.LockID tblCellSearches.SearchID, tblCellSearches.InmateID,  tblCellSearches.LstName, tblCellSearches.StaffName, tblLockAllocations.Lock
    FROM tblCellSearches LEFT JOIN tblLockAllocations ON tblCellSearches.LockID = tblLockAllocations.LockID
    ORDER BY tblCellSearches.LockID;


    Chris Ward

    Thursday, February 28, 2013 3:22 PM
  • Yes. As written it does not eliminate the records with duplicate LockID values. An INNER JOIN returns the same result set. A RIGHT JOIN returns all the recs from tblLockAllocations along with the matching tblCellSearches records including those with duplicate LockID values.

    R

    Thursday, February 28, 2013 4:05 PM
  • Try this --

    SELECT tblCellSearches.SearchID, tblCellSearches.LockID, tblCellSearches.InmateID,  tblCellSearches.LstName, tblCellSearches.StaffName, tblLockAllocations.Lock

    FROM tblCellSearches LEFT JOIN tblLockAllocations ON tblCellSearches.LockID = tblLockAllocations.LockID

    ORDER BY tblCellSearches.LockID

    WHERE  tblCellSearches.SearchID = (SELECT TOP1 [XX].SearchID FROM tblCellSearches AS [XX] WHERE [XX].LockID = tblCellSearches.LockID ORDER BY [XX].SearchID);


    • Edited by KARL DEWEY Thursday, February 28, 2013 5:37 PM
    • Marked as answer by ripperT Saturday, March 02, 2013 1:44 PM
    • Unmarked as answer by ripperT Saturday, March 02, 2013 6:03 PM
    Thursday, February 28, 2013 5:36 PM
  • This worked. I had to move the main query's ORDER BY clause to the end after the subquery in order to avoid a syntax error message.

    Thanks to you both!

    Rip

    Saturday, March 02, 2013 1:43 PM
  • I guess I spoke too soon. This query works on the table as a whole, but I am now trying to parameterize the query based on another (date) field tblCellSearches.SearchDateTime so it will return records for a specified month/year:

    SELECT tblCellSearches.SearchID, tblCellSearches.LockID, tblCellSearches.InmateID, tblCellSearches.LstName, tblCellSearches.StaffName, tblLockAllocations.Lock, tblCellSearches.SearchDateTime
    FROM tblCellSearches LEFT JOIN tblLockAllocations ON tblCellSearches.LockID = tblLockAllocations.LockID
    WHERE (((tblCellSearches.SearchID)=(SELECT TOP 1 T.SearchID FROM tblCellSearches AS T WHERE T.LockID = tblCellSearches.LockID ORDER BY T.SearchID)) AND ((Month([SearchDateTime]))=[Forms]![frmCellSearches]![cboMonthNumber]) AND ((Year([SearchDateTime]))=[Forms]![frmCellSearches].[cboYearNumber]))
    ORDER BY tblCellSearches.LockID;

    The problem now is that if a duplicate lock ID exists anywhere in the table (regardless of its SearchDateTime value), it won't show up at all in the result for the given month/year. I need to understand this Karl so if you could explain exactly what the query, or at least the subquery, is doing, it would start me off in the right direction.

    Many thanks.

    S

    Saturday, March 02, 2013 4:17 PM
  • I think you also need to use the date criteria inside the subquery.

    (Month([SearchDateTime]))=[Forms]![frmCellSearches]![cboMonthNumber]) AND ((Year([SearchDateTime]))=[Forms]![frmCellSearches].[cboYearNumber])

    Saturday, March 02, 2013 5:28 PM
  • With the date criteria added it won't run at all and I get the error message "At most one record can be returned by this subquery."
    Saturday, March 02, 2013 6:01 PM
  • As you are restricting the outer query on the primary key SearchID you should only need to apply the restriction on the search date to the subquery.  If we take out the unnecessary garbage which Access throws in when query design view is used, and lay out the SQL statement so we can see what's going on, we get:

    PARAMETERS [Forms]![frmCellSearches]![cboMonthNumber] SHORT,
    [Forms]![frmCellSearches].[cboYearNumber] SHORT;
    SELECT CS1.SearchID, CS1.LockID, CS1.InmateID, CS1.LstName, CS1.StaffName, tblLockAllocations.Lock, CS1.SearchDateTime
    FROM tblCellSearches  AS CS1 LEFT JOIN tblLockAllocations
    ON CS1.LockID = tblLockAllocations.LockID
    WHERE CS1.SearchID =
        (SELECT TOP 1 SearchID
          FROM tblCellSearches AS CS2
          WHERE CS2.LockID = CS1.LockID
          AND MONTH(CS2.[SearchDateTime]) = [Forms]![frmCellSearches]![cboMonthNumber]
          AND YEAR(CS2.[SearchDateTime]) = [Forms]![frmCellSearches].[cboYearNumber]
          ORDER BY CS2.SearchID)
    ORDER BY CS1.LockID;

    The subquery is correlated with the outer query on the LockID column, with the two instances of the tblCellSearches table being differentiated by the aliases CS1 and CS2, so in effect the subquery runs independently for each row returned by the outer query. By using the TOP 1 option it will return the first row in the SearchID sort order where the LockID value is the same as that returned by the current row of the outer query, and the year and month of the SearchDateTime column's value match the values of the two parameters respectively.  The outer query is then restricted by the value returned by the subquery to the first SearchID per LockID within the month/year in question.

    Note that this is predicated on SearchID being the primary key of tblCellSearches or otherwise uniquely indexed, so that its values must be distinct within the table.

    Ken Sheridan, Stafford, England

    • Marked as answer by ripperT Tuesday, March 05, 2013 12:57 PM
    Saturday, March 02, 2013 7:01 PM
  • This query generates what I was looking for. Many thanks for the explanation as well Ken!

    R

    Tuesday, March 05, 2013 12:57 PM