如何在MS Access中串行处理Skills表，统计各地点开发者技能持有量

不需要循环，通过关联查询+分组就能一次性得到所有技能在各地点的开发者数量，这是最高效的方式：

SELECT 
    s.Skill,
    r.Location,
    COUNT(r.[Resource-ID]) AS DeveloperCount
FROM 
    Skills s
LEFT JOIN 
    Resources r ON r.Resume LIKE "*" & s.Skill & "*"
GROUP BY 
    s.Skill, r.Location
ORDER BY 
    s.Skill, r.Location;

关键细节说明 #

• LEFT JOIN：确保即使某个技能在所有地点都没有匹配的开发者，也会显示该技能（对应计数为0）
• 模糊匹配规则：LIKE "*" & s.Skill & "*" 匹配简历中包含该技能的记录；如果你的需求是匹配技能开头（比如原示例的Like "SKILL-ID*"），改成LIKE s.Skill & "*"即可
• GROUP BY：按技能+地点分组，自动统计每组的开发者人数

如果希望把三个地点的计数放在同一行（列展示形式），可以用交叉表查询：

TRANSFORM COUNT(r.[Resource-ID]) AS DeveloperCount
SELECT 
    s.Skill
FROM 
    Skills s
LEFT JOIN 
    Resources r ON r.Resume LIKE "*" & s.Skill & "*"
GROUP BY 
    s.Skill
PIVOT 
    r.Location;

如果确实需要逐个遍历Skills表的技能执行统计，可以用VBA实现：

1. 按Alt+F11打开Access的VBA编辑器
2. 插入新模块，粘贴以下代码：

Sub CountSkillsByLocation()
    Dim db As DAO.Database
    Dim rsSkills As DAO.Recordset
    Dim rsResult As DAO.Recordset
    Dim strSkill As String
    Dim strSQL As String
    
    ' 创建结果存储表（若已存在则先删除）
    On Error Resume Next
    CurrentDb.Execute "DROP TABLE SkillLocationCounts;"
    On Error GoTo 0
    CurrentDb.Execute "CREATE TABLE SkillLocationCounts (" & _
        "Skill TEXT(50), " & _
        "Location TEXT(50), " & _
        "Count INTEGER);"
    
    Set db = CurrentDb
    Set rsSkills = db.OpenRecordset("SELECT Skill FROM Skills;")
    
    ' 遍历每个技能
    Do While Not rsSkills.EOF
        strSkill = rsSkills!Skill
        
        ' 生成当前技能的地点统计SQL
        strSQL = "SELECT Location, COUNT([Resource-ID]) AS DevCount " & _
                 "FROM Resources " & _
                 "WHERE Resume LIKE '*" & Replace(strSkill, "'", "''") & "*' " & _
                 "GROUP BY Location;"
        
        Set rsResult = db.OpenRecordset(strSQL)
        
        ' 将统计结果写入结果表
        Do While Not rsResult.EOF
            db.Execute "INSERT INTO SkillLocationCounts (Skill, Location, Count) " & _
                       "VALUES ('" & Replace(strSkill, "'", "''") & "', '" & _
                       Replace(rsResult!Location, "'", "''") & "', " & rsResult!DevCount & ");"
            rsResult.MoveNext
        Loop
        
        rsSkills.MoveNext
    Loop
    
    ' 释放资源
    rsResult.Close
    rsSkills.Close
    Set rsResult = Nothing
    Set rsSkills = Nothing
    Set db = Nothing
    
    MsgBox "统计完成，结果已保存到SkillLocationCounts表", vbInformation
End Sub

关键细节说明 #

• 代码会自动创建SkillLocationCounts表存储最终结果
• Replace(strSkill, "'", "''")：处理技能名称中的单引号，避免SQL语法报错
• 如需匹配技能开头，把代码里的'*" & Replace(strSkill, "'", "''") & "*'改成'" & Replace(strSkill, "'", "''") & "*'即可

内容的提问来源于stack exchange，提问作者DM Novice