Access Data Manipulation   «Prev  Next»
Lesson 2 Course project
Objective Describe the guidelines for the course project.

Describe Guidelines for the Course Project

Throughout this course you will have the opportunity to work on the course project. The course project is a database you will build yourself. We will help you out a little, you will not have to enter all the data (since you did that in Building a Database), but you will create queries, forms and reports. The course project is based on this scenario:
  1. You are a technical writer doing contract writing for the marketing and documentation departments of companies in the software and networking business. You have two large clients named
    • Network Consultants, Inc., which is a network consulting company, and
    • Dynamic Solutions, which is a startup software development company currently building a word processing application.
  2. You are juggling many projects and you find that you need to keep better track of your clients and the projects you do for them. After some research, you decide that the best way to address this problem is by building an Access database. In addition to tracking the work you do, the Access database will enable you to do analysis. For instance, you will be able to create a list of the work done in the last month. You will also be able to create a query and a report to invoice your customers.
To effectively manage your projects for Network Consultants, Inc., and Dynamic Solutions using Microsoft Access 2021, follow these steps:
  1. Database Design: Start by designing a relational database. Define tables for Clients, Projects, Tasks, and Deliverables. Ensure each table has a unique identifier (Primary Key).
  2. Clients Table: Include fields like ClientID, Name, Contact Information, and Industry. This will be your reference for client-specific details.
  3. Projects Table: Create a table to store project details. Include fields such as ProjectID, ClientID (foreign key), Project Name, Start Date, End Date, and Status.
  4. Tasks Table: This table should list individual tasks for each project. Include TaskID, ProjectID (foreign key), Description, Assigned To, Deadline, and Completion Status.
  5. Deliverables Table: Track deliverables with fields like DeliverableID, TaskID (foreign key), Description, Submission Date, and Approval Status.
  6. Relationships: Establish relationships between tables to maintain data integrity. For instance, link Clients to Projects, and Projects to Tasks.
  7. Data Entry Forms: Design user-friendly forms for data entry. This will streamline the process of adding and updating records.
  8. Queries for Analysis: Utilize Access’s query capabilities to analyze data. Create queries to track project progress, overdue tasks, client activity, etc.
  9. Reports: Develop reports for a structured view of your data. You can create weekly project status reports, client activity summaries, and more.
  10. Data Backup and Security: Regularly back up your database and implement security measures to protect sensitive information.

By following these steps, you will create a robust system in Access that not only helps you track your projects and clients but also provides valuable insights through data analysis.

Save Time Writing

Construct a Query that takes a Criterion

Problem: I need to construct a query that takes a criterion, but the criterion's value will not be known until the query is run. When it is time to run the query, the user needs a way to supply the criterion without going into the design of the query.
Solution: A query can be designed to accept parameters at the time it is run. Typically, an input box will appear in which the user enters the value for the criterion. A query can have any number of criteria entered in this fashion. A set of brackets defines the question asked in the input box. The brackets and the prompt to the user are placed in the Criteria row of the query grid for the given field. For example, using
[Enter an age]
as the criterion for a field instructs Access to present this prompt in a dialog box,
Discussion: When a query is run, a traditional form is often displayed to enable users to enter parameter values or make selections from a list. But the ability to place parameters directly in the structure of a query provides a great alternative to having to build a form that gathers input. When the criteria are simple, just using brackets in the query design will suffice. When the query is run, the dialog will appear, and the returned records will be filtered to those that match the entered value.