[icDL ‘The Digital Skis Standord ICDL Professional DATABASES Syllabus 6.0 = Learning Material (MS Access 2016) itl Role]‘Copyight ICOL Foundation 1997-2019, Reproducing, repurposing, or distrbuting this courseware without the permission of ICDL Foundation's prohibited ICDL Foundstion is registered business name of ECDL Foundation. ICDL Interrational Computer Driving Licence, ECDL, Europesn Computer Diving Leense and related logos are all egistered trademarks of ICDL Foundation This courseware may be used to sssist candidates to prepare forthe ICDL Foundstion Cectetion Procramme 3s titled on the courseware ICDL Faundaton does not warrant thatthe use ofthis courseware publication wil ensure passing ofthe tests for tht ICDL Foundation Certification Programme Any end all exsezement items andlor perlormanse based avercioes contained in this courseware relate eoaly to this publication and do na Consitute orimply certification by ICDL Foundation in respect ofthe ICDL Foundation Cerification Programme or any other ICDL Foundation test. Ths material does not constitute certfeation and does not lead to cerifcstion through any other prozess than oficial ICDL| Foundation certication testing ‘The cfcial coursenare content is written in English. ICDL Foundation shall not be responsible for any eors or omissions cortained in translated or other variations of this courseware In orc to take the offi tat forthe related |COL Foundation Cartfestion Programme Candidetas using this soursenare must be registered wih the ational Operstor. Winout a valid registration, the tes(s)canno! be undertaken and ro certitate, ror any other form of recognition, can be given toe candidate, Registration can be undertaken ai an Acrecited Test Centre. ICDL Datsbasesis an aternatie tte {or [GDL Using Databases {Screen shots used with permission from Microsoft ICDL Databases Databases are crucial tools for storing, managing, and manipulating data. ICDL Databases will help you understand what a database is and what it is used for. This module will also allow you to demonstrate a range of key skills relating to building and using a database On completion of this module you will be able to » Understand what a database is and how it is organised. * Create a simple database and view the database content in various modes. « Create a table, define and modify fields, and create relationships between tables. Enter and edit data in a table. + Use filters and queries to retrieve specific information from a database * Create a form to enter, modify and delete records and data in records. * Create routine reports and prepare outputs ready for print or electronic distribution What are the benefits of this module? This module covers the key functions and characteristics of databases and how to organise and operate them. Once you have developed the skills and knowledge set out in this book, you will be in a position to become certified in an international standard in this area - ICDL Databases. For details of the specific areas of the ICDL Databases syllabus covered in each section of this book, refer to the ICDL Databases syllabus map at the end of the book. How to use this book This book covers the entirety of the ICDL Databases course. It introduces important concepts and sets out the specific staps associated with using different features of the application. You will also have the opportunity to practice some of these activities yourself Page ii ICDL Detabases 2016 S6.0\V1 210717 ©2017using sample files provided in the Student Folder. It is recommended that you do not save your changes to sample files, as you may want to practice an activity more than once. Click here to download the Student Folder To extract the files once downloaded + Right-click on the zip file icon * Select Extract files... and choose a location * Click Ok A.useful way to open both the sample files and eBook is to re arrange your applications so the eBook opens on the right-hand screen of your screen (Press this icon EI) to view as a single page ) and the sample file opens on the left hand side. This will avoid changing between the two applications. ©2017ICDL DATABASES LESSON 1- EXPLORING ACCESS. 1.1 Working with Access. 1.2 Starting Access 1.3 Opening an Existing Database. 1.4 Familiarising with the Ribbon 1.5 Using the Contextual Tabs. 1.6 Using the Quick Access Toolbar 1,7 Understanding Database Objects 1.8 Using the Navigation Pane 1.9 Opening a Database Object 1.10 Deleting a Database Object 1.11 Using Tabbed Documents. 1.12 Closing a Tabbed Document. 1.13 Closing All Tabbed Documents 1.14 Switching between the View Modes 1.15 Using the Options Dialog Box 1.16 Closing a Database 1.17 Creating a New Database 1.18 Using Help 1.19 Exiting Access. 1.20 Review Exercise LESSON2- CREATING TABLE: 2.1 Using Database Templates 22 Assigning Data Types 23 Creating a Table 2.4 Adding Field Names. 25 Saving a New Table 2.6 Adding a Field Description 27 Setting a Primary Key 2.8 Saving Changes to a Table Page iv ©20172.9 Setting a Primary Key Automatically 3 2.10 Review Exercise B LESSON3- SETTING FIELD PROPERTIES... 34 Using Field Fropetties. 32 Limiting Field Size 36 3.3 Setting Number Formats. a7 3.4 Setting Date/Time Formats 39 35 Setting Yes/No Formats 40 36 Indexing a Field. 4 37 Setting Default Values. 42 38 Setting Validation Rules 44 39 Review Exercise 46 LESSON 4— WORKING WITH TABLES. 4.1 Using Field Templates 43 4.2 Adding Field to Existing Table 49 4.3 Adding Records £0 4.4 Moving through Records a1 45 Selecting Records. 53 46 Editing Records. 54 47 Printing from a Table. 54 48 Deleting Data in a Record 56 49 Deleting Records 56 4.10 Review Exercise 58 LESSON 5— EDITING TABLES 5.1 Changing the Row Height 60 52 Changing the Column Width ot 53 Changing a Font Attribute 63 54 Selecting a Column. 5.5 Moving a Column 56 Review Exercise LESSON 6 — FINDING AND FILTERING DATA . 6.1 Sorting Records 62 Finding Specific Records 69 63 Finding Records using Wildcards n ©2017 Page v64 Using Replace 2 65 Using Filter by Selection 74 66 Applying/Removing a Filter 5 67 Using Filter Excluding Selection 76 6.8 Using the Search Box 7 6.9 Using Quick Filter. 7 6.10 Using AutoFitter 79 6.11 Review Exercise a LESSON7- USING SIMPLE QUERIES 7.1 Using Queries and Recordsets Fc 7.2 Using the Simple Query Wizard Ee 7.3 Creating a Query in Design View 85 7.4 Saving a Query 26 75 Adding Criteria to a Query a 76 Saving Changes to a Query 88 7.7 Opening a Query. 9 78 Adding a Table toa Query 9 79 Running a Query. 1 7.10 Review Exercise 2 LESSON 8— MODIFYING QUERY RESULTS . 811 Sorting a Query Output 4 82 Hiding and Unhiding Field in Query 6 83 Adding and Removing Fields in Query 7 84 Printing a Query 7 8.5 Review Exercise 39 LESSON 8 - USING OPERATORS IN QUERIES. 9.1 Using Comparison Operators. 401 9.2 Using an AND Condition 103 93 Using an Or Condition 104 9.4 Using a Not Condition 105 95 Using the Between... And Operator 106 96 Using a Wildcard Character 107 97 Editing a Query 108 98 Review Exercise 110 Page vi ©2017LESSON 10- CREATING RELATIONSHIPS .. 10.1 Using Related Tables 112 10.2 Setting Referential Integrity 114 LESSON 11- CREATING BASIC FORMS. 11.4 Using Forms 116 11.2 Using the Form Button 117 11.3 Saving a Form. 118 411.4 Viewing Records in a Form 118 11.5 Adding a Record using a Form 119 11.6 Deleting a Record using a Form 124 11.7 Adding, Editing and Deleting Record Data using a Form 124 11.8 Using the Form Wizard 122 11.9 Printing Records in a Form. 124 11.10 Basing a Form on a Query 128 11.11 Review Exercise 128 LESSON 12- USING DESIGN VIEW. 129 12.1 Understanding Controls 120 12.2 Adding a Form Header and Footer Text using the Label Control 120 12.3 Saving Changes to a Form 132 12.4 Editing an Unbound Control 133 12.5 Using Multiple Undo/Redo in Design View 123 12.6 Review Exercise 135 LESSON 13- CREATING BASIC REPORTS.. 136 18.1 Using Reports 137 13.2 Using the Report Button. 137 13.3 Using Print Preview - Reports 139 13.4 Printing Pages of a Report 141 13.5 Using the Report Wizard 142 13.6 Changing Views in a Report. 145 13.7 Changing Control Arrangement of Data Fields in Report Layout 146 1.8 Saving Changes to a Report 147 13.9 Changing Orientation and Paper Size. 148 13.10 Grouping and Summarising Report Data. 149 1.11 Basing a Report on 2 Query 162 ©2017 Page vii13.12 Review Exercise 154 LESSON 14— USING ADVANCED REPORT DESIGN... 185 14.1 Adding Report Sections in Design View 156 14.2 Exporting Data to an Excel Workbook. 187 14.3 Exporting Data to a Text File 160 14.4 Exporting Data to a XML File 161 14.5 Review Exercise 163 ICDL SYLLABUS. Page viii ©2017LESSON 1-— EXPLORING ACCESS In this section, you will learn how to: © Work with Access © Start Access © Open an existing database * Familigrise with the Ribbon + Use the contextual tabs * Use the Quick Access Toolbar « Understand database objects © Use the Navigation Pane © Open a database object * Delete a database object « Use tabbed documents * Close a tabbed document + Close all tabbed documents «Switch between the view modes « Use the Options dialog box * Close a database * Create a new database © Use Help « Exit AccessLesson 1- Exploring Access ICDL Databases 1.1 WORKING WITH ACCESS @ Concepts A database is any collection of related data organised for fast search and retrieval For example, a telephone book 's a non-computerised database of information. Itis organised in alphabetical order and includes information such as names, addresses, and telephone numbers. Other examples of non-computerised databases include address books and inventory lists. Data is raw, unorganised facts and details. Examples of data that could be stored in a database are: + Astudent's test score * Anemployee’s ID » Avendor's email address. Information is the processed output of data. It provides context for data. Examples are * Average test score per subject chart * Mailing list sorted alphabetically and organised by location In Microsoft Access 2016. the database information is stored in data tables. Every data table has a structure that provides for the collection, organisation, storage, and retrieval of data. These tables of information are contained ina database file. Each database file can have numerous data tables va wo = isto =| Patient. vist ate |VistTime «Leng -| sit Ressor« we wa * Inks 27/200 —_s:00aM sidney Samveson, nancy Nelson >No —siriaoe> —seout pent tnnnn Record 3 Quinn 4/27/2003 1:00AM samuel smith Nathaniel nobet TTS AIT TOA aT — SRO SATE-NTGTAT RST Svinn _a/z7/a0ea 00am acald/riv__| sidney samueson, Nancy Nelson Garon sa/20ea_s20PM___rolowyp [sam smith Nathaniel Nobel Rese Ra erie woe | toma [See \ Table TS) Tables are made of fields and records Some of the common uses of large-scale databases are: * Social networks: Social networks utilise large databases to keep record of user profiles and corresponding contact and privacy details. They can be used to track the posts made by users, what they interact with and how long they spend online. Page 2 ©2017ICDL Databases Lesson 1 - Exploring Access * Airline booking systems: These systems maintain a database of all the seats on all the available flights, allowing passengers to be quickly booked onto fights to their destination. * Government records: Governments all over the world rely on multiple large databases in order to function. Tax records, criminal records and social security all require sophisticated database systems * Bank account records: Banks require extremely reliable databases in order to keep track of all their customer account and log transactions * Hospital patient details: Medical records are stored in a database system. This system should allow your medical history to be instantly available wherever you require treatment. Access is a database application that you can use to quickly and easily develop relational database applications that help you manage information. You can create a database to help you keep track of just about any kind of information, such as inventory, professional contacts, or business processes. In fact, Access comes with templates that you can use right away to track a variety of information, making things easy even for a beginner. Professional databases are designed and created by database specialists. These are highly skilled database professionals with an in-depth knowledge of exactly how the database works, such as its overall function. Database specialists design and create databases to meet the organisation's needs for present and future use For example, a database specialist could analyse the data processing and information needs of a computer training centre and designs a database that allows the storing of students, trainers and course details There are various roles that are assigned to maintain the database, such as 1) Database administrator a) Controls the access to different data for specific users b) Implements security measures to safeguard the organisation's database c) Has overalll responsibilty for the maintenance and repair of an organisation's database d) Recovers the database afier a crash or major errors. 2) Database user a) Enters, updates and retrieves data/information b) Granted access rights as needed for basic data entry and search. For example, a database administrator assigns various access permissions to data entry personnel, course administrators and managers for the database in the computer training centre. Data entry personnel may only enter and edit student ©2007 Page 3Lesson 1- Exploring Access ICDL Databases records but cannot access trainers or courses details. The database administrator may incorporate password protection to sensitive data in the database. A database user in a computer training centre can access the database based on the access level granted. Data entry personnel can only enter/edit/search/print records of students but cannot access trainer details. Managers may have a higher level of access such as being able to view/edit/search/print records and reports of students, trainers, and courses. 1.2 STARTING ACCESS Gg Concepts Access databases can help you store and track just about any kind of information, such as inventory, contacts, or business processes. You will learn how to create an Access database, add data to it, and then learn about the next steps towards customising and using your new database Steps To start Microsoft Access 2016 1. Select the Start icon in the taskbar. The Start menu appears Click 2. Point to the programs lst under Recently added Click scroll bar The scroll bar appears. 2. Select Access 2016 Microsoft Access 2016 opens. Click 1.3 OPENING AN EXISTING DATABASE G Concepts Databases residing in your computer or on a shared network drive can be opened by starting Microsoft Access and then selecting Open command from the File tab Page 4 ©2017ICDL Databases Steps Lesson 1 - Exploring Access Open Command To open an existing database from a specific drive and folder location: 1. Select the File tab Click File The Backstage view opens. 2. Select Open Click Open The Open dialog box opens. 3. Select Browse and navigate to the Click Browse Folder that contains the database. Browse to find the correct folder. 4. Navigate to the folder that contains the database. The Student Folder will open. Open the Student Folder a Select the name of the database you want to open. The file name is selected. Scroll as necessary and click on the file LibrarySystem.acedb 6. Select Open The Open dialog box and Getting Started task pane close, and the Security Warning message bar appears below the Ribbon, if applicable Click 7. Select the desired security options. The database content is enabled or remains disabled. Click Enable Content, if necessary Tip: You can use the keyboard shortcut Ctrl+0 to open a database project. ©2017 Page 5Lesson 1- Exploring Access ICDL Databases 1.4 FAMILIARISING WITH THE RIBBON @ Concepts The Ribbon is the long strio comprised of tabs with buttons across the top of the main window within the Access interface. The Ribbon is designed in a way that aids you in quickly finding commands that help complete a task, and contains almost all of the primary commands tor working with access. You no longer have to search commands endlessly through many menus and sub-menus. However, the File tab still exists, as does a single toolbar called the Quick Access Toolbar. All the other commands are now found in the various tabs of the Ribbon within the interface. oD a X cut Y Sl Ascending Ys Selection~ ia fe New R a Copy a Rl Descending T=] Advanced ~ an save @ FormatPainter | Bp RemoveSort WF ToggleFiter|| aye S< Delete ~ ei = Sort & Filter Records Click a tab Some groups Contextual tools to see tab is have an arrow appear when more divided ‘that you can certain objects commands | | into click for more ‘are selectad, groups options Paris of the Ribbon The Ribbon is designed to respond to your current task; however, you can choose to minimise the Ribbon if you find that it takes up too much screen space Click the arrow in the lower-right corner of the Ribbon to minimise it. As an altemative, you can also double-click on any tab heading to minimise the ribbon Click a tab to make the Ribbon reappear. It will disappear again when not in use To maximise the Ribbon, double-click on any tab heading to restore the Ribbon. Mirimised Ribbon Page 6 ©2017ICDL Databases Lesson 1 - Exploring Access 1.5 USING THE CONTEXTUAL TABS @ Concepts Contextual tabs appear on the Ribbon when an object requiring additional functionality is selected. For example, selecting a table in Access will display Table Tools contextual tabs on the Ribbon Be | a 12 GE Reet yy [Benton Te Boe Be | ae SP hens Os a Table Tools Contextual tbs Steps To use a contextual command tab: 1. Open the desired table from the Double-click Assets Table in the Navigation Pane LibrarySystem Database The desired table opens and the Table Tools contextual tab appears on the Ribbon 2. Select the desired contextual command | Click Fields. tab. The desired tab is selected. 1.6 USING THE QUICK ACCESS TOOLBAR @ Concepts By default, the Save, Undo, and Repeat/Redo buttons appear on the Quick Access Toolbar. If you regularly use a few buttons that are scattered on various tabs of the Ribbon and you do not want to switch between tabs to access the butions or crowd the Ribbon with a custom tab, you might want to add these frequently used buttons to the Quick Access Toolbar. They are then always visible in the upper-left corner of the program window. Clicking the Customize Quick Access Toolbar button at the right end of the Quick Access Toolbar displays a menu that lists commonly used commands. ©2017 Page 7Lesson 1- Exploring Access ICDL Databases Check marks appear to the left of commands currently available on the Quick Access Toolbar. You can click these commands to remove them, and click other commands to add them. Customize Quick Access Toolbar New Open mail Quick Print Spelling Y Unde v Redo Mode Refresh All ise Mode ‘ommends, Below the Ribbon ‘As you add buttons to the Quick Access Toolbar, it expands to accommodate them. If you add too many buttons, some of them might not be visible, which defeats the purpose of adding them. To resolve this problem, you can move the Quick Access Toolbar below the ribbon by clicking the Customize Quick Access Toolbar button, and then clicking Show below the Ribbon 1.7 UNDERSTANDING DATABASE OBJECTS Gg Concepts ‘An Access database can contain various types of database objects, including tables, queries, forms, reports, macros and modules. The following section briefly introduces the four main database objects. All database objects can be accessed from the Navigation Pane Tables: The basic building block of any database is a table. A database table is similar in appearance to a list or spreadsheet, in that the data is stored in rows and columns. A database can have many tables. A table should only contain records related to a particular subject. When tables contain data related to a single subject type, itis easier to search for and locate data. For example you may have a table called customers containing information about your customers with a separate table called orders Page 8 ©2017ICDL Databases Queries: Forms: Reports: Lesson 1 - Exploring Access You can then link the tables by creating a relationship, connecting each customer with their associated orders. Queries allow users to locate data from one or more tables according to specific search criteria. The data you want to retrieve may be stored in several tables, and a query allows you to view data from several tables in a single datasheet |n addition, because you generally do not want to see all the information at once, queries allow you select certain fields and add criteria to retrieve only specific records The result of a query is called a record set or result set. You can view the result set on the screen, print it, copy it to the clipboard, or use the output of the query as the record source for a form or report Forms are commonly used as data entry screens. They are user-friendly interfaces for working with data, and they often contain elements and command buttons that make entering data and performing various other tasks quick and easy You use reports to print and summarise data. Each report is formatted to present the information in the most readable way possible. You can format any report to fit your requirements and you can create custom reports as well 1.8 USING THE NAVIGATION PANE @ Concepts Navigation Pane is located at the left side of the Access window and helps you to work with database objects. Navigation Pane is organised by categories and groups. You can customise the categories and groups of objects in the Navigation Pane. You can select specific odjects from the Navigation Pane o27 Page 9Lesson 1- Exploring Access ICDL Databases Navgation ie Panel All Access Objects» «4}——ron" —_ Search. Seach Box roses a DPneE orien BD avemployee Detats Database BP ane 9 +] Objects BD qesupptier Details Bg supptiene + Forms 2 peniClose FB totiovestony sustorm eu BD teisupptiers L__I sepors , [id tptempioyee I ptsupoters td) The Navigation Pane You can change the width of the Navigation Pane by positioning the pointer over the right edge of the Navigation Pane. You can also hide or open the Navigation Pane by clicking the Shutter Bar Open/Close button All Access Objects ® earn. B Tables 2 Ed EE conaa: BE rites FE transactions Queries Basse BI coms andes SB cument Transaction: Foms * a I g L FB cheatin Closed Navigation Pane ‘Open Navigation Pane be lust lavigation Pane Steps To use the Navigation Pane to select an object type 4. Select the arrow in the Navigation Pane header Click [All Access Objects [>] The Navigation Pane menu appears Page 10 ©2017ICDL Databases Lesson 1 - Exploring Access 2. Under Filter by Group, select the Click Tables. desred object type The object type is selected, and the objects stored in it appear in the object list Practice the Concept: Click the Forms object type to display only forms. Then, click the All Access Objects in the program to recisplay all Access objects. 1.9 OPENING A DATABASE OBJECT G Concepts You can use any object in the Navigation Pane by double-clicking it. If you right-click an object, you notice a shortcut menu that lets you do various actions, such as opening an object in design view. All Access Objects © « secre. 2 ao @ Steps To open a database object If necessary, display the Tables object list in the Navigation Pane. ©2017 Page 11Lesson 1- Exploring Access ICDL Databases 1. Select the name of the object you want | Double-click Contaets, if necessary to open The object appears in the corresponding view, or the corresponding program runs. Click the Close button on the Contacts table window title bar to close the Contacts table. 1.10 DELETING A DATABASE OBJECT Gg Concepts You can delete any object (table, query, form, report) in the Navigation Pane by selecting it and pressing the [Delete] key. You can alse click the Delete button on the Home tab. Click Yes for the prompt. Libray System x 1 Do you want to permanently delete the form “Asset Details’? When you delete a table, you delete all the data in the table. You cannot undo the delete operation Practice the Concept Display the Forms object list. Select the Asset Details form object, if necessary, and then double-click it to display the form in Form view Close the Asset Details form window. Delete the Asset Details form. Display the Reports object list. Double-click the All Assets object to display it in print preview mode. Close the All Assets report window without saving changes, if prompted 1.11 USING TABBED DOCUMENTS Ga Concepts Each object that you open in an Access database opens in a new tab. This will allow you to quickly switch between each object. Ifneeded, you can dsable tabbed documents by setting your Access options Page 12 ©2017ICDL Databases Lesson 1 - Exploring Access Steps To view tabbed documents: If necessary, select All Access Objects in the Navigation Pane 1. Select the first desired table Double-click Assets table The desired table opens. | 2. Select the second desired table. Double-click Contacts table. The second desired table opens. 3. Select the desired query Double-click Assets with Transactions The desired query opens. query. 4. Select the second desired query. Double-click Contacts Extended query. The desired query opens. Notice that the tab title is displayed in bold type. 1.12 CLOSING A TABBED DOCUMENT G Concepts An opened tab can be closed by selecting Close from the shortcut menu displayed on the active database object. acs Extended [ES see (GE Contac (GS ares wah tansactone (Sc Deseription MK Design | (Bio Steps To close a tabbed document: Select the desired object tab. Right-click the Assets tab The shortcut menu appears. 2. Select the Close on the shortcut menu Click “8 Sloe The object tab closes. ©2017 Page 13Lesson 1- Exploring Access ICDL Databases If prompted, close without saving changes 1.13 CLOSING ALL TABBED DOCUMENTS G Concepts All the opened tabs can be closed all a time by selecting Close All from the shortcut menu NZ displayed on the active database object. View Til Aces (GE Contac (at assets with Transactions Steps To close all tabbed documents: Select the desired object tab. The shortcut menu appears. Right-click the Contacts tab | 2. Select Close Alll on the shortcut menu All open tabbed objects close. Click Mil Close al | Click No, if prompted to save the changes. Notice that all tabbed objects are closed 1.14 SWITCHING BETWEEN THE VIEW MODES EB concepts As you continue working with Access, you will find yourself switching between Design view and Datasheet view Click the View button on the Home tab to switch to the Design view of a table. query, form or report Page 14 ©2017ICDL Databases Lesson 1 - Exploring Access Click the View icon to return to Datasheet view (in tables and queries). EA View Steps To switch between views if necessary, select All Access Objects in the Navigation Pane. 1. Select the desired database object Double-click Assets table The desired object opens in datasheet view. 2. Select the desired view button on the vw Home tab. rh, View The view changes to design view Ole onthe Home:fap 3. Select another desired view button on the Home tab Ei Click “on the Home tab The view changes to datasheet view. You can also use the status bar to switch between different views. Design View Datasheet View View buttons on the status bar 1.15 USING THE OPTIONS DIALOG Box Gg Concepts Access allows you to configure several things on database start up. For instance. you can hide the Navigation Pane, set an Application Title, set an Application Icon, and several other things. These settings can be set using the Access Options dialog box ©2017 Page 15Lesson 1- Exploring Access ICDL Databases ‘Access Options » x a options for the current database orrent Database Datasheet ‘pplication Options Bronce “Access Options dialog box Steps To use the Access Options dialog box: 1. Select the File tab. Click File The view changes. 2. Select the Options command Click Options The Access Options dialog box opens 3. Select the option corresponding to the | Click the Current Database option features you want to change The appropriate page appears. 4. Select or deselect options as desired | Clickin Application Title text box under the Application Options group * Type Library Systems + Click Z Display status Bar to deselect it if necessary | 5. Select OK. Click LO Tho Access Options dialog box closes, and the options are enabled or disabled accordingly. 1.16 CLOSING A DATABASE @ Concepts Page 16 ©2017ICDL Databases Lesson 1 - Exploring Access An opened database can be closed by selecting the Close command from File tab, Close Command Steps To close a database: 1. Select the File tab Click File The Backstage view opens. 2. Select Close Click Close The database closes and the Getting ‘Started task pane appears. 1.17 CREATING A NEW DATABASE Gg Concepts You can create a database by building your own tables, forms, reports, and other database objects. Steps To create a new, blank database: ©2017 Page 17Lesson 1- Exploring Access Click the Blank database button on the New task pane. The Blank database dialog box appears. ICDL Databases N Click saan 2. Type the desired file name The text appears in the File name box. Type Inventory 3. Select the Save in folder icon. A list of available drives appears. Click = 4. Select the drive to store the file. A list of available folders appears Choose the drive with the Student Folder 5. Open the folder where you want to store the file A list of available folders and files appears. Double-click to open the Student Folder, if necessary 6. Select OK. The folder list closes. 17. Select Create The Getting Started task pane closes, and the database opens. Click U Create Click 8. If necessary, open the Navigation Pane The Navigation Pane opens. Open the Navigation Pane, if necessary Click No, if prompted to save the changes. Notice that all tabbed objects are closed Page 18 ©2017ICDL Databases Lesson 1 - Exploring Access 1.18 USING HELP @ Concepts You can use Access's extensive Help facility when you need assistance on any Access topic or task. You can search Access online Help sources to provide assistance Steps To access Help 1. Open the File tab. Click File 2. Select the ? button in the top rignt hand corner of the window. The Access help window opens 3. Type what to search for inthe search | Type primary key Click box The search text appears in the text box. 4. Click the Search icon D The list of related search topics appears. | Click 5. Click on the required topic link. Click the desired link Information regarding the selected topic appears. Close the Help window when done. Tip: You can access Help by hitting F1 on your keyboard 1.19 ExITING Access @ Concepts To exit Access, you need to close the Access application Steps To exit Access 4. Select the Exit Access bution EE Click Access application closes Notice that the Access application has closed ©2017 Page 19Lesson 1- Exploring Access ICDL Databases 1.20 REVIEW EXERCISE 8 Explore Access 1 2 3. 4. oN On Start Access, if necessary. Open BakeryDatabase.acedb Open the Navigation Pane, if necessary. Select the Queries, Forms, and Reports object types to view the objects in each; then. select Access All Objects Open all the tables. Hide the Navigation Pane. Use Close Alll to close the remaining open objects. Close the database Page 20 ©2017LESSON 2- CREATING TABLES In this section, you will learn how to: «Use database templates * Create a table in Datasheet view «Create a table in Design view * Add field names * Assign data types * Save anew table «Add a field description «Seta primary key ‘© Save changes to a table «Set a primary key automatically « Use multi-valued fieldsICDL Databases Lesson 2 - Creating Tables 2.1 USING DATABASE TEMPLATES @ Concepts Access provides various templates that can be used to speed up your database creation process. A template is a ready to use database that contains all of the tables, queries, forms, and reports needed for performing certain task. For example, there are templates that include databases for asset tracking, issue tracking, asset management, and project management. Some databases contain sample records to help demonstrate their use. Templates can be used as is, or you can customise them to suit your needs better. New arch ate 2 Suggeted seachex Databae Business Loge sts Personal bnventores Backstage view displaying the suggested search for New Database Templates Steps Use a template to create a new database. 1. On the Access start-up screen, scroll Click Database. down to see the featured templates. A list of database template is displayed and all the templates under the same category appear in the pane 2. Select the desired template from the displayed templates The database template is selected. Click Peductinventory ©2017 Page 23Lesson 2- Creating Tables ICDL Databases 3. Type the desired file name The text appears in the File name box. Type Product inventory 4. Select the Save in folder icon A list of available drives appears Click = 5. Select the drive where you wantto store the file. A list of available folders appears. Click the drive with the Student Folder, if necessary 6. Open the folder where you want to store the file A list of available folders and files appears. Double-click to open the Student Folder, if necessary 7. Select OK. The folder list closes. cick LT] 8. Select Create. The Local Templates task pane closes, and the database opens. 5 Greate Click 9. Open the Navigation Pane The Navigation Pane opens. Open the Navigation Pane, if necessary If necessary, it is a good idea to select Expand Alll from the right click menu in the Navigation Pane to display all the objects in the database. You can double-click any object in the Navigation Pane to open it. You can now enter data directly into the new database, or modify it to meet your needs. Close Product inventory.accdb 2.2 ASSIGNING DATA TYPES €3 con cepts The field data type tells Access what kind of values you plan to store in a field and how much storage space to set aside for the field. Although you can change a data type after a field contains data, doing so may erase some or all of the information in the field. The tblInventory table below in the Design view shows Field Name. Data Type and Properties: Page 24 ©2017ICDL Databases Bi timwentoy Fi Genera ookup| Fela see Fomst Input Mast cation Vaidston Tet requiea Indexes Tet ign a See caries Desciion tong Text Ene em Descptin category Sher Text Ene itemcategary Iocan Sher Text Ener Location Supple Nunber Ener Supple umber Manutatuee Shr Text Ener rafecturer Name Model Shert Text Enter Model Number feorde:tevel Number Enter Reorder evel Target Sock evel Number [elenter target stock evel Lesson 2 - Creating Tables ‘AutoNumber wentoryiD gets generated autom: (onatntaer fo General inveniory table in the Design View The field data type tells Access what kind of values you plan to store in a field and how much storage space to set aside for the field. Although you can change a data type after a field contains data, doing so may erase some or all of the information in the field The following table describes the available data types’ Short Text Text numbers, or both up to 255 characters. Long Text Text, numbers, or both up to 65,536 characters. Number Numbers used in calculations Large Number Non-monetary large values, compatible with SQL_BIGINT data type in OBDC. Used to efficiently calculate large numbers. ‘Date/Time Date times or both up to 8 bytes. Currency Currency values prevenis rounding “AutoNumber Unique sequential numbers automatically added to field Yes/No True/False On/Off OLE Object Documents created in other Office programs up to 1GB. Hyperlink | Hyperlink to a UNC path or URL up to 2048 characters Attachment | Stores one or more file attachments in one field Calculated Data created using a formula, calculated value can later be retrieved to use again Lookup Wizard Create a field that allows the choice of a value from another table Steps or list Assign data types to database fields ©2017 Page 25Lesson 2- Creating Tables ICDL Databases 4. Select the Data Type column in the field for which you want to change the data type The insertion point and a drop-down arrow appear in the corresponding Data Type column. Click in the Data Type column for the Description feld 2. Select the arrow A list of available data types appears Click 3. Select the desired data type The data type is selected and the field properties change in the lower pane. Click Long Text. Practice the Concept: Assign the following data types to the respective fields: * ID Supplier Origin Country Reorder Level Target Stock Level Unit Price Discontinued Discontinued Date AutoNumber Number Short Text Number Number Currency Yes/No Date/Time 2.3 CREATING A TABLE @ Concepts Tables are critical objects in a database because they hold all the information or data. For example, a database for a business can have a Contacts table that stores the names of their suppliers, their e-mail addresses, and telephone numbers. In Design view, you first create the structure of the new table. Then, you can either switch to Datasheet view to enter data or you can also use a form to enter data. Design view also allows you to specify the field name, data type and additional information for each field in the Description column Steps Create @ new table in Design view. Open a blank database. 1. Select the Create tab on the Ribbon The Create tab appears Click on the Create tab. Page 26 ©2017ICDL Databases Lesson 2 - Creating Tables 2. Select the Table Design button in the Tables group KE A blank table appears in Design view. Table Click Design 2.4 ADDING FIELD NAMES @ Concepts A data table consists of fields and records. Fields are categories of information. For example, in an address table, you may maintain names, addresses, cities, states, and zip codes. Each of these categories is a feeld in the address database. For each field in your table, you must give a meaningful name in the Field Name column Each field in a table should contain only one element of data. For instance, if you are storing a person's name, use one field for the first name and another field for the second name This means that at a later date you could manipulate the data to sort by the second name. For the same reason split the details of an address into as many smaller parts as possible so that later you could search by state/region, by post code’zip code or even by country if you are maintaining an international list. eldNeme D tem Description category Location supplier Manufacturer Model Reorder Level Target Stock Level ‘Adding Field Name Ps) Steps Add field names in Design view. Ifnecessary, create a new table in Design view and select the first blank cell in the Field Name column. 1. Type the desired field name Type ID. The text appears in the Field Name column of the current row. ©2017 Page 27Lesson 2- Creating Tables ICDL Databases 2. Press DOWN ARROW. Press DOWN 2. The insertion point moves down one row, and a default data typo Short Toxt appears in the Data Type column of the previous row. Practice the Concept: Type Item, Description, Category, Location, Supplier, Manufacturer, Mod: eorder Le Target Stock Level, and Unit Price as the next field names; pressing [Down] after each. 2.5 SAVING A NEW TABLE Steps Save a new table. 1. Select the Close Table icon in the top ee right-hand corer of the Table in Click Datasheet View. A save dialog box appears with options for closing the table. NR Select whether you wish to save the Click Yes table or not. The Save As dialog box appears 3. Enter the table name in the Table Type the desired table name Name: box. The new table name is entered into the box. 4. Save the table. Click OK The Save As dialog box closes and the new tabie is named. 2.6 ADDING A FIELD DESCRIPTION &3 Concepts You can use the Description column to provide information about individual table fields. The field Description is optional. it helps you describe the field and is also displayed in the status bar when you select the field on a form Page 28 ©2017ICDL Databases Lesson 2 - Creating Tables Steps Add a description to a field. Open the tblinventory table in Design view 1. Select the Description column in the Click in the Description column for the ID field to which you want to add a field description The insertion point appears in the corresponding Description column. 2. Type the desired description Type Inventory ID gets generated The text appears in the Description | #4tomatically. column. 3. Select the Description column in the _| Click in the Description column for the field fo which you want to add a Item field description The insertion point appears in the corresponding Description column. 4. Type the desired description Type Enter item Name. The text appears in the Description column. Practice the Concept: Type the following description for the respective fields. Description - Enter Item Description Category = Enter Item Category Location - Enter Location Supplier - Select Supplier Number Manufacturer - Enter Manufacturer Name Model - Enter Model Number Reorder Level - Enter Reorder Level Target Stock Level - Enter Target Stock Level Unit Price - Enter Unit Price 2.7 SETTING A PRIMARY KEY @ Concepts The primary key is a field in the table that uniquely identifies each record in the table. Examples of primary key include Order ID, Product ID, or Customer ID. There are several advantages to setting a primary key. First, the primary key is automatically indexed, which makes information retrieval faster. Second, when you open a table, the records are automatically sorted in order by the primary key. ©2007 Page 29Lesson 2- Creating Tables ICDL Databases Finally, a primary key prevents the entry of duplicate data because Access does not allow duplicates in the primary key field Be Rinse ows = = y ¢ ¥ 5 Bg 2 lina Re elias ingen ostieg ooops avecarcer Table Tools Design tab Data Type ‘AutoNumber TnventorylO gets [item Short Text Enter item Name | Description short Text Enter Item Description | category Short Text Enter Item Category | Location short Text Enter Location | supplier Number Enter Supplier Number | Manufacturer Short Text Enter Manufacturer Name | Modet short Text Enter Model Number |__ Reorder Level Number Enter Reorder Level | Target stock Level Number Enter Target Stock Level | unit Price Currency Enter Unit Price ID field chosen as primary key @ Steps Seta field as the primary key. 1. Select the field you want to designate | Click ID in the Field Name column. as the primary key The insertion point appears in the field. 2. Click the Primary Key bution in the Tools group on the Design tab on the ? ribbon . A key appears in the row selector for | cyicy | key the designated field and the field is set as the primary key. You can use an AutoNumber field to provide a unique value that's only purpose is to make each value unique. Page 30 ©2017