如何在Access数据库中计算年终累计在活人口？

How to Calculate Year-End Cumulative Living Population in an Access Database?

提问人：Frank Lin 提问时间：11/14/2023 最后编辑：Frank Lin 更新时间：11/16/2023 访问量：77

问：

你好 StackOverflow 社区，

我正在使用一个 Microsoft Access 数据库，其中包含一个跨越多年的大型数据集。数据包括带有姓名、出生日期和死亡日期字段的单个记录。死亡日期字段可能为空，表示此人还活着。我的目标是在 Access 中创建一个 SQL 查询，用于计算每年年底（截至 12 月 31 日）的累计存活人数。

查询需要考虑每年 12 月 31 日或之前出生的每个人，以及谁仍然活着（无效死亡日期）或在该年年底之后死亡。我的主要挑战是，鉴于数据量很大，时间范围很广，如何有效地实现这一目标。

我特别在寻找可以在 Access 中实现的解决方案。但是，如果您的建议涉及使用不同的数据库系统，请明确指出这一点，并指定您的脚本适用于哪个数据库。

任何有效构建此查询的见解或示例将不胜感激。

提前致谢！

我只能计算每年的出生人数和每年的死亡人数，我不知道如何将这些年来的数据相加。除非使用 Excel。

我所期望的：

我想直接在 SQL 中实现它，而不需要其他工具，例如 Excel。

下面是示例输入数据：

编号 性 生日 数据日期
2 女性 1973/5/12 2009/2/22
1 雄 1974/2/3 1997/3/6
6 雄 1975/8/15
7 雄 1975/12/31 2004/3/21
16 雄 1976/7/2 2011/12/8
13 雄 1977/11/19 1995/4/17
15 女性 1979/1/31 2018/8/8
8 女性 1979/7/19 2008/9/2
11 雄 1980/1/14
20 女性 1982/3/12 1991/3/25
12 女性 1984/2/29 1998/8/13
5 女性 1984/11/12 1992/3/28
17 女性 1986/1/7
3 雄 1987/4/28
4 女性 1988/7/23 2013/3/23
19 雄 1989/5/30 2004/7/25
18 女性 1989/8/11 2012/6/23
10 女性 1991/2/8 2008/4/12
14 女性 1991/2/10
9 女性 1994/3/1 2015/5/12

下面是一个示例结果，我通过使用 Excel 电子表格中的公式进行计算获得。

年终 人口 雄 女性
1973/12/31 1 0 1
1974/12/31 2 1 1
1975/12/31 4 3 1
1976/12/31 5 4 1
1977/12/31 6 5 1
1978/12/31 6 5 1
1979/12/31 8 5 3
1980/12/31 9 6 3
1981/12/31 9 6 3
1982/12/31 10 6 4
1983/12/31 10 6 4
1984/12/31 12 6 6
1985/12/31 12 6 6
1986/12/31 13 6 7
1987/12/31 14 7 7
1988/12/31 15 7 8
1989/12/31 17 8 9
1990/12/31 17 8 9
1991/12/31 18 8 10
1992/12/31 17 8 9
1993/12/31 17 8 9
1994/12/31 18 8 10
1995/12/31 17 7 10
1996/12/31 17 7 10
1997/12/31 16 6 10
1998/12/31 15 6 9
1999/12/31 15 6 9
2000/12/31 15 6 9
2001/12/31 15 6 9
2002/12/31 15 6 9
2003/12/31 15 6 9
2004/12/31 13 4 9
2005/12/31 13 4 9
2006/12/31 13 4 9
2007/12/31 13 4 9
2008/12/31 11 4 7
2009/12/31 10 4 6
2010/12/31 10 4 6
2011/12/31 9 3 6
2012/12/31 8 3 5
2013/12/31 7 3 4
2014/12/31 7 3 4
2015/12/31 6 3 3
访问SQL MS-Access

评论

1赞 Chris Maurer 11/14/2023
如果您付出一些努力并告诉我们您尝试过什么，您更有可能获得帮助。Stack Overflow 不是免费的代码编写服务。
1赞 June7 11/15/2023
将示例数据和所需输出显示为格式化文本表。尝试的 SQL 也很好。可以在允许附加文件并提供数据库进行分析的讨论论坛中发帖。若要在 Access 中完成，可能需要将 SQL 聚合和报表（使用聚合计算）组合在一起。
0赞 Frank Lin 11/15/2023
我已经发布了示例数据和结果。有帮助吗？

