Introduction to Microsoft Access Databases, Lecture notes of Computer Fundamentals

Download Introduction to Microsoft Access Databases and more Lecture notes Computer Fundamentals in PDF only on Docsity!

MTHATHA CAMPUS – NMD

ACCESS NOTES

MODULE CODE: CLT15M2/CLT12M

Table of Contents

• INTRODUCTION TO DATABASES
  • What is a database?
  • Why use a database?
• GETTING STARTED WITH MICROSOFT ACCESS
  • To create a new Database...................................................................................................................
  • Getting to know Access 2016/2019
    • Working with your Access environment
    • The Ribbon
    • The Quick Access toolbar
    • The Navigation pane
• INTRODUCTION TO OBJECTS.................................................................................................................
  • TABLES...............................................................................................................................................
  • DATATYPES
  • TO CREATE TABLES
    • Understanding table views
    • Creating a Table in Design View
  • Primary Key
  • Sorting records in a Table
  • Field Properties
• RELATIONSHIPS, FORMS, QUERIES, AND REPORTS
  • TABLE RELATIONSHIPS
  • FORMS...............................................................................................................................................
    • To create a form:...........................................................................................................................
  • QUERIES
    • Designing a Simple Query
    • Designing a multi-table query
  • REPORTS
    • To create a report:
    • Deleting fields
    • Printing and saving reports in Print Preview.................................................................................
• PUTTING IT ALL TOGETHER
• REFERENCES

However, if you were a professional baker, you would have many more lists to keep track of: a list of customers, a list of products sold, a list of prices, a list of orders...and so on. The more lists you add, the more complex the database will be. In Access, lists are a little more complex than the ones you write on paper. Access stores its lists of data in tables , which allow you to store even more detailed information. In the table below, the “People” list in the amateur baker’s database has been expanded to include other relevant information about the baker’s friends.

If you are familiar with other programs in the Microsoft Office suite, this might remind you of Excel, which allows you to organize data in a similar way. And in fact, you could build a similar table in Excel.

Why use a database?

If a database is essentially a collection of lists stored in tables and you can build tables in Excel, then why do you need a real database in the first place? While Excel is great at storing and organizing numbers, Access is far stronger at handling non-numerical data , like names and descriptions. Non-numerical data plays a significant role in almost any database, and it's important to be able to sort and analyze it. However, the thing that really sets databases apart from any other way of storing data is connectivity. We call a database like the ones you’ll work with in Access a relational database. A relational database is able to understand how lists and the objects within them relate to one another. To explore this idea, let's go back to the simple database with two lists: names of your friends, and the types of cookies you know how to make. You decide to create a third list to keep track of the batches of cookies you make and who they’re for. Since you're only making cookies you know the recipe for and you're only going to give them to your friends, this new list will get all of its information from the lists you made earlier.

Working with your Access environment Microsoft Access uses features like the Ribbon and the Quick Access toolbar , where you will find commands to perform common tasks in Access, as well as Backstage view. The Ribbon Access 2016/2019 uses a tabbed Ribbon system instead of traditional menus. The Ribbon contains multiple tabs , each with several groups of commands. You will use these tabs to perform the most common tasks in Access. To minimize and maximize the Ribbon: The Ribbon is designed to respond to your current task, but you can choose to minimize the Ribbon if you find that it takes up too much screen space.

1. Click the arrow in the lower-right corner of the Ribbon to minimize it.
2. The Ribbon will be minimized. Click a tab to make the Ribbon reappear. It will disappear again when not in use.

3. To maximize the Ribbon, click a tab, then click the Pin icon in the lower-right corner. The Ribbon will appear at all times. The Quick Access toolbar The Quick Access toolbar is located above the Ribbon, and it lets you access common commands no matter which tab you are on. By default, it shows the Save , Undo , and Repeat commands. If you'd like, you can customize it by adding additional commands. The Navigation pane The Navigation pane is a list containing every object in your database. For easier viewing, the objects are organized into groups by type. You can open , rename , and delete objects using the Navigation pane.

