sandra access 2010 tutorial

Sandra Dyke, 2012 Access 2010 Tutorial

Microsoft Office

Access 2010

Tutorial

An Introduction to Access 2010

Sandra Dyke, 2012 Access 2010 Tutorial

Contents Page Introduction ........................................................................................................................... 1

Let’s Get Started ................................................................................................................... 2

Creating Tables ..................................................................................................................... 4

Creating Relationships and Enforcing Referential Integrity .................................................... 9

Adding Data ........................................................................................................................ 14

Creating Queries ................................................................................................................. 18

Reports ............................................................................................................................... 28

Forms ................................................................................................................................. 38

Sandra Dyke, 2012 1 Access 2010 Tutorial

Introduction This tutorial is an introduction to using Microsoft Access 2010. In this tutorial a database with 4 tables will be created, data added to these tables, queries created to obtain information from the database, and reports to display information from the database. The database being constructed is for a basic sales system and contains the following tables and attributes with the stated data types and sizes:

Table Attribute Data Type Size Required

Customer CustomerID AutoNumber Y

CustomerFirstName Text 50 Y

CustomerLastName Text 50 Y

CustomerAddress Text 50 Y

CustomerSuburb Text 50 N

CustomerCity Text 50 Y

CustomerPostCode Text 4 N

CustomerPhoneNumber Text 15 N

Inventory InventoryID AutoNumber Y

InventoryName Text 50 Y

InventoryDescription Text 255 N

Employee EmployeeID AutoNumber Y

EmployeeFirstName Text 50 Y

EmployeeLastName Text 50 Y

EmployeeExtension Text 4 N

Sale SaleID AutoNumber Y

CustomerID Number LongInteger Y

InventoryID Number LongInteger Y

EmployeeID Number LongInteger Y

SaleDate DateTime Y

SaleQuantity Number Integer Y

SaleUnitPrice Currency Y

Notice that the names of each attribute are typed with no spaces between the words, but each new word starts with a capital. This is called CamelCase. Don’t use spaces in field names because different database programmes treat these differently and it can also cause problems with creating queries later. Also notice that each attribute name has the table name in front of it. This is not always done, but is useful when the same field name may be used for different tables. For example, this database also has an Employee table where the employee name is included. By including the table name in front of the field name, it is easier to determine which field is being referred to when using queries and reports.


Let’s Get Started Open Microsoft Access and select Blank Database

Give the database a suitable name BasicSalesSystem and choose a folder to save it to by clicking the folder icon. Click Create

This creates the database and opens the default window.

Close this table without saving by clicking the X


The Navigation Pane

Choosing ‘Object Type’ option displays the objects under headings of table, queries, forms and reports. This is my preferred option.

The navigation pane can be collapsed and expanded by clicking the Shutter Bar Open/Close Button

Creating a Back up Go to File Save Database As


Creating Tables Steps for creating a Table

1. Click Create Table Design 2. Enter Field names, data types, and descriptions (optional) 3. Enter properties for fields 4. Set the primary key(s) 5. Save the table design (and name the table)

Creating the Customer Table Select the Create Tab and Click the Table Design button

This opens the Design view for the table

If you clicked Table instead of Table Design change the view from Data sheet view to Date Design view using the View option under the file tab or the icons at the bottom right of the screen


The first table to be created is for Customer In the first Field Name type CustomerID, change the data type to AutoNumber, add a description, and in the Field Properties section, change Indexed to ‘Yes (no duplicates)’ using the dropdown arrow

The CustomerID is going to be the Primary Key, so with the CustomerID field selected, click the Primary Key button on the ribbon. Notice the key which now appears next to CustomerID


Move down to the next row, and type CustomerFirstName in the field name cell, choose Text as the data type, add a suitable description.

In the Field Properties Box, change the Field Size to ‘50’, and Required to ‘Yes’

Now add the rest of the Attributes with the following properties

Table Attribute Data Type Size Required

Customer CustomerID AutoNumber Y

CustomerFirstName Text 50 Y

CustomerLastName Text 50 Y

CustomerAddress Text 50 Y

