Comparison formula (EXCEL to ACCESS)

[AntonioCrz]

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):
[TABLE="width: 24"]
<tbody>[TR]
[TD="class: xl66, width: 24"]=SUM(IF(COUNTIF($J$2:$N$2;J6:N6);1))[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[stumac]

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:

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.

 

[stumac]

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:

SELECT Count([[I]MyNumber[/I]]) AS Common FROM [I]Table1 [/I]WHERE [[I]MyNumber[/I]] In (Select [MyNumber] FROM [I]Table2[/I])

 

[AntonioCrz]

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

 

[stumac]

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.

 

[AntonioCrz]

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:

SELECT Count([MyNumber]) AS Common FROM vendas_1 WHERE [MyNumber] In (Select [MyNumber] FROM Conjunto1)

 

[stumac]

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.

 

[AntonioCrz]

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

 

[stumac]

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!

 

[AntonioCrz]

The main difficulty is formatting my brain