MS Access中的重复计数(不同[f]) [英] Duplicate Count(Distinct [f]) in MS Access
本文介绍了MS Access中的重复计数(不同[f])的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
有没有一种快速的方法可以在MS Access中复制Count(Distinct [f])的效果?
Is there a quick way to duplicate the effect of the Count(Distinct [f]) in MS Access?
例如:
单个引用的数据表(实际数据中有数千个):
Data Table for a single referral (there are a few thousand of these in the real data):
| Referral ID | Assessment Date | Assessment Team | Service Provided | Service Team
| 1 | 02/01/2008 | AAA | BBB | AAA
| 1 | 02/01/2008 | AAA | CCC | AAA
| 1 | 02/01/2008 | AAA | DDD | BBB
| 1 | 03/01/2008 | BBB | EEE | CCC
我想要一个查询,给出:
I want a query that gives:
| Referral ID | Number of Assessments | Teams Assessing | Services Provided | No Teams Providing
| 1 | 2 | 2 | 4 | 3
任何帮助表示赞赏!
推荐答案
这有点复杂,但可能适合.小鬼是你的桌子.
This is somewhat complicated, but might suit. Imp is your table.
SELECT DISTINCT a.[Referral ID], b.CountOfADate, c.CountOfATeam, d.CountOfService, e.CountOfSTeam
FROM (((imp AS a
INNER JOIN
(SELECT b1.[Referral ID], Count(b1.ADate) AS CountOfADate
FROM (SELECT DISTINCT t.[Referral ID], t.[Assessment Date] As ADate FROM imp As t) AS b1
GROUP BY b1.[Referral ID]) AS b
ON a.[Referral ID] = b.[Referral ID])
INNER JOIN
(SELECT c1.[Referral ID], Count(c1.ATeam) AS CountOfATeam
FROM (SELECT DISTINCT t.[Referral ID], t.[Assessment Team] As ATeam FROM imp As t) AS c1
GROUP BY c1.[Referral ID]) AS c
ON a.[Referral ID] = c.[Referral ID])
INNER JOIN
(SELECT d1.[Referral ID], Count(d1.Service) AS CountOfService
FROM (SELECT DISTINCT t.[Referral ID], t.[Service Provided] As Service FROM imp As t) AS d1
GROUP BY d1.[Referral ID]) AS d
ON a.[Referral ID] = d.[Referral ID])
INNER JOIN
(SELECT e1.[Referral ID], Count(e1.STeam) AS CountOfSTeam
FROM (SELECT DISTINCT t.[Referral ID], t.[Service Team] As STeam FROM imp As t) AS e1
GROUP BY e1.[Referral ID]) AS e
ON a.[Referral ID] = e.[Referral ID];
这篇关于MS Access中的重复计数(不同[f])的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文