Results 1 to 8 of 8
  1. #1
    chotroi is offline Novice
    Windows 8 Access 2007
    Join Date
    Mar 2014
    Posts
    4

    Compare if duplicate and display message

    Please help.



    I have an access table like below:
    ..........ColA | ColB
    row1: Haga | "Good"
    row2: Haga | "Dup"
    row3: Stanton

    I would like to have an expression in MS Access that compare row1 and row2 in ColA, if they are duplicate, then display "Good" in row1 of ColB and display "Dup" in row2 of ColB.

    Thanks for your help.

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    You could create an alias in a query object. Perhaps an instant if function.

    Something like this should work

    MyComparison: IIf(TableName.ColA = TableName.ColB, "Good", "Dup")

    This will create an alias, "MyComparison". When you view your query in DataSheet view you will see a new column named MyComparison. You can change the alias name to whatever you like.

  3. #3
    chotroi is offline Novice
    Windows 8 Access 2007
    Join Date
    Mar 2014
    Posts
    4
    ColB is a result, not to compare.

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    I see. I did not take enough time to understand clearly. Have you tried using a totals query? You might be able to create an alias in a totals query that would analyze the count.

    MyComparison: IIf(TableName.CountAlias >1, "Good", "Dup")

  5. #5
    chotroi is offline Novice
    Windows 8 Access 2007
    Join Date
    Mar 2014
    Posts
    4
    I am a newbie to Access. Can you please so me how?

    Thanks for your responses.

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Start by using the "Query Design" button to create a new query object. Add a table to your query, add a field to the grid, click the "Totals Query" button, and save your new query.

    Make adjustments to your query to get the results you need when viewing it in DataSheet view. Use the criteria and total fields in the grid towards the bottom of the window to make further adjustments.

  7. #7
    chotroi is offline Novice
    Windows 8 Access 2007
    Join Date
    Mar 2014
    Posts
    4
    I tried but not working. Can you please give more details? Thnaks

  8. #8
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Perhaps you can offer details to what you have tried and why you believe it is not working.

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

Similar Threads

  1. Replies: 4
    Last Post: 01-14-2016, 02:18 PM
  2. Duplicate Record Error Message!!
    By forumer in forum Forms
    Replies: 6
    Last Post: 03-14-2014, 07:25 AM
  3. Duplicate ID Message
    By Traviscon in forum Programming
    Replies: 1
    Last Post: 05-11-2012, 12:38 PM
  4. Replies: 1
    Last Post: 02-17-2012, 04:43 PM
  5. Replies: 3
    Last Post: 09-11-2011, 06:38 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