Count same digit numbers from each row

[Gulya]

Dear Access Forum Experts,

I have data contains numbers only and would like to count same numbers from another data from each rows using Access.

I have ready template with formula in Excel but it's working extremely slow with a huge data.



So appreciate if someone could provide me same as attached excel calculation in Microsoft Access please.

The attachment is for your perusal plz.

Thanks in advance

[Bob Fitz]

Can you explain what the formula in Excel does

[Gulya]

Can you explain what the formula in Excel does

I would like to count if the same number is available in other database (searching from each respective rows B:G).

For instance, the numbers in “Cover!B2:G2” is 50, 99, 102, 145, 208, 854 and the same numbers to be searched & counted from each respective rows of another data like "Database!B2:G2,Database!B3:G3 .............. Database!B1000000:G1000000" and providing the maximum counted result.

The result could be either 0, 1, 2, 3, 4, 5 or maximum 6

thanks

[Welshgasman]

Well for a start, those are not the values in B2:G2 on the Cover sheet?
In fact that sequence does not even exist?

In fact I only have 76 rows for data?, I do not have a number as low as 480 in B6 either?

[CJ_London]

perhaps just explain in detail how the 4 is arrived at on the first row of data

B1	B2	B3	B4	B5	B6	Count
27	51	59	68	162	862	4

If I change the value of B1 from 27 to 25, the count changes from 4 to 3 - why?

just to clarify - if I understand the formula correctly from your description

27 - appears once in Data B1
51 - appears once in Data B1
59 - appears once in Data B1 and again Data B3
68 - does not appear
162 - appears once in Data B5
862 - appears once in Data B6

that is a total count of 6, not 4

and if I change 27 to 25 (which also appears once in Data B1), the count calculation changes to 3 - yet the total count remains as 6

remember you are asking an access forum for advice. Databases work in a completely different way to excel. Many of us are experienced excel users, but perhaps not to your level

Edit #2:

think I've got it

27,51 etc appears in row 3 which has 4 matches (27,59,162,862)
25,51 etc appears in row 3 which has 3 matches (59,162,862)

So the answer to your question is to left join your cover table to your data table on all 6 fields

then have a simple calculation to count the number of matches.

Without knowing what this data is all about, I suspect it is not normalised - if it was (i.e. B1, etc would not be a column heading but a field value) the query would be simpler

aircode so may have some typos

SELECT C.B1, C.B2, C.B3, C.B4, C.B5, C.B6,max(6+isnull(D.B1)+isnull(D.B2)+isnull(D.B3)+isnull(D.B4)+isnull(D.B5)+isnull(D.B6)) AS Count
FROM Cover C LEFT JOIN Data D ON C.B1=D.B1 AND  C.B2=D.B2 AND  C.B3=D.B3 AND  C.B4=D.B4 AND  C.B5=D.B5 AND  C.B6=D.B6
GROUP BY  C.B1, C.B2, C.B3, C.B4, C.B5, C.B6

[accesstos]

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:

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:

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:

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!

[CJ_London]

The formula compares each number of a row in Cover with all numbers of Data and returns the max matches in a single row.

yes - finally figured it out and provided a solution

[accesstos]

Of course, the first two queries of my suggestion may combine in one:

SELECT C.ID, 
Max(-(([C].[B1]=[D].[B1])
+([C].[B2]=[D].[B2])
+([C].[B3]=[D].[B3])
+([C].[B4]=[D].[B4])
+([C].[B5]=[D].[B5])
+([C].[B6]=[D].[B6]))) AS Matches 
FROM Data AS D, Cover AS C 
GROUP BY C.ID;

[Gulya]

Of course, the first two queries of my suggestion may combine in one:

SELECT C.ID, 
Max(-(([C].[B1]=[D].[B1])
+([C].[B2]=[D].[B2])
+([C].[B3]=[D].[B3])
+([C].[B4]=[D].[B4])
+([C].[B5]=[D].[B5])
+([C].[B6]=[D].[B6]))) AS Matches 
FROM Data AS D, Cover AS C 
GROUP BY C.ID;

Dear Accesstos,

Thanks a lot for your support, would really appreciated if you could share the Access template which you have created.

I'm really struggling to apply the provided criteria/formulas.

thanks a lot

[accesstos]

Dear Accesstos,

Thanks a lot for your support, would really appreciated if you could share the Access template which you have created.

I'm really struggling to apply the provided criteria/formulas.

thanks a lot

Sure, I will. That's the easiest part of the solution.

The last provided query is the qryMCount of attachment.

You are welcome!

[Gulya]

Sure, I will. That's the easiest part of the solution.

The last provided query is the qryMCount of attachment.

You are welcome!

Thanks a lot Accesstos, working perfectly

[accesstos]

Thanks a lot Accesstos, working perfectly

I'm glad to hear that!

You are welcome and,
Thanks for the star!

P.S.:
Welshgasman, thanks for the star to you too!