Calculate a field's value based on multiple re

Question

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

the records in ESDailyTotals should be:

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

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.

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. :)

Thanks in advance everyone!

Answer 1

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

Name the query ESDailyTotals.