Comparison formula (EXCEL to ACCESS)

AntonioCrz

New Member
Joined
Mar 14, 2019
Messages
5
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))

<tbody>
</tbody>

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
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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 [[I]MyNumber[/I]] FROM [I]Table1 [/I]WHERE [[I]MyNumber[/I]] In (Select [[I]MyNumber[/I]] FROM [I]Table2[/I])

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.
 
Upvote 0
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([[I]MyNumber[/I]]) AS Common FROM [I]Table1 [/I]WHERE [[I]MyNumber[/I]] In (Select [MyNumber] FROM [I]Table2[/I])
 
Last edited:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,913
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top