Counting character count in Access database column ins SQL
- by jzr
Good Evening.
My problem is possibly very easy, I just have spent some time researching now and probably have a brain lock and unable to solve this, help would be much appreciated.
database structure:
col1 col2 col3 col4
====================
1233+4566+ABCD+CDEF
1233+4566+ACD1+CDEF
1233+4566+D1AF+CDEF
I need to count character count in col3, wanted result in from the previous table would be:
char count
===========
A 3
B 1
C 2
D 3
F 1
1 2
is this possible to achieve by using SQL only?
at the moment I am thinking of passing a parameter in to SQL query and count the characters one by one and then sum, however I did not start the VBA part yet, and frankly wouldn't want to do that.
this is my query at the moment:
PARAMETERS X Long;
SELECT First(Mid(TABLE.col3,X,1)) AS [col3 Field], Count(Mid(TABLE.col3,X,1)) AS Dcount
FROM TEST
GROUP BY Mid(TABLE.col3,X,1)
HAVING (((Count(Mid([TABLE].[col3],[X],1)))>=1));
ideas and help are much appreciated, as being said this is probably very for some of your guys, I don't usually work with access and SQL.
Thanks.
