Question Building Cluster Functionality in Access from Tables

[npatil]

Hello,

I need to filter some data from a huge table having many fields records. Since I am using it for an engineering application, I need to build categories or clusters of tyres which have similar properties over a wide range of Fields, but differ only in one or more..

For example, I have a table which is represented as follows:

Field 1 Field2 Field3 Field4................................Field20
A B C F ................................ X
A B C F .............................. Y
A B C F .................................. Z
A G C F ................................ X
A G C F ................................... Y
. . . . ...................................
. . . . ...................................
. . . . ..................................
A B C D ................................ X
A B C D .............................. Y
A B C D .................................. Z
A G C E ................................ P
A G C E ................................... Q
. . . . ................................. .
. . . . .................................. .


Now, I am using the above table to depict what my table would look like with numerical and/or alpha-numerical value.

As you can see, there are 4 clusters here present:
1. A B C F....(X Y Z)
2. A G C F...(X Y)
3. A B C D...(X Y Z)
4. A G C E ...(P Q)

So we can see that there are groups of data which vary only in 1 Field( for simplicity) and are equal in other fields.

Thus, I would like to build a query which can identify and represent such clusters. Furthermore, I would like each of the clusters to be associated with 1 important field, which would be used to identify it. With help of this Field, we should be able to compare and view its individual details.

Any help on this would be greatly appreciated!
Best,

Nikhil

 

[DCrake]

You would need to group by each field that could possibly be a part of a cluster then as an extra column add Cnt:1 and count this column. If this count is greater than 1 then there are at least 2 rows that share the same data.

A-B-C-D-E-F Cnt:1
A-B-C-D-E-F Cnt:1
A-B-C-D-E-F Cnt:1

Returns
A-B-C-D-E-F Cnt:3

Under the Cnt column enter >1 in the condition row and save the query as QryClusteredData

 

[npatil]

Well, thanks for the suggestion. But I think I would have to manually add Cnt:" " for every possible cluster which can be formed. But since I have a huge database, this would not be feasible,that is if I understand your answer correctly.

Do you think it is possibleto write a VBA code which would find groups of data by comparing different records and their fields, using some smart conditilons ?

Thanks,

Nikhil

 

[DCrake]

Sen me a couple of 100 lines to play with in a table and provide some resuls that you would expect to find.

 

[npatil]

Sorry but I did not get you. Are you looking for the VBA code from my side?