Meet Malori! Malori is a paralegal in Microsoft's legal department. She manages patent applications and assigns them out to attorneys and portfolio managers as well as other paralegals. Malori was looking for a way to make her team more efficient by presenting all of the information they needed in a single web page or application. Our team jumped in and made an Access Services solution, which allows her to manage all the data and provide custom views to her teammates. Today this web database is used by over 80 attorneys working on different cases at Microsoft.
In today’s episode, Malori shares her experience with Access Services and how it has helped her team be more efficient.
Today’s Power Tip comes from Brandon with OpenGate Software, offering Microsoft Access products for Access users of every experience level, including UI Builder, Designer, and Dashboard Builder for Microsoft Access.
As we all know, it's never easy to estimate how much work will be involved when creating a new Access database. To help with that, OpenGate Software has produced a white paper designed to help Access users (particularly newer users) figure out how complex their prospective Access database may be, and what skills they may need to get the job done.
http://www.opengatesw.net/ms-access-tutorials/Microsoft-Access-Project-Complexity.htm
Thanks Brandon!
Send your Power Tips to Mike and Chris at accpower@microsoft.com.
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. 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.
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. 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.
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.
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!