Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Comparison formula (EXCEL to ACCESS)

  1. #1
    New Member
    Join Date
    Mar 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Comparison formula (EXCEL to ACCESS)

    Good afternoon All

    In excel I used the following formula to compare to sets of numbers and count how many were common. How can I do this in Access?

    FORMULA (CTRL+SHIFT+ENTER):
    =SUM(IF(COUNTIF($J$2:$N$2;J6:N6);1))

    There are two sets (each set can have between 0 to 80 000 entries), one in each table. The result should be in a third table.

    Rgds
    António

  2. #2
    Board Regular
    Join Date
    Jul 2010
    Posts
    347
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Comparison formula (EXCEL to ACCESS)

    Normally you wouldn't have a third table but a query that returns the results:

    there are a few ways to do this, one way:

    Code:
    SELECT [MyNumber] FROM Table1 WHERE [MyNumber] In (Select [MyNumber] FROM Table2)
    Create a new query in design view, right click the tab of the query and select SQL view then post the post above (with field and table names changed to match your data) then select run from the ribbon.

    If you need them in a table you can change this to a make table or append query.

  3. #3
    Board Regular
    Join Date
    Jul 2010
    Posts
    347
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Comparison formula (EXCEL to ACCESS)

    Sorry, for clarity - that will give you all the common numbers not just the total, for just the total you wold need to change to an aggregate query:

    Code:
    SELECT Count([MyNumber]) AS Common FROM Table1 WHERE [MyNumber] In (Select [MyNumber] FROM Table2)
    Last edited by stumac; Mar 14th, 2019 at 01:11 PM.

  4. #4
    New Member
    Join Date
    Mar 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Comparison formula (EXCEL to ACCESS)

    Hello!

    I've tried this solution, but it requests to manually introduce the numbers. I would like it to go in a cycle and check every entry from table1. How can I make this happen?

    Kind regards

  5. #5
    Board Regular
    Join Date
    Jul 2010
    Posts
    347
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Comparison formula (EXCEL to ACCESS)

    Can you post the sql of your query (after you changed table and field names)?

    If you are getting a prompt 'Enter Paramater' it's probably down to spelling a field name wrong or not changing it.

  6. #6
    New Member
    Join Date
    Mar 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Comparison formula (EXCEL to ACCESS)

    Quote Originally Posted by stumac View Post
    Can you post the sql of your query (after you changed table and field names)?

    If you are getting a prompt 'Enter Paramater' it's probably down to spelling a field name wrong or not changing it.
    I wrote:
    Code:
    SELECT Count([MyNumber]) AS Common FROM vendas_1 WHERE [MyNumber] In (Select [MyNumber] FROM Conjunto1)

  7. #7
    Board Regular
    Join Date
    Jul 2010
    Posts
    347
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Comparison formula (EXCEL to ACCESS)

    This would rely on both tables having the field 'MyNumber', if you change 'MyNumber' to the actual field names in the relevant tables it should work.

  8. #8
    New Member
    Join Date
    Mar 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Comparison formula (EXCEL to ACCESS)

    Quote Originally Posted by stumac View Post
    This would rely on both tables having the field 'MyNumber', if you change 'MyNumber' to the actual field names in the relevant tables it should work.
    Unfortunally

    Unfortunately, unable to make it work. I'm brand new to Access, and the migration from Excel it's much more difficult than expected
    Will keep on trying

    Thank you
    António

  9. #9
    Board Regular
    Join Date
    Jul 2010
    Posts
    347
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Comparison formula (EXCEL to ACCESS)

    What is the field name in the table Vendas_1 and the field name in Conjunto1 that you would like to compare?



    Access and Excel are completely different and usually serve different purposes, for data sets the size you are talking about then Access will usually perform better if the data is properly stored and normalised. If you are decent on Excel then once you get used to working with Database's (i.e. tables & fields rather than sheets and cells) you will probably pick up a lot of it fairly easily!

  10. #10
    New Member
    Join Date
    Mar 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Comparison formula (EXCEL to ACCESS)

    The main difficulty is formatting my brain

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •