Table of Contents

Access Summary of the steps to create a database

1. Design the database
a. What OUTPUT do you require
i. What input will be require to give you the output
2. Create the database
3. Design the tables
4. Create the tables
a. Use the Design View
b. Remember to name the table and the fields appropriately
5. Create the relationships between the tables – do this BEFORE you add any data to the tables.
a. Remember to enforce the referential integrity
6. Create the forms
a. Use the Wizard
7. Input the data
8. Create Queries
a. Use the Design View
9. Create Reports
a. Use the Wizard

Executive Summary

2.0 Introduction

Microsoft Access is an example of a relational database management system for desktop computers. The relational data model was created by Dr. E. F. Codd in 1970. Prior to this, querying a database had involved complex and lengthy programming. Codd’s model was a marvellous breakthrough allowing much greater ease and flexibility of use. It proposed a reliance on relationships between data values organised in tables of columns and rows, rather than a need to know how data is organised inside the computer (IBM News, 2003). In 1985, Codd published twelve rules defining the ideal relational database, two of which are: that all data should be presented in table form, and that all data should be accessible without ambiguity (Accela Communications, 2001). We can see the operation of these two rules in the system presented here.
Brunswick Specialty Foods is a new wholesale business supplying exclusive food and drinks to retailers in regional Victoria. The business provided details of its customers and products. Sales data from its first month’s trading were also available to be entered into the database and used to update tables and produce reports.

3.0 Progress of the Project

3.1 Database design in Microsoft Access

The first step was to create a new database (in the Task Pane – New – Blank database). In the subsequent development of the database a prime concern was data normalization: avoiding redundancy and duplication, so that the database would be as efficient and easily maintained as possible (Brady & Monk, 2005).

3.2 Create tables and enter the reference file data

In this step, we created two tables. The first table was saved as “tbl Cards” which included the following fields: CardID, CardName, CardSupplier, CardFaceValue, CardPurchasePrice and CardSalePrice. CardID was identified as the primary key because it’s a unique allocation to identify the card.

In this step, two tables were created. The first was the “Customers” table, with the fields Cust_ID, Cust_Name, Credit, Discount and Head_Office. The field Cust_ID was the primary key field. The format of Cust_Name was set as “@_@@” so that the code format would be automatically applied as the letters and number were entered; “Yes/No” was selected as the format of the Credit field as it would be most convenient for users. The length of text fields was changed to accommodate different text lengths. The table was saved as “Customers”, opened in datasheet view and the reference file data was entered into it, as shown in Figure 1.0.

3.5 Query Database
a. Sales by Customer
To obtain monthly sales by customer required establishing a relationship between the Customers Table and the Order Details Query. However, these have no field in common, so the Orders Table was also included in this query because it could link them, having the Cust_ID field in common with the Customers Table and the Order_ID field in common with the Order Details Query. Cust_Name was selected from the Customers Table and a field “Total Sales” was calculated from the Order Details Query using the expression “Total Sales: Total_Price”. The Totals button “Σ” was clicked and “Sum” selected for the Total