答：

1赞 June7 11/16/2023 #1

这确实变得复杂。需要一系列查询。

查询 1：

SELECT ID, Sex, BirthDate AS EventDate, DateSerial(Year([BirthDate]),12,31) AS EndDate, "Birth" AS Category FROM Stats
UNION SELECT ID, Sex, DatahDate, DateSerial(Year(Nz([DatahDate],#12/31/3000#)),12,31), "Death" FROM Stats;

查询 2

TRANSFORM Sum(IIf([Category]="Birth",1,-1)) AS SumYearPop
SELECT EndDate, Sum(IIf([Category]="Birth",1,-1)) AS Pop
FROM Query1
GROUP BY EndDate
PIVOT Sex;

下一步是计算运行总和。生成一个报表，该报表使用文本框 RunningSum 属性执行运行计算，这很简单。我提供的内容将仅显示那些有数据的年份的记录。在没有数据的情况下强制记录多年需要表中的虚拟数据，或者将 Query1（或每个 SELECT 行）或 Query2 联接到具有所有可能年份的数据集。

我发现 CROSSTAB 不能用作正在运行的总查询的源。Query2 可以替换为：

SELECT EndDate, Sum(IIf([Category]="Birth",1,-1)) AS SumYear, 
Sum(IIf([Sex]="female",IIf([Category]="Birth",1,-1),0)) AS SumYearFem, 
Sum(IIf([Sex]="male",IIf([Category]="Birth",1,-1),0)) AS SumYearMale
FROM Query1
GROUP BY EndDate;

这确实可以用作运行求和查询的源。例：

SELECT Query2.EndDate, 
(SELECT Sum(SumYear) FROM Query2 AS Dupe WHERE Dupe.EndDate<=Query2.EndDate) AS RunPop,
(SELECT Sum(SumYearFem) FROM Query2 AS Dupe WHERE Dupe.EndDate<=Query2.EndDate) AS RunFemale,
(SELECT Sum(SumYearMale) FROM Query2 AS Dupe WHERE Dupe.EndDate<=Query2.EndDate) AS RunMale
FROM Query2;

请注意，涉及相关子查询的查询对于大型数据集的执行速度可能会很慢。

评论

0赞 Frank Lin 11/16/2023
很好的解决方案，通过将出生和死亡日期均匀地转换为数字的增加或减少，然后可以通过简单的日期过滤和求和来计算结果。谢谢。
0赞 Frank Lin 11/16/2023
你介意我修改第二个 Query2 和 Query3（最后一个查询）吗？由于 列和 SumYear 不应出现在 Query2 中，否则某些 EndDate 将重复出现，例如 3000/12/31。
0赞 June7 11/16/2023
好点子。当我从 CROSSTAB 修改为简单的 GROUP BY 聚合时，我忘记删除 字段。我会编辑答案。
0赞 Frank Lin 11/16/2023 #2

受到 @June7 答案的启发，我想出了另一个解决方案，如下所示，也使用了 3 个查询：

  1. 将示例数据输入到名为“Stats”的表中。
  2. 创建查询 endDates 以收集所有事件的 EndDates
SELECT DISTINCT endDates.enddate
FROM (SELECT DateSerial (Year([BirthDate]), 12, 31) AS EndDate FROM Stats
UNION select iIf (DeathDate is not null, DateSerial (Year([DeathDate]), 12, 31), null) AS enddate FROM Stats
) AS endDates
WHERE (((endDates.enddate) Is Not Null));
  1. 创建 Stats 和 endDates 的笛卡尔乘积，然后添加过滤器
SELECT Stats.ID, Stats.Sex, Stats.BirthDate, Stats.DeathDate, endDates.enddate
FROM Stats, endDates
WHERE (((Stats.BirthDate) <= endDates.enddate) And ((endDates.enddate) < Stats.DeathDate) or ((Stats.DeathDate) Is Null));
  1. 通过数据透视表获取结果。
TRANSFORM Count (filter.[ID]) AS ID_Count
SELECT filter.[enddate], Count (filter.[ID]) AS ID_Subtotal
FROM filter
GROUP BY filter.[enddate]
ORDER BY filter.[enddate]
PIVOT filter.[Sex];

我不确定它是否更有效（请随时发表评论），但我认为这个解决方案更具可读性。

上一个：Java SQL 在表中插入主键

下一个：查询 MS Access 问题中的自定义字段