Results 1 to 6 of 6
  1. #1
    MsAxes is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Posts
    100

    How to identify columns with duplicate values in one column with matching records in a second column

    Hi everyone



    I have a table that has duplicate dollar values (that is expected and okay). Each dollar amount should have an 'id flag' with different values, if they don't then I need to review those. I am looking to create a column that flags the same dollar amounts that do not have a different value in the 'id flag' field.


    ex. the dollar amount 258,963.00 appears twice but both 'id flag' have the same value so I need to flag it as 'no dup. value'.

    The biggest issue is examples like 500,000 appears 8 times but four of them do not have a 'id flag' with a different value. I'm not sure if these are even possible to flag.

    thoughts?
    Attached Files Attached Files

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    So which record(s) in that table should a query return? Based on what you posted, I'd say none. Expected/desired results are usually a good thing to include in your post(s).

    EDIT - Or maybe it's 321,456.00, in which case try
    Code:
    SELECT Sheet1.amounts, Count(Sheet1.[id flag]) AS [CountOfid flag]
    FROM Sheet1
    GROUP BY Sheet1.amounts
    HAVING (((Count(Sheet1.[id flag]))=1));
    If your table has no id field, I don't think the results will prove to be too useful.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    MsAxes is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Posts
    100
    Everything in my table should be returned - only difference is that I need a new field which tells the user which duplicate dollar values do not have a duplicate ID flag

  4. #4
    xps35's Avatar
    xps35 is online now Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    229
    Something like this?
    Code:
    SELECT Q1.amounts, IIF(Count(Q1.[id flag])=1,"no dup. value", "dup. value") AS [Dups?] 
    FROM (SELECT DISTINCT amounts, [id flag] FROM Sheet1) Q1
    GROUP BY Q1.amounts;

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    which duplicate dollar values do not have a duplicate ID flag
    These amounts are duplicated and have 2 or more of the same id flag value. The other records are single amounts (no dupes) so they don't factor into it at all. Thus there seems to be no records that fit the requirement.
    amounts CountOfamounts id flag
    258,963.00 2 reviewed
    500,000.00 6 monitor
    500,000.00 2 reviewed
    541,212.00 2 monitor

    So I don't see which records fit the stated requirement and I'm not going to continue to ask for info that shows the expected results. Hope you get a solution.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    MsAxes is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2021
    Posts
    100
    Yes! Thank you xps35.

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

Similar Threads

  1. Replies: 2
    Last Post: 06-26-2020, 05:02 AM
  2. Replies: 4
    Last Post: 11-29-2017, 10:01 AM
  3. Replies: 14
    Last Post: 07-13-2015, 12:47 PM
  4. Replies: 3
    Last Post: 03-02-2015, 09:50 AM
  5. Replies: 1
    Last Post: 11-12-2013, 02:19 AM

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