Count Function/Concatenation

[map816]

If I have fields with data that has been concatenated is there a way to run a count function in a query to tell me how many times an option is found in the database?

Say 5 rows of data are:

Field 1 - a, b, c
Field 2 - d, e, f
Field 3 - a, b, c
Field 4 - d, e, f
Field 5 - d, j, k

Is there a way to tell me a count for every letter in this data since each field is concatenated? Would ideally like to get something like this:

a - 2
b - 2
c - 2
d - 3
e - 2
f - 2
j - 1
k - 1

I am able to get counts with no problems if the data isn't concatenated but am not getting any counts unless the concatenation is exactly the same options selected for each field. Any help? Thanks.

 

[Dairy Farmer]

2007+ allows multi select with Lookup field

 

[map816]

Attached are sample data sets like I mentioned in my post. Table 1 has one text option in each field and when I create query 1 it gives me the correct count for each option but in query 2 the text is concatenated and what I want to do is get a count for how often text options shows up across all records. The count option only counts like records so I don't get an accurate count. In query 2 I am hoping to get data to look like this:

a - 3
b - 3
c - 3
d - 3
e - 1
f - 2
h - 1
j - 1
k - 1

Any help would be appreciated. Thanks.

 

[vbaInet]

I had a bit of time so I thought should give it a go. I've altered your database to count a, b, c and d. Just follow what I've done to count the rest. Not as easy as you would have imagined.

Have a look.

 

[map816]

I have been applying this to other fields that have standard responses instead of alphabet concatenation. I am not sure the mdlCountLetters module is working properly with it. Will this apply to other uses outside of alphabet options? I added a sample data set to review. Thanks.

 

[vbaInet]

The function I created was following your original request. If you want to count the occurence of certain words then you need a much more sophisticated algorithm like a Soundex algorithm.

Have a look at DCrake's site for the soundex example:

http://www.xcraftlimited.co.uk/Sample-Databases.html