This project introduces students to database design and implementation using Access 2013. It is appropriate for use in accounting or management information systems undergraduate or graduate courses to satisfy learning objectives associated with developing database design and implementation skills, and understanding and implementing application controls. To complete the project, students should have an understanding of business models, entity integrity and referential integrity rules, and the concept of cardinalities. The project can be completed in or out of class and takes about 1–2 hours. This resource includes a tutorial and sample final assessment, with solutions. Upon completion, students will be able to create tables, forms, queries, and reports and understand and enable data input validation controls within Access 2013.

Although students interact with databases on a daily basis (e.g., student registration system, online shopping, social media sites), they rarely think about the underlying structure, design, and controls of those databases. While accounting graduates will probably never need to create a database from scratch, they will routinely evaluate data, processes, and controls associated with databases. Virtually all introductory accounting and management information systems (AIS and MIS) courses include database design as a topic (Murthy & Ragland 2009). This project introduces students to database implementation using Microsoft Access 2013. Students create a database to keep track of students, assignments, and related dates and grades on assignments. The project requires students to create tables, add validation controls on fields, design forms to ease data entry, create relationships, and develop useful queries and reports. The project purposely focuses on a non-financial context that is familiar to students. By using familiar tasks (tracking assignments and grades), students can focus on learning database design and implementation, rather than being concerned with whether they are properly applying accounting rules and conventions. Once students become competent in Access, they can apply these skills in newer contexts, such as creating databases to record financial transactions associated with revenue and expenditure cycles.

I was motivated to create this project, as the existing tutorials associated with Access were generally long and complex. There is limited time in class, and increasingly more content to cover – especially in information system classes, where we are updating material almost daily to accommodate myriad changes in technology. This particular assignment is condensed, (can be completed in 1–2 hours), yet covers the basic skills necessary to create a simple database. It is designed to address both 1:N and M:N cardinality types, and includes various types of queries. Anecdotal evidence from recruiters and the profession indicates strong support for students to develop software and data modeling proficiency. This project requires students not only learn Access, but also import data from Excel, enhancing their ability to work with various software applications.

This project is appropriate for use in undergraduate or graduate introductory AIS or MIS courses. It addresses two types of learning objectives. First, it addresses learning objectives calling for student proficiency in Access. Second, it addresses learning objectives associated with student understanding of and ability to implement application controls (in the table creation, field settings steps). Students should have general computing skills (file creation, navigation) to begin this project, but need no prior database application experience. Students should be familiar with database modeling (ER, REA diagrams), the entity integrity and referential integrity rules, and the concept of cardinalities. A summary of these rules is included in Appendix A. This project should be used following a discussion of business modeling, and may be revisited when covering the topic of internal control.

Student Learning Objectives

  • 1 – Students will be able to create tables, relationships, forms, reports, and queries.

  • 2 – Students will be able to apply input validation controls to fields in tables.

  • 3 – Students will be able to import data from Excel to Access.

  • 4 – Students will be able to create visually appealing and useful forms and reports.

  • 5 – Student will be able to create simple and specialty queries.

“The AICPA Core Competency Framework, developed by educators for educators, defines a set of skills-based competencies needed by all students entering the accounting profession, regardless of the career path they choose (public/industry/government/nonprofit) or the specific accounting services they will perform” (American Institute of Certified Public Accountants, Inc. 2005). Included in the frameworks' three areas (functional, personal, and broad business perspective) are explicit references to students' ability to leverage technology. Additionally, anecdotal evidence indicates that recruiters place student competency in software applications such as Microsoft Access, Excel, and Word high on their lists. These skills are in demand, and often, departmental expectations are that AIS courses will include them.

Despite the demand for students to develop software skills, there is often little time to devote to them, given the “kitchen sink” nature of typical introductory AIS courses. Murthy and Ragland (2009) find great diversity across AIS syllabi, noting a long list of topics covered, with less overlap than they find among MIS syllabi. As a result, AIS courses typically include a variety of other topics, allowing limited time for software skills development. Badua (2008) finds that topical diversity of AIS courses grew over the 2001–2007 period, with an increased focus on database design and development. However, coverage of database/Access projects actually decreased post 2002, from 4.1% of class time to 2.8% of class time (Murthy & Ragland 2009). With limited time to devote to Access skills development, there is a need for projects that are limited in scope, but comprehensive enough to cover the basics.

As noted earlier, existing Access instructional resources are available, but tend to be long and complex. A search on Amazon.com revealed Access 2013 resource books ranging in price from $15.00–$68.00; all included multiple lessons and projects. While these resources are appropriate for semester-long courses focusing on Access alone, for most AIS courses, they are excessive. A search of Access cases published in accounting education journals over the past ten years revealed several Access and database projects. Schafer and Hurtt (2006) provide a thorough project for developing a database for the revenue cycle at a lemonade stand, while Ballenger (2007) provides a “moderately complex” project for a database for a veterinary clinic. Bradford, Samuels, and Wood (2008) offer a more comprehensive case, involving conversion from a legacy system to an Access database. Other cases focus primarily on querying or data extraction and analysis skills such as Garnsey and Hotaling (2013) for financial analysis, and Antcliff, Doren, Harris, and Hayes (2012), Worrell (2010), and Loraas and Searcy (2010), for auditing procedures.

