UNIT VIII STUDY GUIDE Working With Microsoft Access Course Learning Outcomes for Unit VIII Upon completion of this unit, students should be able to: 1. Explain the fundamentals of computer software. 2. Demonstrate fundamental techniques within a word-processing application. 3. Use fundamental desktop-publishing techniques. 4. Perform fundamental data-manipulation techniques. 4.1 Apply Microsoft Access 2016 skills in PC Pal. Course/Unit Learning Outcomes 1 2 3 4 4.1 Learning Activity Unit VIII PowerPoint Presentation Unit VIII PowerPoint Presentation Unit VIII PowerPoint Presentation Unit VIII PowerPoint Presentation Unit Lesson PC Pal Unit 1 Unit VIII Quiz 1 Reading Assignment Complete the following lessons for PC Pal Unit 1. Each lesson consists of videos, interactive exercises, and multiple-choice quizzes. (These are nongraded, but they are great practice for the interactive assessment at the end of each unit.) PC Pal Unit 1 Lesson 1.1 Touring the Interface Presentations 1.1.1 Touring the Interface 1.1.2 Tables and Records 1.1.3 Design View 1.1.4 Queries, Forms and Reports Interactive Exercises 1.1.1 Touring the Interface 1.1.2 Tables and Records 1.1.3 Design View 1.1.4 Queries, Forms and Reports Multiple Choice Quiz ITC 3001, Personal Computer Fundamentals 1 Lesson 1.2 Working With Tables Presentations 1.2.1 Data Entry, Application Parts & Primary Keys 1.2.2 More About Tables 1.2.3 Fields and Field Properties 1.2.4 Filtering and Sorting UNIT x STUDY GUIDE Title Interactive Exercises 1.2.1 Data Entry, Application Parts & Primary Keys 1.2.2 More About Tables 1.2.3 Fields and Field Properties 1.2.4 Filtering and Sorting Multiple Choice Quiz Lesson 1.3 Creating Forms and Importing Objects Presentations 1.3.1 Creating a Form 1.3.2 Importing Objects 1.3.3 Creating a Subdatasheet 1.3.4 Editing Relationships Interactive Exercises 1.3.1 Creating a Form 1.3.2 Importing Objects 1.3.3 Creating a Subdatasheet 1.3.4 Editing Relationships Multiple Choice Quiz After completing PC Pal Unit 1, complete the Unit VIII Quiz 1. Unit Lesson If you have registered for school, have a bank account, or have purchased an item online, then you have interacted with a database. The basis for information management in most businesses is a database. That is where all of the information needed to track and manage an organization is stored. In this section, you will be introduced to how databases are designed and created, possibly for the first time. This unit introduces you to Microsoft Access 2016, which is a relational database. This is the application in the Microsoft Office Suite that performs database management. Data is stored in a table within records or rows. These rows can be manipulated or displayed in numerous ways using the datasheet, form, and report designers. Forms can be designed and created to allow you to enter new records into your database, modify or delete existing records, or view data from database tables. Queries and reports allow you to display your data in meaningful ways. The strength of current databases is their ability to store information about the relationships between the different tables. These relational databases, such as those created in Access, SQL Server, and Oracle, allow for much more accurate and timely information. Information is only as reliable as the underlying data. Therefore, it is essential to maintain certain guidelines when designing your database. The more time you spend designing the database before, the less trouble you will have later with the data and reports. The first step in designing your database is to decide how you will use it. That overall decision will guide you in making the more detailed design decisions. Next, decide what objects you want to track within your database such as customers, products, and orders. These become the tables in your database. The columns of the table represent the attributes about the data that you want to track. For instance, the customer table might have columns such as name, address, city, state, zip code, phone number, industry, or contact person. ITC 3001, Personal Computer Fundamentals 2 Data is stored in rows within the database, with a value for each column. Some columns can be empty or null. UNIT x STUDY GUIDE Others require that a value is entered such as the primary key. Title The primary key is a value used to identify each individual row in the table. It must be unique within the table. For instance, you could use the customer name as the primary key, but there is a chance that would not be unique, and there might be two customers with the same name. For that reason, a customer identification would be assigned to the customer as the primary key. The primary key can be a number that is generated by the database, or it can be something more meaningful. Many databases use a person’s social security number (SSN) as the primary key to identify that person. Each column in the table must be assigned a data type. The main thing to remember about data types is that if you perform calculations on that column, then you would want to use the number data type. If you will not be performing calculations or if the column will contain a combination of numbers and text, the data type should be text. Common guidelines for designing a database are listed below. Include all necessary data. Store data in its smallest parts. Avoid calculated fields. Use data validation to ensure consistency. In addition to primary keys, the designer must also decide on foreign keys. A foreign key is just a primary key from one table that is stored in another table. Foreign keys are used to establish relationships between tables. An example would be linking customers to their orders. The primary key from the Customer table— Customer_ID—would be stored in each order in the Order table, thus linking the two. Three primary types of relationships exist in a database design: one:one, one:many, and many:many. The colon is pronounced as to when you say the relationship, so one:one would be pronounced as one to one. One:one relationships are rather rare. An example would be when a single employee is assigned to a single computer, where each employee is assigned no more than one computer, and each computer can be assigned to only one person. One:many relationships are more common. An example of this type of relationship is the customer/order relationship, where each individual customer can have many orders, and each order is associated with only one customer. Many:many relationships can be modeled in the design but must be converted to two one:many relationships to be implemented in the database. An example of a many:many relationship is the relationship between a student and the class. An individual student may register for many classes, and each individual class can have many students. These relationship types are enforced through the use of foreign keys. For instance, the Customer_ID is stored in the Order table with each order. A Customer_ID cannot exist in the Order table unless it first appears in the Customer table. In that way, the database prevents orders from being created for nonexistent customers. This concept is called referential integrity. It is used in relational databases to prevent orphan records. Do not worry if the database design concepts and terminology are difficult to grasp. Whole courses are devoted to database design and, even then, only provide an introduction. It takes a long time, many resources, and a lot of practice to become a database expert. Be sure to contact your instructor if you have any questions. After the design has been developed, turn your attention to implementing that design in Access. You will practice creating Access objects such as tables and reports. Tables and columns within those tables have many properties that must be defined in order to fully utilize the database. Access offers the ability to use templates to create the database. Several basic database structures are available. Third-party companies also offer Access database templates for a fee. These are more complex and often can be customized by the developer to fit your specific situation. Data can be sorted in the tables for easier viewing. You can sort in ascending or descending order. You can also sort by multiple fields, such as by state and then city in order for all of the cities within the state to be shown together. Filters are available as well. Filters can be used to display only those records that meet ITC 3001, Personal Computer Fundamentals 3 certain criteria. After you apply the filter, you can manipulate the data just as you would if the filter were UNIT x STUDY GUIDE not there. Title Many people confuse data and information. Data and information are not synonymous. Data refers to a fact or facts about a specific record such as a customer’s name, address, or items purchased. Information can be defined as data that has been rearranged into a more useful format. The individual elements related to a single business transaction are considered data. A report showing the sales totals by region is information that is generated from the data about individual transactions. What good is storing all of that data if you are unable to retrieve it in a meaningful format? The ease with which you are able to build queries and reports will depend on your database design. Queries are questions that you ask the database, and reports are the mechanisms to display the data. You are able to include many formatting features and calculated values in a report. Reports are objects within an Access database that are used to organize and display information. Reports can be created using a Wizard or by choosing all of the options yourself. Themes can also be applied to reports so that they appear more professional, without a lot of formatting work. Reports are used only to view data. They cannot be used to change data in the database. Each report has a source that is the underlying table(s) in the database that supplies the report with data. Each report is divided into several sections, allowing for more flexibility in adding totals and labels. The report header and report footer are only printed once with each report. These areas are where you would place items relevant to the report as a whole. The page header and page footer are printed on every page, so you would include items that should appear on every page, such as the report title or page numbers. The next level in the report format is the group, so there is a group header and a group footer. You might group your data by sales region or customer, and the group header and footer would print every time a group changed. The detail section contains the main information or body of the report. It contains the individual rows that are returned when you run a query against the database. Every report starts with a query, whether it is created by the Wizard or created manually. Additional information is available from Microsoft at https://support.office.com/en-US/Access, including Access training for current and older versions of the software (Microsoft, n.d.). Reference Microsoft. (n.d.). Welcome to Office help & training. Retrieved from https://support.office.com/en-US/Access Suggested Reading In order to access the following resource, click the link below. The following article provides great information about new database technology that is creating specialized databases for certain tasks or situations. McKendrick, J. (2014). Where new database technologies fit into the enterprise. Database Trends & Applications, 28(3), 42. Retrieved from http://go.galegroup.com.libraryresources.columbiasouthern.edu/ps/i.do?p=CDB&sw=w&u=oran95108 &v=2.1&it=r&id=GALE%7CA379981637&asid=1fa75015debf3fd48ead9d615cc87bb2 ITC 3001, Personal Computer Fundamentals 4
Purchase answer to see full attachment