Ms access Tutorial

MS Access Tutorial

• MGMT 683
• J. Rees

Introduction

• MS Access is a relational database management system (RDBMS)
• Other PC-based RDMBS include:
• MS FoxPro
• Borland dBase
• “Industrial-strength” RDBMS include
• Oracle
• MS SQL Server
• IBM DB2
• Informix

Getting Started

• Open MS Access (Under Microsoft Office 2003 heading)
• Open a New Database

• Select “Blank Database”

• Name your database MyShop.mdb

• Now click “Create.” Everything you create for this database will be saved as an *.mdb file.

MS Access Functionality

• Tables
• Store Data
• Queries
• Manipulate Data
• Forms
• Make data entry and display easier
• Reports
• Provides information from database to users in easy-to-understand format

Business Example

• Your sister has started her own home-décor shop. She sells items such as candles, pictures and knick-knacks. She wishes to track the items in her inventory, sales and customer information. You agree to assist her in setting up a simple MS Access database that will track the appropriate information.

Business Example, Continued

• You identify that three tables should be created:
• Product Table containing the product number, product name, product description, product price, and product on hand.
• Sale Table containing the sale number, sale date, the product sold and the customer.
• Customer Table containing the customer number, customer last name, customer first name, phone number, address and notes about customer preferences (notify if a Yankee Candle Company candle comes in, etc.)

• Creating Tables

• Double click ‘Create table in Design view’

• Creating the Product Table

• Enter the table information

• Next: Create the primary key and save your table

Creating Tables

• Create the Sales and Customer Tables
• Use AutoNumber for the Sales Number and Customer Number
• Remember to set the primary key and save your tables

Entering Data

• Enter the following data into each of your tables:

Entering Data, Continued

Relationships

• Select Tables to be Linked

• Link the three tables together

Queries

• Queries allow data to be manipulated
• What were the total sales today?
• How many of my customers come from out of town?

• Run this query
• Create query to determine customers who do not live in Lafayette

Forms

• Provide a user-friendly interface to the data.
• Control over user’s additions and modifications to the data.
• Forms (like queries & reports) do not contain data. Data is always saved in tables.

• Create a new form using wizard

• Create the Product Form

• Select fields to appear on your form

Reports

• Reports display the output of tables, forms, and/or queries in a formatted manner.
• Create reports for each query using the Report Wizard