CustomerSuburb Text 50 N

CustomerCity Text 50 Y

CustomerPostCode Text 4 N

CustomerPhoneNumber Text 15 N


Save and name the table Customer (can save by closing the table or by using the save button) Then name your table

Now there is a table called Customer

Now create tables for: Inventory (primary key InventoryID)

Employee (primary key EmployeeID)


Sale (primary key SaleID)

Creating Joint Primary Keys Sometimes there is more than 1 field used for a primary key. Control click to select all of the fields required for the primary key (or click the first in the list and shift click the last in the list all the fields are consecutive and then click the primary key button. The key symbol should appear against all of the fields which are part of the primary key.


Creating Relationships and Enforcing Referential Integrity One of the most common types of database used for commercial purposes is the relational database. This type of database stores data in related tables, and if well designed, reduces the possibilities of data anomalies by storing a data item the minimum number of times required. This usually means a data item is only stored in one table in the database but can be accessed by other tables using the primary/foreign key link. To create the relationships between the tables requires a link to be created between the primary key in one table and the same data item in the other table.

Primary Key Foreign Key

Table Attribute Table Attribute

Customer CustomerID Sale CustomerID

Inventory InventoryID Sale InventoryID

Employee EmployeeID Sale EmployeeID

The Primary/Foreign key link allows any of the information in the primary key’s table to be accessed via the link. For example, because the Sale table has the CustomerID in it, a query run across the Sale and Customer tables will allow the customer name to be displayed with the details of the sale. More about this later. One of the advantages of the relational database design is to ensure that only valid data can be entered for the foreign key data items. This is done by enforcing referential integrity which means that before a value can be entered into the foreign key attribute, it must already exist in the table containing the primary key. This means when entering data, care has to be taken about the order the data is entered: first enter data in the primary key table and then enter the data in the foreign key table. For this tutorial database, data will need to be added to the Customer, Inventory and Employee tables before anything can be added to the Sale table. Click on Database Tools Relationships


Add all 4 tables in the ‘Show Table’ dialogue box

Rearrange and resize the tables so all attributes are shown

To create the relationship, click and drag the primary key attribute to the appropriate attribute in the related table: Click and Drag CustomerID from the Customer table to the CustomerID in the Sale table


This opens the Edit Relationships dialogue box. Tick Enforce Referential Integrity Notice that the Relationship Type is One-To-Many, This means one CustomerID could relate to many Sale records. Click Create Notice the link which has now been created between the customer table and the Sale table. The 1 at the Customer table end means 1 customer record could be related to many (∞) sale records.

Add the rest of the relationships.


Close the relationship diagram, saving it when requested to. Problems you may encounter

1. Line doesn’t show the 1 and many symbols. It is also thinner than the line you expect.

Solution

Right click on the link Select Edit Relationship Click Enforce Referential Integrity option


2. The following error message appears when OK is clicked

This means there is a mismatch between the data size or type for the fields selected. Solutions

Option 1: Incorrect attribute showing Check the Table and Related Table fields have the create attribute showing. If not, use the drop down arrows to select the correct attribute. The drop down arrow appears when the textbox is selected

Option 2: Incorrect data type or size for either the primary key or the foreign key Check the data type and size of the fields Note 1: Name of attributes don’t have to be the same (eg may have InventoryID and InventoryCode). As long as the data type and size are correct, this won’t cause a problem Note 2: The data type and size must be the same for both fields eg number and long integer, text and size 6. The only exception is where the primary key is an Autonumber – the foreign key field must be of type Number and Long Integer.


Adding Data Data can be added using the Datasheet View mode or by using a form (covered later). Data can also be imported from a spread sheet or copied from a spread sheet. If importing or copying from a spread sheet, it may be best to add the field properties after the data has been imported. Either way, once the data is imported and the field properties are set, only valid data can be entered. If field properties are set up before importing/coping the data, if there is invalid data in any of the fields imported/copied, none of the data will be imported/copied across. If the field properties are set up after the import/copying, the field properties changed will only be accepted if all of the data in that particular field is valid for the new field property entered. Open the Customer table in View mode (Datasheet View)

Click on CustomerFirstName and type Bill, tab to CustomerLastName and type Smith etc. The CustomerID does not need to be typed because it is an AutoNumber and is automatically included by Access. Type in the rest of the customer records. Do the same thing for the other tables. Make sure the data is added to the Customer, Inventory and Employee tables before adding data to the Sale table (ie add data to the Sale table last)


Table Attribute

Customer CustomerID CustomerFirstName CustomerLastName CustomerAddress CustomerSuburb CustomerCity CustomerPostCode CustomerPhoneNumber

1 Bill Smith 123 Dove Street Hillside Nelson 7055 03123456

2 Bob Brown 43 Sweet Road Riverside Wellington 6986 0274567898

3 Jane Green PO Box 780 Auckland 1436 04573953

4 Sue Cross PO Box 8249 Nelson 7073 03850345

5 Fred Brown 9 Castle Grove Picton 7125 0277684930

Inventory InventoryID InventoryName InventoryDescription

1 Hammer

2 Screwdriver Phillips

3 Saw

4 Spanner

5 Pliers

Employee EmployeeID EmployeeFirstName EmployeeLastName EmployeeExtension

1 Joy Smith 123

2 Jack Trigger 124

3 Gary Wood 125

4 Gail Smith

5 Lee Drake 126

Sale SaleID CustomerID InventoryID EmployeeID SaleDate SaleQuantity SaleUnitPrice

1 1 1 2 12/1/12 3 15.99

2 1 3 5 6/2/12 1 17.99

3 2 2 1 14/2/12 4 6.95

4 4 4 4 28/2/12 1 9.99

5 3 4 2 12/3/12 5 9.99


Customer Table

Inventory Table

Employee Table

Sale Table


Effect of enforcing referential integrity Remember the discussion about referential integrity? What happens if an incorrect value is included in the Sale table for CustomerID, InventoryID or EmployeeID? If the value included in one of the above fields isn’t in the appropriate primary table, Access will not let the record be added or changed in the table until it is fixed. Eg For SaleID 5 change the InventoryID to 6 and then either save the table or select a different record. The Inventory table does not contain an inventory item with an ID of 6 so the record cannot be changed. Two possibilities exist here – either ID 6 is incorrect or ID 6 needs to be added to the Inventory table. If the Inventory table needs to be updated, the record in the Sale table needs to have correct data added to it before the Inventory table can be corrected.

What is sufficient test data Where a database is being created by adding test data (or in the case of an assignment the instructions are to include ‘suitable data’), the amount of data needs to be sufficient to test any queries which are to be created later. For example, the data above includes the same customer with more than 1 sale (customer 1 has a sale ID of 1 and 2). Likewise, the same InventoryID appears in 2 different sales, as does the same EmployeeID.


Creating Queries Now that the data has been added to the tables, it can be used to produce results related to a set of conditions given to it. This is known as queries. Queries can be from one table or from a number of tables. Queries Select Create Query Design

Steps

1. Select the required table(s) 2. Select the fields required 3. Include any conditions 4. Select display requirements 5. Run Query 6. Save Query with a suitable name by closing it, or clicking the Save Button at the top

of the Access screen Important: When creating queries check that the results seem to be reasonable. When there is only a small dataset, look at the data and determine what the results should be before running the query and checking that the results obtained are the same.


Query 1: Single Table Query Create a Customer Phone List This query will produce a list of customer first name, last name and phone number. Select the Customer table from the Show Table Dialogue and click Add

Select the CustomerFirstName, CustomerLastName and CustomerPhoneNumber by using the dropdown arrow in the Field row, by dragging the attribute from the Customer table shown, or by double clicking the attribute in the Customer table shown.

Everything is going to be shown so nothing further is required.


Run the query by clicking Run

Result

Changing the views: Datasheet shows the results SQL shows the query written in the SQL language (covered in DAT501) Design shows the query using the GUI view we started the query with. Let’s sort this query by the customer’s last name. Change the query to Design View mode and select Ascending for CustomerLastName


Run the Query again

Now the results are sorted by the customer’s last name Close the query and give it a suitable name when prompted (eg qCustomerPhoneList). Query 2: Multi-table Query Create a list of customer names and the dates they have purchased items. This is a query across 2 tables: Customer and Sales Click Create Query Design Add Customer and Sales Notice that this time, the relationship between the tables is also shown (a customer may have many sales, but a sale can only relate to one customer)

Select CustomerFirstName, CustomerLastName from the Customer table and the SaleDate from the Sale table. Sort by the sale date.


Result

Save this query with a suitable name (eg qCustomerSaleDate). Both queries so far have returned all of the records from the tables selected. It is possible to select only some of the records by using the ‘Criteria’ section. Adjust the query by showing only Bill Smith’s sales by including ‘Bill’ in Customer FirstName and ‘Smith’ in CustomerLastName.

Result


Change the query to show only those sales which occurred in February 2012 This can be done by typing >= 1/2/12 And <= 29/2/12 in the SaleDate criteria. When the query is run, Access adds the # around each date.

Result

Query 3: Multi-table Query Create a query which shows the Employee Name, Date, Quantity, and Inventory Name for sales for each Employee. Sort by Employee Last Name This query is across 3 tables: Employee, Inventory and Sale.


Result

Save the query with a suitable name. Adjust the query to show only the sales which Gail Smith has made.

Result

Experiment with other conditions.


Query 4: Multi-table Query Create a query which shows the customer name, address, Inventory Name, Date, Quantity, Price, and Employee Name for each sale. Sort by customer last name.

Result

Save the query with a suitable name (qCustomerSale). Experiment with different conditions. Query 5: Adding Calculations The price shown in the Sale table represents the unit price for the items sold. It is possible to add the total price for the sale to the record by using a query. Values which can be calculated from the other data in the table are generally not included within the table due to the normalisation rules and to ensure an efficient database. Open query 4 (qCustomerSale) and rename it by going to File Save Object As (qCustomerSaleTotal).

The format to add a calculated attribute is –

Column Name: Table.Attribute * Table.Attribute In the first empty cell type

TotalSalePrice: Sale.SaleQuantity*Sale.SaleUnitPrice


Run the query Result

Once the query has been run, the calculation will show [ ] around the table and attribute names:


Query 6: Joining 2 text fields together A normalised database will usually have the customer name and address in more than 1 field eg CustomerFirstName,CustomerLastName, CustomerAddress, CustomerSuburb, CustomerCity and CustomerPostCode. However, when these attributes are used, usually the first and last name will need to be together as will the city and postcode. Because the length of text contained in these fields could be different lengths, it is easier to join the fields together as a new attribute and then use these new fields. Text fields can be joined together using the & operator and spaces can be introduced by using “ “ (double quote space double quote). Create a simple query using all of the fields from the Customer table and save it with a suitable name eg qCustomerDetails:

Switch into Design View In the first empty cell type: CustomerName: CustomerFirstName & “ “ & CustomerLastName

Run the Query Result

Try combining city and postcode into 1 field. To really see the effect of this joining text fields, create a report (complete the next section first) which shows customer addresses using the separates CustomerFirstName, CustomerLastName attributes as well as the joined attribute CustomerName and see the differences. This is a brief introduction to queries. There are lots of other types of queries which can be created. This will be left for your own research activity.


Reports Reports are used to view information in the database. They can be created using a wizard or manually and they can be modified to display the information in different ways. This tutorial will only use the Report Wizard and making modifications to this. Reports can be based on tables or on queries. Select Create Report Wizard

Follow the instructions in the wizard to select the table or query and the desired fields. The fields will be shown in the report in the order in which they are selected. Report 1: Customer Phone List From the Tables/Queries drop down list select the Customer table From the Available Fields list select CustomerFirstName CustomerLastName CustomerPhoneNumber

Click Next Ignore the grouping option at this stage Click Next


Sort the report first by CustomerLastName and then by CustomerFirstName

Click Next Select a report layout (this example will use the tabular layout) and Orientation (portrait) Give the report a suitable name (eg Customer Phone List)

Click Finish Preview the report.

When the preview is closed, the report is shown in the Design View Mode where alterations can be made to the formatting of the report.


Let’s have a look at the formatting of the report. The heading looks okay, but it would be better if it was centred. The column headings are the table attributes and are in CamelCase. They also include the word Customer in each one. A better format for the column headings would be to delete the word ‘Customer’ from each heading and to put a space between the words. In Design View, the report is broken into a number of sections. The report header and page header details are just headings and can easily be adjusted. The Detail section is linked to the database table and fields. These can also be modified by changing their placement and the size of the fields can be changed to display all of the data. The page footer shows the date (=Now()) and the page number.

There are 2 ways to centre the report header. First the textbox containing the heading can be moved and manually centred. Alternatively, the width of the textbox can be changed to the width of the page and then the text can be centred. Select the textbox

Click on the middle dot on the right of the textbox and drag it to the edge of the page

Click inside the textbox and then click the centre button (on the Format Tab).


Result

Adjusting the column headings Click inside the textbox, delete the word ‘Customer’ and put a space between ‘Last’ and ‘Name’

Do not change any of the text in the details section. If you do, the report will no longer display the required information in the report. The text in the Detail section refers to the field names in the database. Switch to Report View Mode


Report 2: Using a query The customer phone list report can also be created using the query created previously. Instead of using a table, select the query. Finish selecting options presented by the wizard and preview the report


Report 3: Creating an Employee Sales Report Using the Report Wizard, select the query qEmployeeSaleDetails and select all of the fields. This time, the report is going to group all of the sales by employee

The report will be formatted as shown above. Click Next Let’s group within each employee by the Inventory Item. Select InventoryName and click the > button


Click Next Sort by SaleDate

Let’s sum by the quantity for each inventory item. Click on Summary Options and select Sum


Click OK Click Next Select the Stepped layout and change the orientation to landscape Click Next Give the report a suitable title (eg Employee Sales Report) Click Finish Extracts from the report after changing the report to landscape mode

Now, the report is not the nicest. Switch to the Design View mode and modify the report format to look like the following:


It is possible to add new Page Headers and to modify the data displayed in the InventoryName Footer and EmployeeFirstName Footer. The Property Sheet is useful to add new headings so they have the same properties as the other headings.


Adjusted View


Forms A form can be used to view, add, edit and delete data. Forms can be formatted in a way which is useful for the user. The easiest way to create a data entry form is to use the Form button Select the table required Click Create Form Form 1: Creating a Customer Form First select the Customer table Then Click the Form button

This produces a form including all of the data entry fields.


Any related records in the Sale table are also shown, making it easy to add, edit or delete these records. Each Customer record can be displayed and new records added. Save the form as fSale Form 2: Sale entry At the moment, the fSale form could be used; however this is not very useful because the only data captured are ID numbers.

To improve this form, the Sale table could be modified to include lookup lists for customer, inventory, and employee to help the user to capture the correct information. Steps

1. Copy the Sale table and rename it (eg Sale2) – choose the structure and data option 2. Change the CusotmerID field to a lookup list (see below) 3. Do the same for InventoryID and EmployeeID 4. Create a form to input the sale data (fSale2) 5. Change the SaleID to a read only field because this is an auto number field (see

below) Change the CustomerID field to a lookup list: In design view Click on Lookup in the Field Properties section Change the properties as shown below.


The source is based on the Customer table, and the first 6 columns which are the CustomerID, CustomerFirstName, CustomerLastName, CustomerAddress, CustomerSuburb and CustomerCity. Basing this on the table is not the best design option because if the table structure changes (eg columns are added, removed, or reordered), the look up table will be inaccurate. Creating a customer query to include the required information is better. Try this yourself. Repeat for Inventory ID and Employee ID Create a form for this table To disable the SaleID in fSale2: Click on SaleID textbox Open the Property Sheet if it isn’t already open Select the Data tab Change Enabled to ‘No’ Form with lookup list

Customer Lookup list

NB If you use a form to update a table, the table needs to be closed for the update to be shown.

sandra access 2010 tutorial

Documents