Counting occurrences in a table

Question

Lets say I want to count the total number of occurrences of a name contained within a string in a column and display that total next to all occurrences of that name in a new column beside it. For example, if I have:

Name  | Home Address | Special ID 
==================================
Frank | 152414       | aTRF342
Jane  | 4342342      | rRFC432
Mary  | 423432       | xTRF353
James | 32111111     | tLZQ399
May   |    4302443   | 3TRF322

How would I count the occurrences of special tags like 'TRF', 'RFC', or 'LZQ' so the table looks like this:

Name  | Home Address | Special ID  | Occurrences
================================================
Frank | 152414       | aTRF342     |     3
Jane  | 4342342      | rRFC432     |     1
Mary  | 423432       | xTRF353     |     3
James | 32111111     | tLZQ399     |     1
May   |    4302443   | 3TRF322     |     3

Currently using Access 2007. Is this even possible using a SQL query?

How do you know what the code is for a given row? In the first example, it is the first 3 characters. In the last, it is the last three characters. — Gordon Linoff, May 09 '12 at 14:32
edited to make the special tags appear in the same place, after the first character. So I could use substr like Josvic has suggested — user1384831, May 09 '12 at 14:55

score 0 · Answer 1 · edited May 23 '17 at 11:48

You would have to GROUP BY the substring of Special ID. In MS Access, you can read about how to compute substrings here.

The problem in your case is that your data in Special ID column does not follow a standard pattern, one which easy to extract via the substring function. You might need to use regular expressions to extract such values, and later apply the GROUP BY to them.

With MSSQL, Oracle, PostgreSQL you would be able to declare a stored procedure (example CLR function in MS SQL Server) that would do this for you. Not sure with MS Access.

score 0 · Answer 2 · edited May 23 '17 at 12:11

you can do something like this:

select Name, [Home Address], [Special ID], 
       (select count(*) from [your table] where [Special ID] = RemoveNonAlphaCharacters([Special ID]) ) as Occurrences
from [your table]

auxiliar function (got from this link):

Create Function [dbo].[RemoveNonAlphaCharacters](@Temp VarChar(1000))
Returns VarChar(1000)
AS
Begin

    While PatIndex('%[^a-z]%', @Temp) > 0
        Set @Temp = Stuff(@Temp, PatIndex('%[^a-z]%', @Temp), 1, '')

    Return @Temp
End

score 0 · Answer 3 · answered May 09 '12 at 14:47

lets say your first table is called 'table_with_string'

the following code will show the occurance based on the first 3 charecters of string in Special ID column. since it is not clear how exactly you are passing the string to match

select tws.Name,tws.HomeAddress,tws.SpecialID,str_count.Occurrences from    
table_with_string tws
left join
(select SpecialID,count(*) from table_with_string where specialID like(substring  
(specialid,0,3))
group by specialId) as str_count(id,Occurrences)
on str_count.id=tws.SpecialID

score 0 · Answer 4 · edited Feb 17 '13 at 15:42

0

I would suggest doing this explicitly as a join, so you are clear on how it works:

select tws.Name, tws.HomeAddress, tws.SpecialID, str_count.Occurrences
from table_with_string tws 
join
(
  select substring(spcecialid, 2, 3) as code, count(*) as Occurrences
  from table_with_string tws
  group by substring(spcecialid, 2, 3)
) s
  on s.code = substring(tws.spcecialid, 2, 3)

edited Feb 17 '13 at 15:42

wickedone

542
1
6
18

answered May 09 '12 at 15:46

Gordon Linoff

1,242,037
58
646
786

HansUp · Accepted Answer · 2012-05-09T16:58:06.130

Using Access 2007, I stored your sample data in a table named tblUser1384831. The query below returns this result set.

Name  Home Address Special ID special_tag Occurrences
----  ------------ ---------- ----------- -----------
Frank 152414       aTRF342    TRF                   3
Jane  4342342      rRFC432    RFC                   1
Mary  423432       xTRF353    TRF                   3
James 32111111     tLZQ399    LZQ                   1
May   4302443      3TRF322    TRF                   3

Although your question has a vba tag, you don't need to use a VBA procedure for this. You can do it with SQL and the Mid() function.

SELECT
    base.[Name],
    base.[Home Address],
    base.[Special ID],
    base.special_tag,
    tag_count.Occurrences
FROM
        (
            SELECT
                [Name],
                [Home Address],
                [Special ID],
                Mid([Special ID],2,3) AS special_tag
            FROM tblUser1384831
        ) AS base
    INNER JOIN
        (
            SELECT
                Mid([Special ID],2,3) AS special_tag,
                Count(*) AS Occurrences
            FROM tblUser1384831
            GROUP BY Mid([Special ID],2,3)
        ) AS tag_count
    ON base.special_tag = tag_count.special_tag;

Counting occurrences in a table

5 Answers5