An Access Project, Part 1: Requirements

In this series of articles, David Irvine walks through the process of creating a complete Access application. While the application itself is a valuable tool in a developer’s toolkit, David will concentrate in each article on using some feature of Microsoft Access.

When I was approached to do this series of articles, I was intrigued. I had discussed the possibilities of doing a one- or two-piece set on developing a real-world application. I wanted to illustrate the ease with which MS Access can be interfaced with other Microsoft products. From that beginning, though, the project has expanded into a series where I will look at a practical application from the requirements phase, through design and development, and on to final release.

To give a bit of background for the sample application, I’m currently working on a long-term contract for a company that lives and breathes by project management. In fact, I’ve never worked anywhere where software development is so closely and accurately tracked. I work in the “Technical Services” group (my primary responsibility is electronic commerce) and was assigned the role of backup to the Senior Project Analyst. The Project Analyst’s job is to collect time information about the various projects and to maintain this in a project plan database. The Project Analyst works with the project leaders to ensure that projects are on track and communicates with management the time and cost of the various development initiatives (there are currently 14 separate projects going on). This is a big task, and the company has developed a number of tools to assist the Project Analyst in this daunting, but necessary, task. Having had the dubious honor of being the person to collate and enter this information, I was keenly interested in developing some tools to assist in this task. As a result, I decided to use this project as the basis for this series of articles.

In this article, I’m going to lay out the requirements for the system. In an Access publication, this might seem like a waste of your time, but — as discussed in Mike Gunderloy’s article, “The ABCs of Requirements Management for Access,” in the November 1997 issue of Smart Access — this document is as important as the code that’s eventually produced.

I’m going to describe the system in terms of user-level facilities (Mike also mentions general system properties and developer constraints, which I’m going to ignore for this exercise). As Mike describes in his article, each one of my requirements is “testable” — it’s not hard to imagine a test that would prove whether or not the application meets each of these requirements. Finally, you’ll notice that I stick to “user language” in this document: no discussion of many-to-many relationships, or “normal forms” (that will be in the next issue).

The application and the real world

When determining the requirements for any database application, the most important starting point is reality itself. This might sound dumb, but consider it for a moment. In software development, we have many tasks to do beyond writing code. We need to track the changes made to a program and ensure that only one developer is working on a particular piece of code at any time. We need to develop programming specifications, track the progress of the development of the software, and track the time spent on the project. These are tasks we must do, no matter what development environment, tools, or application we’re involved with.

My sample application assumes that every project is divided into modules, and each module is divided into tasks. The components managed in these modules are:

  • Project names
  • Module names
  • Project resources
  • Program specifications
  • Code development
  • Release history
  • Resource time

Each of these items has specific attributes. As a developer, you need to look at each item individually and identify what the key requirements are in order to successfully deliver the application. You might think that you know what each of these items is, but a good requirements document will identify each in detail and specify how it’s to be used.


A project name is used to identify a project, both to people and to the systems used to monitor and track the project. A common practice in software development is to name systems with an acronym (a couple of projects I’ve worked on were T.A.C.S. [Time Accounting and Control System] and Quest [Quality for Emco in Systems and Technology]). These acronyms can be anything you want, but from the standpoint of managing them in a database, consider the fact that they’ll become the primary key for managing the project. As a result, they must follow the basic rules for key development: They must be unique, non-volatile (not change very often), and easy for people to work with. Calling a project “The Super Colossal Database Design and Development Management and Tracking Project” is fine (it would certainly be unique), but if I were a developer on the project, I’d throttle the database designer after I’d entered that on my time sheet a couple of times.

Very few people appreciate how important a name is to the success of a project. For instance, you could easily shorten “The Super Colossal . . .” to DaDe (Database Design) to get a more manageable name. A further change of the project name to “Database Utility Design” and you have DUDe, and the makings of a great project.


I need to manage each project as a series of modules, which are themselves a collection of related tasks. Some standard modules in a software development project are:

  1. Needs analysis
  2. Data modeling
  3. Specification development
  4. Program development
  5. Testing
  6. Quality assurance
  7. Documentation (no, I don’t like it either, but it’s necessary)
  8. Training
  9. Project management

There can be many more, depending on the project, but these are some of the basics.

Again, these module names will become the keys in the database, so the names assigned should be easy to use — for instance, Testing, Design, Development, Admin. These will be used again and again in various projects.


