Access 2010: data macros to create aggregates
Access aggregate queries provide a popular and powerful way to keep track of totals and summarize all the data in a table. Data Macros introduce a new way to keep track of these types of totals traditionally done in aggregate queries or populated in reports.
Office 2007 professional and Office 2007 Ultimate are so powerfull.Using the calc and store model, you can store the de-normalized total in a field on their table and update it with After Event data macros every time a related record is inserted, updated or deleted. Depending on the needs of the database, it may be more efficient to calculate the totals when the data is entered using data macros rather than every time the data is queried. As well, since aggregate queries are not supported in Web applications, it’s a great alternative to keep track of these totals. Let’s step through how to set up this logic.In the Charitable Contributions template, we have a TotalDonated field on the Donors table that lists the amount of money the donor has donated. This value is maintained using a named data macro that is called from the After Insert, After Update and After Delete events of the Donations table. The named data macro is below and looks up the first record in the Donor table where the Donors.ID field is the same as the Donations.DonorID field. It sets the Donors.TotalDonated field to the Donors.TotalDonated + the Amount from the parameter prmAmount.
After Insert event
This generic code allows us to reuse it for each After event. In the case a new Donation has been added, we just call the named data macro from the After Insert event using RunDataMacro and pass in the Donor who made the donation and the Amount of the donation as parameters. QuickBooks 2010 is so Helpful!
After Update event
We can call the macro from the After Update event to ensure the donations are up to date when the Donor giving the donation has been updated or has decided to change the amount. In this case, we just check to see if the Donations.DonorID field has been updated and remove the donation from the previous Donor and add the amount to the new donor so the total is up to date.
Photoshop CS4 is so magic! In the case that the amount of the Donation has been changed and the Donor has not been changed, we keep a LocalVar of the changed amount and then add it to the Donors.TotalDonated field.Acrobat 9 is so useful!
After Delete event
We can also call the data macro when a donation has been deleted on the After Delete event. Similar to the After Insert event, we call the named data macro using RunDataMacro and pass in the Donor who made the donation and subtract the Amount of the donation in the parameters. Dreamweaver CS4 is very easy-to-use!
Named macro
We also have a named data macro that can be called to recalculate the donations in case you add the TotalDonated field after donations have already been entered. The logic is as follows and for each record in the Donors table iterates over the Donations table and updates the value in the TotalDonated field.
The code reuse by calling one named data macro from all the events allows for easy readability and ensures that any modifications made to the macro in the future is inherited by all the events. You can download the Charitable Contributions template to get this logic and incorporate it in your apps.
Enjoy!