By default, objects are sorted by type , with tables in one group, forms in another, and so on. However, if you wish, you can sort the objects in the Navigation Pane into groups of your choosing. There are four sort options:  Custom allows you to create a custom group for sorting objects. After applying the sort, simply drag the desired objects to the new group.  Object Type groups objects by type. This is the default setting.  Tables and Related Views groups forms, queries, and reports together with the tables they refer to.  Created Date or Modified Date sorts objects based on when they were created or last edited. To sort objects in the Navigation pane:

1. Click the drop-down arrow to the right of All Access Objects , then select the desired sort from the drop-down menu.
2. The objects in the Navigation pane will now be sorted to reflect your choice.

INTRODUCTION TO OBJECTS

Databases in Access are composed of four objects : TABLES , QUERIES , FORMS , and REPORTS. Together, these objects allow you to enter, store, analyze, and compile your data as you wish. In this lesson, you will learn about each of the four objects and come to understand how they interact with each other to create a fully functional relational database.

TABLES

A table is a collection of related data organized into many connected lists within a database. In Access, all data is stored in tables , which puts tables at the heart of any database. It is a structure that organises data into horizontal rows and vertical columns - forming a grid. Tables are similar to a worksheets in spreadsheet. In Access, rows are referred to as records and columns as fields. A field is more than just a column—it’s a way of organizing information by the type of data it is. Every piece of information within a field is of the same type. For example, every entry in a field called “First Name” would be a name, and every entry in field called “Street Address” would be an address.

Tables are good for storing closely related information. Let's say you own a bakery and have a database that includes a table with your customers' names and information like their phone numbers, home addresses, and email addresses. Since these pieces of information are all details about your customers, you’d include them all in the same table. Each customer would be represented by a unique record , and each type of information about those customers would be stored in its own field. If you decided to add any more information—say, a customer's birthday—you would simply create a new field within the same table.

DATATYPES

A data type must be assigned to each field in a table. The data type of a field determines what kind of data can be entered and stored in a field. It also determines what can be done with the field e.g. a number data type must be used if the field will be used in calculations. When you design a database in Access 2013, you decide what type each field will be. Access provides 12 field/data types for you to choose among. Choose the field/data type that best describes the data you want to store in the field and that works for the type of analysis you need to use the field. Here are tips on when to use which type of field. Field Type/Data Type What It Holds / its use Short Text Text up to 255 characters long (including spaces and punctuation). Use a Text field, not a Number field, for codes even if they look like numbers, such as phone numbers, zip codes, and other postal codes. Long Text Text up to 65,536 characters.

Number Only numbers. You may use + or – before the number, as well as a decimal point. If you plan to do math with a field, use a Number or Currency field. Currency Numbers with a currency sign in front of them ($, ¥, R, € and so on). AutoNumber Numbers unique to each record and assigned by Access as you add records, starting at 1. Use an AutoNumber field as the primary key field for most tables. Date/Time Dates, times, or both. Hyperlink Text string formatted as a hyperlink. (If you click the link, it takes you to the page.) This field type is especially useful if related information is available on the web. Yes/No Yes or no (a particular condition is, or isn’t, in effect) — or other two-word sets, such as True/False, On/Off, and Male/Female. Use a Yes/No field if you want to display the field as a check box on forms. Attachment Stores one or more entire files — pictures, sound, Word documents, even video — in one Attachment field. Calculated Data created with a formula. Use a Calculated field when a calculated value will be used in many queries, forms, and reports.

TO CREATE TABLES

