Results 1 to 9 of 9
  1. #1
    phspec is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    4

    Select Rows based on Unique Values in One Column

    I'm trying to make a query that will return rows of data from a table based on whether the row of data is unique in one column, Subject Line, and not a duplicate of another piece of data in that column.
    ID (Primary Key) Subject Line Attachment
    1 Subject 1 Att1
    2 Subject 2 Att1
    3 Subject 3 Att2
    4 Subject 1 Att2
    So my query would ideally return the rows with ID 1,2 & 3.

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    So my query would ideally return the rows with ID 1,2 & 3.
    I don't follow your example. Which columns are you looking at exactly when trying to determine whether or not the rows are unique?

    Why would ID 4 be dropped? It appears that all 4 Subject Line values are unique.

    If only looking at the Attachment field, both 2 and 4 would be dropped.

  3. #3
    phspec is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    4
    I'm sorry, my table had a typo. Rows 1 and 4 now have the same value in the subject column

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    So, you only want to consider the Subject Line in determining duplicates?
    What then is the logic for which attachment value to return in that scenario?

  5. #5
    phspec is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    4
    Yes, I only want to consider the Subject Line in determine duplicates. I would just like the attachment value and the whole row associates with the Subject Line to be returned, so the return value would be:

    ID (Primary Key) Subject Line Attachment
    1 Subject 1 Att1
    2 Subject 2 Att1
    3 Subject 3 Att2

  6. #6
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    OK, but you didn't answer my question for the logic to use in which record to select. In order to do what you want, we need to know what the logic or "rules" are.

    Let's look at your example. There are two records with a Subject Line of "Subject 1". One has an attachment value of "Att1" and the other has an attachment value of
    "Att2". How do you decide which one of these two Attachment values is the one you want to return?

  7. #7
    phspec is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    4
    I suppose which ever one occurs first and is processed first in the query search. Sorry for the confusion and miscommunication.

  8. #8
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I suppose which ever one occurs first and is processed first in the query search.
    That is what we are trying to define. Queries can be sorted in any order you want.

    Someone once made a great metaphor when it comes to how Access stores data. It really isn't ordered in the table/database (people who are used to using Excel sometimes have a tough time envisioning this). Think of it as a big bag of marbles, all mixed up with no real order at all.

    We need to define how we want to sort/order it. So what field do we want to sort it by, so that when we say select the first record it finds, that means something (because we have defined the fields to sort by)?

    Or maybe you don't really care which value you return in the Attachment field? If so, we can just tell it to pick the first one that it finds, but that may not also match up to what you think is the first one it finds (if there is a Primary Key, it will probably default sort by that).

  9. #9
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    If you don't care about which Field IDs or Attachment records are returned, simply go to your query, and add all three fields.
    Then click on the Totals Icon, which will add a Totals row under each field with the phrase "Group By" under each one.
    Change "Group By" to "First" under the ID and Attachment fields. This should return what you are looking for.

    If you are particular about which IDs or Attachments should be returned (as I was mentioning previously), we probably will need to use a Nested Query, Subquery, or Two Queries. Just let us know the logic for determination which values to be returned and we can help you with that part.

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

Similar Threads

  1. Replies: 4
    Last Post: 12-29-2015, 03:25 PM
  2. Select unique values from multiple fields
    By MrDummy in forum Queries
    Replies: 2
    Last Post: 12-07-2015, 01:42 PM
  3. Replies: 3
    Last Post: 09-06-2014, 01:25 PM
  4. Replies: 7
    Last Post: 02-20-2014, 02:34 PM
  5. Replies: 2
    Last Post: 08-28-2011, 06:06 AM

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