Using a Zeros Table in Microsoft Access

Shawn LaRoche, Senior Research Analyst, Mount Wachusett Community College

Microsoft Access allows users to build queries that, among other things, can be used for simple analysis, reporting, and production automation. Ensuring output is clean, accurate, and complete is of paramount importance, especially when automating processes. Ensuring data are complete can be easy to overlook when querying in Microsoft Access. In this Tech Tip I will demonstrate how users can ensure their information contains all possible parameters, even when there is no output for them all, by utilizing a ‘zeros table.’

Users will encounter tables that do not include all iterations of data. For example, institutions that have multiple campuses may offer certain programs on one campus, but not another. So, when a user queries from a table and there is not an iteration of the program at a campus, no output will be included in the query {see figures 1 (table) and 2 (crosstab) below}.

Oct2017a.PNGOct2017b.PNG

In figure 1, the data looks complete since various iterations of campus, program, and year are included. However, since there are no combinations of Campus B and Program A, the crosstab in figure 2 shows no output for that specific combination of parameters.

A simple way to account for parameters that have no output is to create a zeros table, including all possible parameter iterations that could be included in the data table, and then appending the zeros to the data table. Start by creating tables for each individual parameter. In this example, create three stand-alone tables for campus, program, and year. Users will need to create the tables one at a time from the data table (i.e. Sample Campus ENR in this example). Figures 3-6 below show step-by-step instructions on how to create a table. Repeat them for each parameter.

Oct2017c.PNG

Oct2017d.PNG

Oct2017e.PNG

OCt2017f.PNG

Once the lookup tables are created, users can create a zeros table. Create a zeros table using all lookup tables and a new field to match the data table measure (in this example, ‘Enrollments’) as seen in figure 7. No linking of parameters should occur. Note that after the measure name ‘Enrollments’ is a colon and a 0. When the table is created, using the 0 in this manner will include a 0 in each field in the Enrollments column (see figure 8). 

Oct2017g.PNG

The zeros table includes all eight possible iterations of the parameters as opposed to the six in the data table (as seen in figure 1). At this point, the next step could be to append the data in the zeros table to the data table. However, this would create duplicate entries in the data table for parameters that are already included. To ensure using only parameters that are not already included in the data table, the next step is to run a delete query. Running a delete query will remove the parameters from the zeros table that are already included in the data table. See figures 9-11 for instructions on running the delete query.

Oct2017h.PNG
Oct2017i.PNG
Oct2017j.PNG

Once the delete query has been run, the user can append the remaining records from the Zeros table to the data table. Figures 12-13 below show step-by-step instructions on how to append the records while queries 14 and 15 show the updated results.

Oct2017k.PNG
Oct2017L.PNG
Oct2017m.PNG

The example included in this Tech Tip is kept intentionally simple. The real value of this method comes when handling larger datasets that have many parameters with no values. Other examples where using a zeros table could help users fill in gaps are when analyzing data by small geographies, various demographics, or brief time intervals. I hope this proves useful and I would love to hear other ways IR practitioners use this or other similar methods to fill in data gaps in the comments, below.

 

 Comments

 
To add a comment, Sign In
There are no comments.