根据Access DB另一个表中的多个记录计算字段的值 [英] Calculate a field's value based on multiple records in another table in Access DB

问题描述

我正在尝试在Access 2010中创建数据库，但遇到了一些问题.我目前有两个表，EarlyStageListResults& ESDailyTotals.这些表中的每个表都有一个名为Records的字段，其中ESDailyTotals是EarlyStageListResults中多个条目的摘要.我需要做的是让ESDailyTotals中的Records字段成为EarlyStageListResults中多个Records字段的总和.例如，给定EarlyStageListResults中的以下记录:

I'm trying to create a database in Access 2010, and have run into a bit of a problem. I currently have two tables, EarlyStageListResults & ESDailyTotals. Each of these tables has a field named Records, with ESDailyTotals being a summary of multiple entries in EarlyStageListResults. What I need to do is have the Records field in ESDailyTotals be the sum of multiple Records fields in EarlyStageListResults. For example, given the following records in EarlyStageListResults:

Date          Records
4/22/16       2000
4/22/16       3000
4/22/16       1500
4/21/16       1200
4/21/16       2700

ESDailyTotals中的记录应为:

Date          Records
4/22/16       6500
4/21/16       3900

我知道以后可以通过VBA和一个表单事件来计算，但是最理想的情况是，我希望能够在EarlyStageListResults中的任何Records字段发生更改时立即对其进行更新.似乎可以使用Access Macro Editor(不确定名称，但可以通过一系列组合框而不是通过VBA创建宏的工具)来执行此操作，但是我从来没有对使用该工具的理解，因此始终依赖于Forms和VBA.基本上，如果有一个事件在字段更新时触发，并且有一种将VBA代码输入该事件处理程序的方式(如使用Access Forms一样)，那么我可以使用DLookup或我认为的SQL语句来执行此操作，但是我不这样做不知道如何获取该事件处理程序.

I know this can be calculated later through VBA and a form event, but optimally I'd like to be able to have it update as soon as any of the Records fields in EarlyStageListResults changes. It looks like there may be a way to do this using the Access Macro Editor (not sure of the name, but the tool where you can create a macro through a series of combo boxes rather than through VBA), but I've never gotten an understanding of using that tool, so have always relied on Forms and VBA instead. Basically if there's an event that triggers when a field is updated, and a way to enter VBA code into that event handler like you can with Access Forms, then I can do it with either DLookup or an SQL statement I think, but I don't know how to grab that event handler.

这一切都需要在Access本身中完成，我不能使用外部程序来更新Access数据库文件中的记录.这是为了工作，而且(至少在官方上而言)任何自定义程序都是很大的禁忌. :)

This does all need to be done within Access itself, I can't use an external program to update the records in an Access database file. This is for work, and (officially at least) any custom programs are a big no-no. :)

提前感谢大家！

推荐答案

您使这种方法过于复杂.您只需要一个表和一个数据库视图(我认为在Access中称为查询)

You're making this way too complicated. You just need one table and a database view (called a query in Access, I think) that is defined as

select Date, sum(Records) as Records from EarlyStageListResults
group by Date

将查询命名为ESDailyTotals.

这篇关于根据Access DB另一个表中的多个记录计算字段的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！