Originally Posted by
Ajax
that is a total count of 6, not 4
The formula compares each number of a row in Cover with all numbers of Data and returns the max matches in a single row. So, for the numbers of the first row of worksheet Cover, there are 4 matches in Data!b4:g4 (ID 3) and this is the returned value of the formula in Cover!H2.
In Access, if it’s possible, I’m afraid that it’s little a bit tricky and I don't know how efficient could be.
Edit:
Hmm... Maybe not.
I see that the formula looks up in the coresponding column for each number of each row, not in all columns.
So, at first, I created a query named qryComp:
Code:
SELECT C.ID,
-([C].[B1]=[D].[B1]) AS L1,
-([C].[B2]=[D].[B2]) AS L2,
-([C].[B3]=[D].[B3]) AS L3,
-([C].[B4]=[D].[B4]) AS L4,
-([C].[B5]=[D].[B5]) AS L5,
-([C].[B6]=[D].[B6]) AS L6
FROM Data AS D, Cover AS C;
Then, I created the qryMax:
Code:
SELECT qryComp.ID,
Max((L1+L2+L3+L4+L5+L6)) AS Matches
FROM qryComp
GROUP BY qryComp.ID;
Finaly, I created a simple query that join the table Cover with the qryMax:
Code:
SELECT
Cover.ID,
Cover.B1,
Cover.B2,
Cover.B3,
Cover.B4,
Cover.B5,
Cover.B6,
qryMax.Matches
FROM Cover INNER JOIN qryMax ON Cover.ID = qryMax.ID;
Done!