Results 1 to 3 of 3
  1. #1
    spreestr is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Oct 2017
    Posts
    2

    Making an Update Query to Mimic an Excel IF Formula that Compares Two Columns

    Hello,

    I have an Excel formula that I haven't been able to mimic in Access as a query and I'm not sure how to even start.

    The Excel Formula: =IF(COUNTIF($A$2:$A$10,$A2)<>COUNTIFS($A$2:$A$10,$ A2,$B$2:$B$10,B2),"Yes","")

    This formula is used in the following Context:

    SBSB_ID
    PCP In?
    Formula Compare
    903490878
    N/A
    903490494
    Yes
    903490502
    Yes Yes
    903490521
    N/A
    903490509
    N/A
    903490509
    N/A
    905409331
    N/A
    903490502
    No Yes
    905277856
    N/A

    This formula returns a "Yes" if any of the same IDs have different "PCP In?" values.


    Given the same table in Access, is there an update query that can be used to mimic this formula's return?



    Any help is greatly appreciated!

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you make a query, that counts your column.
    select count(A), Count(B) from table

    the make a query on the query above that pulls only data you want where COUNTA=COUNTB

  3. #3
    spreestr is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Oct 2017
    Posts
    2
    I'm confused as to what you are suggesting. In your first part the select query is returning the total number of rows in the table for both columns.

    What do you mean make a query on the query? Do you mean making a new table and having a query run on that? Also, how would pulling only data where COUNTA=COUNTB update my original table with "Yes" where the IDs have differing values in the second column?

    Apologies if I'm not grasping something simple, I've never tried to do something like this before.

    Thank you!

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

Similar Threads

  1. Replies: 2
    Last Post: 10-27-2016, 06:40 AM
  2. Replies: 19
    Last Post: 07-13-2015, 10:15 AM
  3. Replies: 2
    Last Post: 02-13-2015, 12:34 AM
  4. Macro to mimic Import & Link Excel button on external data
    By arnstrb in forum Import/Export Data
    Replies: 1
    Last Post: 03-16-2014, 07:52 PM
  5. Query where values are compares
    By marcvanderpeet12 in forum Queries
    Replies: 1
    Last Post: 07-15-2013, 08:46 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