How to find distinct strings in Access?

Question

Hi,

I have a column with the following values

abc 23
34 abc
23 abc 56

These are distinct values but i want only abc to be returned as it is there in all values. is this possible. moreover i dont want to give abs as input as there are numerous values like this.

thanks

regards tksy

Answer 1

This should be stored in a separate table, like this:

RowID | Index | value 
1     | 1     | abc
1     | 2     | 23
2     | 1     | 34
2     | 2     | abc
3     | 1     | 23
3     | 2     | abc
3     | 3     | 56

Get rid of the Index column if order does not matter. Either store it directly like this, or populate this table from your original column by splitting the string on whitespace and repeating inserts.

Once you have this format of storage, it is relatively easy to achieve what you want:

SELECT value, count(*) from keywords group by value;

to get a count of all the distinct flags, then join it with

SELECT count(*) from original_table;