Comparing Data in Two Tables (1 Viewer)

[jeffreyccs]

As part of learning how to use Access better during shielding, I am rewriting some of my Excel solutions in Access and have come across something that was fairly straightforward in Excel that I cannot work out in Access. I have two tables that I wish to compare Table A has five numeric fields and Table B one numeric field and I wish to know how many matches each record has in table A against all records in table B, rteturning a value between 0 and 5. I had tried using a calculated field and dcount but could not get it to work, however I suspect that I should be looking at a query. I am sure it is possible and it is just my inexperience that is at fault. In Excel it is just a question of using CountIf in an array formula. If anyone culd point me in the right direction I would appreciate it.

Many thanks

Jeff

 

[arnelgp]

use query:

select tableA.* From tableA, tableB Where "|" & tableA.Field1 & "|" & tableA.Field2 & "|" & tableA.Field3 & "|" & tableA.Field4 & "|" & tableA.Field5 & "|" Like "*|" & tableB.Field1 & "|*"


or

select tableA.* From tableA, tableB Where Instr("|" & tableA.Field1 & "|" & tableA.Field2 & "|" & tableA.Field3 & "|" & tableA.Field4 & "|" & tableA.Field5 & "|", "|" & tableB.Field1 & "|") > 0


To make it a SARgable query:

SELECT tableA.* FROm tableA, tableB where (tableA.field1 = tableB.Field1 Or tableA.Field2 = tableB.Field1 Or tableA.Field3 = tableB.Field1 Or tableA.Field4 = tableB.Field1 Or tableA.Field5 = tableB.Field1)

 

[jeffreyccs]

Thanks I'll have a go and see how I get on

Regards

Jeff