This is an area where many project managers and project leaders disagree. To some, resources are strictly people who’ll work on a project. To others, resources are anything with an associated cost that will impact the project, such as system time, facilities, software, and so on, as well as people. What determines a resource depends on how you want to track a project. If you’re simply tracking a project from a time standpoint, then tracking resources other than people is unnecessary. If, however, you want to track overall project costs, then things such as meeting rooms, system time, software tools, and so forth should be assigned a cost value per hour and tracked along with the people. Because of this disagreement, there’s a real risk for you to build an application that does one thing while your user had a completely different set of requirements.

For my application, I’m more interested in overall time spent, so I’ll just be tracking people. I might wish to assign a bill-out rate to each person, so that I can track manpower costs (and some of my overhead, such as system time, meeting rooms, and software tools can be built into the bill-out rate). Again, resources must be tracked as unique entities. One common way to identify people-type resources is to use initials. This is less prone to duplication than first names (or even last names), and where duplication occurs, some bending of the rules can take place (use or don’t use middle initials, or add a number, such as dgi1, dgi2, and so on).

Program specifications

Any software development project requires design and specifications. As is the case in many larger shops, in my shop the person doing the analysis and design isn’t the person who actually writes the code. As a result, some method of recording the specification is required. Specifications will be assigned to a project and a module, and may or may not be assigned to a specific piece of code. To be exact, a specification ties a particular task to a particular module and project.

It might sound surprising, but I’ll also require that training plans for users on the functionality of the system be considered a specification in the project. A training plan relates to a project and to a module, but doesn’t relate to a specific piece of code.

Code development

Obviously, in any software development project, one of the key activities is code development. This could be new program development, or it could be modifications to existing code. Regardless of the development task, you must ensure that the code is managed efficiently. The system must be able to identify where a piece of code is (I use three standard levels — work, test, and release). One important item to manage is that code is “checked out” when it’s being worked on, to ensure that one developer’s changes aren’t overwritten by another developer. Only one developer can own code at a time. Code can only be checked out from the “Released” or “Live” level, and can’t be checked out if a copy is in the “Work” or “Test” level. Code development is linked to a specification, and thereby linked to a project and module.

Release history

As code is checked in and out and released to the users, I want to keep a history of the modifications that have been made to the code. In our database environment, I’ll keep a history of what specifications relate to a specific piece of code, indicating what’s been done, by whom, and when. While this can be done as internal program documentation (and we’re all so good at documenting our code, aren’t we?), it’s nice to have a central repository in which this is stored. This will be linked to the code and to the specifications.

Resource time

Since I’m going to use this application to feed my project plans, I have to capture the time spent on specific tasks. I must be able to record time spent by a specific resource on a specific task for a specific project and module. A time sheet system allows me to link this information, as well as to provide a tracking mechanism for staff time. It’s not practical (or easy) to allow all developers to access the project plan in MS Project to enter the information directly, so this is a nice mechanism to keep the project up to date.

Finishing the requirements

Well, that’s the requirements document. This specification isn’t complete (it doesn’t include a list of input and output forms, for instance), but it will provide a basis for the next step. Going back to the recommendations in Mike’s article, I’d use Access as a prototyping tool to knock together some sample forms and reports. I’d then use those prototypes to further explore and finalize the requirements with the user. At this point, I’ve also developed the overall scope and structure of the project. However, I’d recognize that (as Mike points out) the requirements will change at a rate of about 1 percent per month. Since this is projected to be a 12-month series, I’d be smart to budget for an actual run of 13 months.

In next month’s issue, I’m going to look at modeling the data and go on to designing and developing the tables. From there, I’ll look at building the input and output forms and reports, and building the interface to Project. This is going to be a fun and informative trip, and it will provide you with some insight not only into Access development, but into project management and tracking as well. And as the series progresses and requirements change, you can expect me to come back and “enhance” this document, along with the application itself.

About David Irvine

Dave is an IT trainer and consultant who continues to teach Access and Visual Basic. He is principal consultant for IDS Solutions, and is the lead instructor and program facilitator for the Information Technology Professional program at Lambton College in Canada.
This entry was posted in Design and Tables and tagged . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *


This site uses Akismet to reduce spam. Learn how your comment data is processed.