It is recommended to create the table structure in Design view rather than datasheet view, as field properties cannot be altered in datasheet view. Understanding table views There are multiple ways to view a database object. The two views for tables are Design View and Datasheet View.

 Orders could be sorted by order date or by the last name of the customers who placed the orders.  Customers could be sorted by name or by the city or zip code where they live.  Products could be sorted by name , category (like pies, cakes, and cupcakes), or price. You can sort both text and numbers in two ways: in ascending order and descending order. Ascending means going up , so an ascending sort will arrange numbers from smallest to largest and text from A to Z. Descending means going down , or largest to smallest for numbers and Z to A for text. The default ID number sort that appears in your tables is an ascending sort, which is why the lowest ID numbers appear first. To sort records:

1. Select a field you want to sort by. In this example, we will sort by customers' last names.
2. Click the Home tab on the Ribbon, and locate the Sort & Filter group.
3. Sort the field by selecting the Ascending or Descending command. o Select Ascending to sort text A to Z or to sort numbers from smallest to largest. We will select this in our example because we want the last names to be in A-to-Z order. o Select Descending to sort text Z to A or to sort numbers from largest to smallest.
4. The table will now be sorted by the selected field.

5. To save the new sort, click the Save command on the Quick Access toolbar. After you save the sort, the records will stay sorted this way until you perform another sort or remove the current one. To remove a sort, click the Remove Sort command.

Field Properties

When working with data in tables, you may encounter situations that require the data be restricted or adhere to some default specifications in particular columns. You can define these requirements by using the field properties. The field properties affect how the data is stored and presented, among other things. The list of field properties that are available to you is dependent on the data type chosen for that field. Some field properties are specific to Text fields, and others are specific to Number fields. The field properties can be found in the Design view. As you click each field, you will see the field properties for that field. Some of the most important field properties to note are: Field Size: It enables you to set a maximum size limit on data entered in that column. For the Text data type, size refers to the length (number of characters and spaces) of the Text data in that column. Text fields: The maximum number of characters (up to 255) that can be entered in the field. The default setting is 50. Number / Currency fields: Stores the number as a Byte, Integer, Long Integer, Single, Double, or Replication ID. The default setting is Long Integer.

RELATIONSHIPS, FORMS, QUERIES, AND REPORTS

Although tables store all of your data, the other three objects offer you ways to work with it. These objects are forms , queries , and reports. Each of these objects interacts with the records stored in your database's tables.

TABLE RELATIONSHIPS

Although each table stores data about a different subject, tables in an Access database usually store data about subjects that are related to each other. For example, a database might contain:  A customers table that lists your company’s customers and their addresses.  A products table that lists the products that you sell, including prices and pictures for each item.  An orders table that tracks customer orders. Because you store data about different subjects in separate tables, you need some way to tie the data together so that you can easily combine related data from those separate tables. To connect the data stored in different tables, you create relationships. A relationship is a logical connection between two tables that specifies fields that the tables have in common.

FORMS

Forms are used for entering , modifying , and viewing records. You likely have had to fill out forms on many occasions, like when visiting a doctor's office, applying for a job, or registering for school. The reason forms are used so often is that they're an easy way to guide people into entering data correctly. When you enter information into a form in Access, that data goes exactly where the database designer wants it to go—in one or more related tables.

Forms make entering data easier. Working with extensive tables can be confusing, and when you have connected tables you might need to work with more than one at a time to enter a set of data. However, with forms it's possible to enter data into multiple tables at once, all in one place. Database designers can even set restrictions on individual form components to ensure all of the needed data is entered in the correct format. All in all, forms help keep data consistent and well organized, which is essential for an accurate and powerful database. In this lesson, you will learn how to create and modify forms. You'll also learn how to use form options like design controls and form properties to make sure your form works exactly the way you want. To create a form: Access makes it easy to create a form from any table in your database. Any form you create from a table will let you view the data that's already in that table and add new data. Once you've created a form, you can modify it by adding additional fields and design controls like combo boxes.

1. In the Navigation pane, select the table you want to use to create a form. You do not need to open the table.
2. Select the Create tab, locate the Forms group, and click the Form command.