While these cases serve important purposes, none cover the basics of database creation using a simple, non-accounting context. Thus, this project fills a gap by providing a concise, yet comprehensive introduction to Access, in a way that does not require students to understand transaction cycle terminology. Further, this project includes a sample assessment, which allows instructors to measure student proficiency, in a hands-on format.

The project consists of two parts: a tutorial and a sample assessment. The tutorial is a detailed, step-by-step guide students follow to create a database in Access for tracking students, assignments, and attributes associated with student assignments. Extensive screenshots accompany all steps, allowing students to visually confirm that they are on the right track. The tutorial addresses all five learning objectives listed in the related section above. It requires students to create tables, relationships, forms, reports, and queries, import data from Excel to Access, and enable input validation controls on data fields. The tutorial includes a discussion of entity and referential integrity in Appendix A, which helps remind students of rules to use when designating primary keys and creating relationships between tables. The scenario includes a 1:M relationship between Chapter and Assignment, and M:N relationship between Student and Assignment, requiring students to be able to create a linking, or associative table with a concatenated primary key.

Other material available to instructors includes a sample assessment, to use in class for determining individual student proficiency in Access. Instructors should administer the assessment after students work through the tutorial.

I have used some form of this tutorial and assessment over the past eight years, with one-two sections (thirty students per section) per semester. I made minor adjustments over time, as versions of the software changed (from Access 2003 through 2013), but the basic skills and knowledge taught and tested remained virtually constant. I use the assessment to determine whether my class is meeting assurance of learning standards related to software skills, as part of our AACSB accreditation. Since fall 2006, in 22 out of 23 sections taught (approximately 15–35 students per section), my class has met the standard, determined as 70% of students earning at least 70% on the assessment.

Demands on accounting faculty to prepare students adequately for a technology-centered work world are growing exponentially. These demands frequently come to AIS faculty first. Because AIS faculty often teach in computer labs, and because others see systems as synonymous with technology, we are the de facto “technology experts” and become responsible for meeting these demands. While it is tempting to eliminate skills such as database design and creation from our classes, due to the complexity and amount of time it seems to take to adequately teach them, doing so would eliminate virtually all hands-on contact our students have with databases at the design and implementation levels.

Existing Access tutorial resources are involved, long, and complex; they are better used for an entire course on Access, than for introducing students to the basics of database creation. This tutorial uses a non-accounting context with which students are intimately familiar, to allow students to develop basic skills in database creation. Students can complete this tutorial in 1–2 hours. It covers table, form, relationship, report, and query creation. It also provides hands-on practice identifying and implementing data input validation controls. Alone, the tutorial provides students the basics; when followed up with a project requiring students to create an accounting-based database (e.g., one that records data associated with a revenue or expenditure cycle), it provides a strong foundation and reference tool. I have used the assessment successfully over eight years to evaluate students' performance in actually performing the Access tasks. This hands-on assessment has provided a valid and reliable measure of student learning. Overall the project is time-tested, and should be useful for faculty whose goals include helping students understand database design and creation.

American Institute of Certified Public Accountants, Inc. (AICPA)
.
2005
.
Antcliff
,
M.
,
Doren
R.
,
Harris
L.
, and
D.
Hayes
.
2012
.
A Case to Provide Students Practice in Basic and Advanced Functions of IDEA Software
.
AIS Educator Journal
,
7
(
1
):
69
73
.
Badua
,
F.
2008
.
Pedagogy and the PC: Trends in the AIS Curriculum
.
Journal of Education for Business
83
(
5
):
259
264
.
Ballenger
,
R. M.
2007
.
A REA Business Process Modeling and Relational Database Development Case: The Paws and Claws Veterinary
.
AIS Educator Journal
2
(
1
):
23
31
.
Bradford
,
M.
,
Samuels
J.A.
, and
R.E.
Wood
.
2008
.
Beta Auto Dealers: Integrating disparate data to solve management problems
.
Issues in Accounting Education
23
(
2
):
309
326
.
Garnsey
,
M.
and
A.
Hotaling
.
2013
.
What's Happening to Profits at Cazenovia Creations?
AIS Educator Journal
8
(
1
):
24
29
.
Loraas
,
T. M.
, and
D.L.
Searcy
.
2010
.
Using Queries to Automate Journal Entry Tests: Agile Machinery Group, Inc
.
Issues in Accounting Education
25
(
1
):
155
174
.
Murthy
,
U. S.
, and
L.
Ragland
.
2009
.
Towards an understanding of accounting information systems as a discipline: A comparative analysis of topical coverage in AIS and MIS courses
.
AIS Educator Journal
4
(
1
):
1
15
.
Schafer
,
B. A.
, and
K.
Hurtt
.
2006
.
The Lemonade Stand: A Teaching Case for Developing an Information System for the Revenue Process
.
AIS Educator's Journal
1
(
1
):
5
10
.
Worrell
,
J. L.
2010
.
Blazer Communications: A Procurement Audit Simulation
.
Issues in Accounting Education
25
(
3
):
527
546
.