You’ll find references to various columns throughout the years that can offer more details about a particular topic. Also feel free to do a Web search. It’s amazing what you can find about Access once you know what to look for!

  1. Take the time to do upfront design. Get outside technical support if needed. (April 2009)

  1. Use functions and properties to refine your queries, reports, and forms. (March 2009, February 2010) Figure 1 is a list of the properties frequently used to refine database objects, and Figure 2 lists common functions that will help.

Layout 1_Page 61 copy.eps Figure 1. Frequently Used Properties Layout 1_Page 61 copy.eps Figure 2. Frequently Used Functions
  1. Design and import tables with a key field to connect data in different tables. If you separate some of the data, be sure to include the key to reconnect the data in case that becomes a need in the future. (May 2009)

  1. Develop queries one step at a time to ensure that the data selected is in line with your expectations and requirements at every step. (January 2009, November 2012-March 2013, April 2014-October 2014, and August 2015-November 2015 are just some of the 27 columns that addressed building queries. Yes, Access is mostly about using queries to gather the information you want!)

  1. If one query doesn’t gather the appropriate data, think about using two or more separate queries. Include a common data element to then link the data together. (April 2015)

  1. To get appropriate data without including duplicates, group by relevant fields and save the query. Then use that query as a data source for the query that will summarize totals. (August 2015)

  1. Every manual process can be built into Access, but sometimes it takes more steps than you think. If one query doesn’t work, keep trying until you work through all the intricacies of your process or question. (December 2012-May 2013)

  1. As a rule, don’t create duplicate tables or tables that are subsets unless you have a specific reason to do so (e.g., if you create summary data that can’t be pulled together in one query or a set of nested queries). You may need to build a temporary table with several append queries. If you have a process in which data is generated periodically (such as monthly), then create a query that empties the data to start, and then group your append queries in a macro. A button on the form to run the macro will automate the process. (November 2009)

  1. When creating complex reports with subreports, remember to reduce, reuse, recycle! Create a subreport by opening the primary report and changing the data source and the fields used for data in the controls. Be sure to “Save As” to give the subreport its own name and keep the main report intact. When you drag the subreport onto the main report, align it to the left and delete the subreport’s label. This will line up the data vertically in the reports. (November 2013, August 2014)

  1. To create an ad hoc variation to standard reports, use text boxes and combo boxes on forms that can be referred to in a query. (March 2010)

  1. Use functions to break up data and to put it back together again. (December 2009, September 2011, November 2015)

  1. When you have a working model of something in one database that you would like to recreate in a new database, use the design of the object to learn how it works. Carefully look at properties of controls to see if they have been changed from their defaults. (November 2014)

These 12 ways of Access will put you on the right path to developing practical databases that add value to your work processes. As you continue your work in Access, use all of your resources and cultivate relationships with people who can help you. Conversely, help others by sharing what you know. Develop a process and support system for continuous improvement.

Thanks again to all the readers who have shared their questions and ideas with me over the years and to all of you quiet readers out there as well. I hope this summary serves as an excellent reference for your work with Access in the future. The Access column will continue next month with a new contributor to answer your questions and cover Access topics that will help you build and improve databases, but you are also still welcome to e-mail me with questions, which I will try to answer. Happy holidays and best wishes for a happy New Year!


SF BEST PRACTICES

Use all of your resources and cultivate relationships with people who can help you. Conversely, help others by sharing what you know.

About the Authors

Patricia Cox
Patricia Cox has taught Excel and Access to management accounting students and other college majors and has consulted with local area businesses to create database reporting systems since 1998. She also is a member of IMA’s Madison Chapter. To send Patricia a question to address in the Access column, e-mail her at kathrynmann@tds.net.

December 2015

Current Issue
All Issues
Show More

Publication Highlights