Results 1 to 6 of 6
  1. #1
    rmartel is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Feb 2016
    Posts
    3

    Unique Record Return

    My question is somewhat convoluted so I will try to be as clear and explicit as possible. What I am trying to do is create a query (or a series of queries) that will return records when the record only meets the criteria. What I mean is, say I have two user profiles, one that has three records, lets say for orders made, and the second has one record, again for orders made.

    Ex. Orders
    Users Item Color Price
    A Truck Red $10,000
    A Car Blue $5,000
    B Boat Green $25,000
    A Boat Blue $15,000
    C Car Yellow $10,000

    If I were to run a query like this:


    Select Users, Item
    From Orders
    Where Item = "Boat"

    I would return user A and B. I want to write it in such a way so that I only return user B, because their only record is the record that matches my search criteria. I'm looking for records/users that only match the criteria given. Is there any way to get Access to recognize when there are multiple records in addition to the record it returns and then exclude the result if that's the case?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You are wrong, A & B match your criteria. Not B only.
    queries always return your exact criteria, if you don't use wildcards.
    multiple records is correct.
    if you only want 1 random of the collection , use TOP 1.

  3. #3
    rmartel is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Feb 2016
    Posts
    3
    Thanks, but I'm not wrong. I know both A and B match my criteria. That's not the question I'm asking. I'm saying that A and B are different, A has two other records in addition to the one matching my criteria, B does not. How do I write my query in a way so that it only returns the scenarios like B, where the record that matches my criteria is the only record that exists?

    And Top 1 would only work if I wanted to see one return for each User regardless of how many records match the criteria.

  4. #4
    redpenner is offline Novice
    Windows 7 32bit Access 2010 (version 14.0)
    Join Date
    Aug 2010
    Posts
    23
    rmartel sounds like you are looking for users whose total number of records = 1, and for those users, any records that match the criteria. Is that it?

  5. #5
    rmartel is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Feb 2016
    Posts
    3
    Yes, that is what I mean. Though, for my real-world example, it's a bit more complex. I work for a school and I'm trying to match students who have taken one of two courses only. The student can have taken either of them (or both), but no other courses.

  6. #6
    CJ_London is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    suggest look at the query wizard for finding duplicates and adapt to your requirements

    not tested but something like

    Code:
    Select Users, Item
     From Orders
    Group By Users, Item
    Having Item = "Boat" AND Count(*)=1
    The duplicate would generate something similar but with Count(*)>1

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

Similar Threads

  1. Replies: 5
    Last Post: 09-22-2015, 12:18 PM
  2. Unique Record
    By TinaH in forum Access
    Replies: 11
    Last Post: 01-08-2015, 03:38 PM
  3. Replies: 3
    Last Post: 09-06-2014, 01:25 PM
  4. Next unique record ( Record Navigation )
    By ramindya in forum Access
    Replies: 5
    Last Post: 04-26-2012, 09:10 AM
  5. Creating unique record from record and field data
    By arthurpenske in forum Access
    Replies: 3
    Last Post: 08-24-2011, 06:11 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