Matching content to other content

[radek225]

Comparing content to other content

I have to know, how many elements matching to my primary elements from any records of my query and count match, if some element doesn't match then I need to add it to my primary elements, then at the end (rst.eof) count how many primary elements I have.

I read about using array and found on the Internet code which is helpful when using only one element to compare, but each of my records has many:/


E.G

id colours
1 blue;red
2 purple;blue;green
3 red;violet;purple;blue

dim matching_elements as long
dim primary_elements as string
dim TheNumberOfPrimaryElements as long

First of all, if I open recordset primary_elements is empty so I need to assign a value form first record.

primary_elements = rst!colours (so primary_elements = blue;red)

Now I can start comparing my primary_elements with second record:
matching_elements= 1
primary_elements = blue;red;purple;green

comparing my primary_elements with third record:
matching_elements= 3
primary_elements = blue;red;purple;green;violet

It's my last record so I need to count primary_elements
TheNumberOfPrimaryElements = 5

I need "matching_elements" to count other function in my aplication.
I stuck in this moment with my aplication so I hope that someone help me:/

 

[CJ_London]

It is not at all clear what you are describing or what you want to do

suggest you provide details of your table design plus relationships.

for example, is rst!colours a multivalue field or a string?

And how are you determining that TheNumberOfPrimaryElements = 5 as it is not clear to me

 

[namliam]

He wants to know the colours, 5 colours Red, Blue, Green, Purple, Violet

Assuming there is another table that has these listed, wouldnt it be as easy as to construct an "IN CLAUSE" for a select on that table?
Should return the number of rows selected even if you have selected red 10 times, should only return 1 row

 

[TimW]

Hi
I am not sure about storing 3 colours in one field... Or about noting the number of changes as looping through the records. ( If a new record was added then perhaps I would be interested in a new element

However this is how I would go about solving this problem.
Each value in your colour field needs to be split into an array with the said number of elements using your delimiter ;

A variable array needs to be set up to hold compared values.
You need to loop through your variable array and your 'colour' arrays and compare the values. (A loop within a loop) If the colour is found then you add this to matching_elements amount. If the colour is not found then it needs adding to your variable array.

When you have done looping you need to count the number of elements in your variable array

I hope this helps

T

 

[radek225]

It is not at all clear what you are describing or what you want to do

suggest you provide details of your table design plus relationships.

for example, is rst!colours a multivalue field or a string?

And how are you determining that TheNumberOfPrimaryElements = 5 as it is not clear to me

The source is one table with 2 columns:
1) id
2) colours

rst!colours is a text field.
TheNumberOfPrimaryElements - when I sum all of colours from primary elements then we have 5 (blue+red+purple+green+violet = 5)

...Assuming there is another table that has these listed, wouldnt it be as easy as to construct an "IN CLAUSE" for a select on that table?
Should return the number of rows selected even if you have selected red 10 times, should only return 1 row

I need to storing all of my colours in one field (it's usually no more than 5). Because In a subform I have another related information (there is only a part of my aplication). View of information will be to complicated when I do relations one to many

However this is how I would go about solving this problem.
Each value in your colour field needs to be split into an array..

I never use an array so that's the problem:/ Could you show me how to solve this example (with code)? Then I will know how to use array to solve my problem...

 

[jdraw]

radek,

I don't think readers understand what you are trying to do --I know I don't.
Here's what I'm seeing at the moment:

-you have 5 (primary colors) Red, Blue, Green, Purple, Violet
-you have a number of records that contain color values

-And you want to Count something. What is the something???

Your colors do not have to all be in 1 field.

 

[TimW]

Hi Again Radek
I think you need to go back to first principles. in my field we have the 7 Whys?
So ask yourself : why do I have to have more than one colour in a field? Why do I have to know the number of matching fields?
Why cannot this be achieved by using a related table?
I don't think anyone fully understands what you need. You need to lookup why its not a good idea to have more than one value in a field. How is this data entered and why this way?
You will need to do some research on Strings, and Arrays in VBA.
I'm sorry but I don't have time to write your code for you but I am sure there are samples out there. I will have a look at some of my code that will loop through loops & arrays.
If you look through my posts recently I have posted a loop through a string and used a compare function.. this may be of help
But once again, I would not put more than one value in a field out of choice....
Sorry I could not be more positive. good luck

 

[CJ_London]

I think I am getting a feel for what is required

in your example

id colours
1 blue;red
2 purple;blue;green
3 red;violet;purple;blue

You want to concatenate records 1 and 2 to get
blue;red & purple;blue;green
but you already have a blue so you get
blue;red;purple;green

then you concatenate with record 3
blue;red;purple;green & red;violet;purple;blue


but you already have red, purple and blue so you get
blue;red;purple;green;violet

If this is the case then as Tim says - why is your data like this? Nobody wants to spend a lot of time coming up with a very complex solution to a poor table structure. Does it have to be this way or can the data be stored in a way that makes it easier to provide an answer? i.e. one colour per record.

If it has to be like this then additional information is required:

• is the maximum number of colours in any one record 4 or can it be more? If more, what is the maximum?
• how many colours are there - or is this exercise intended to discover that?
• are all the colour spellings consistent and do not 'overlap' e.g. red;dark red;?
• is there always a ; between colours?
• is the data spelling consistent? e.g. Blue;Blu or with spaces Blue; Red
• is this 'nonsense example' data which you want to apply to a real life situation e.g. to discover number of words in a document - if so, better to explain the real life situation