NURS 6411: Information and Knowledge Management | Week 6

In the past several weeks, you have been introduced to the database software, Microsoft Access, and have the opportunity to explore its capabilities for creating tables and establishing relationships. To this point, the focus has been on creating the database. Now, you begin to explore how you get the specific information or data you need out of the database. Reflect for a moment on the types of information that you could use to help in your role as a nurse informaticist. Perhaps you need to know how many patients are being treated for a certain condition, or you may be interested in determining which children are behind on their immunizations. Microsoft Access has the capability to generate a wide range of reports. The possibilities are exciting!

This week, you continue to explore functionalities in Access by generating queries and reports. Also, Part 2 of your Team Database Project is due this week.

Learning Objectives – NURS 6411: Week 6: Fundamentals of Relational Databases in Microsoft Access Part 2 Assignment

Students will:

• Generate a suggestion for using Microsoft Access
• Develop a plan to design a Microsoft Access database

Learning Resources

Required Readings

Coronel, C. & Morris, S. (2017). Database systems: Design, implementation, and management (12th ed.). Boston, MA: Cengage Learning.

• Chapter 7, “Introduction to Structured Query Language (SQL)” (pp.246-305)This chapter introduces structured query language and discusses how it is instrumental in performing efficient data extraction and management.

Cox, J. & Lambert, J. (2013). Step by step: Microsoft Access 2013. Richland, WA: Microsoft Press.

• Chapter 5, “Create Simple Reports” (pp. 135–156)Chapter 5 explores the creation of simple reports, using a Microsoft Access wizard. The authors then demonstrate how to preview, modify, and print the reports.>
• Chapter 7, “Create Queries” (pp. 197–226)In this chapter, the authors explore the concept of queries, which are used to filter through information to identify relevant data. The authors examine two types of queries; select and action. They then demonstrate how to create queries, calculate and update records using queries, and finally, how to delete records.
• Chapter 8, “Create Custom Forms” (pp. 227–254)This chapter focuses on creating custom forms to meet unique needs. The authors review the three main sections for forms, and then demonstrate how to customize each of these sections.

Optional Resources

There are a wide variety of online tutorials available to assist with using Microsoft Access. Below are several you might consider, as needed:

Goodwill Community Foundation, Inc. (2010). Access 2010. Retrieved from http://www.gcflearnfree.org/office

This free resource provides a series of lessons, interactives, and video tutorials to assist in learning how to use Access 2010.

Pollock, A. (2010). Access 2010 essential training. Retrieved from http://www.lynda.com/Access-2010-tutorials/essential-training/62642-2.html

This series of videos provides detailed instruction on all aspects of Microsoft Access 2010 use. This resource requires a paid subscription.

Discussion: Microsoft Access Queries and Reports

As you have begun to explore Microsoft Access these past several weeks, you may have encountered challenges with using the software. In these cases, it can be beneficial to share your experiences with colleagues and exchange tips and tricks for using Access.

In this Discussion, you consider your work with Access so far and reflect on the areas that were difficult for you. You also consider areas where you could provide useful advice for others.

To prepare:

• Review the steps of planning and creating a database using Microsoft Access.
• Identify an area or step in Access that you have found to be confusing and determine what you could ask a colleague to help clarify the process.
• Consider an aspect of creating a database, query, or report using Microsoft Access that is easy for you, or a solution to a problem you found on your own. With this in mind, develop a tip for using Access that can assist your colleagues.

By Day 3

Post your question about using Microsoft Access. Describe your challenges and success with creating tables, designing queries, or generating reports in Access, including what you found easy and what areas were the most challenging.

Read a selection of your colleagues’ responses.

By Day 6

Respond to at least two of your colleagues on two different days addressing those questions raised to which you can provide input or assistance. Fundamentals of Relational Databases in Microsoft Access Part 2 Assignment

ADDITIONAL INFORMATION;

Fundamentals of Relational Databases in Microsoft Access

Introduction

Microsoft Access is a powerful database management application that allows users to create, edit, and maintain databases. In this article, we’ll look at the basics of relational databases in Microsoft Access. We’ll discuss how to create tables with columns and rows; add data; modify relationships; and use other features of this program to efficiently manage your databases.

Introduction

Relational databases are the most popular type of database. They store data in tables and use SQL statements to manipulate the data. Microsoft Access is one of the most popular and powerful relational databases available today, so it’s easy to see why we’d want to learn about it here!

Microsoft Access has many benefits over other types of databases:

• It’s easy to learn, even if you’ve never used a computer before (which I hope this course will help with).

• You can easily share your files with others by saving them as templates or queries which means they’ll be accessible not just on your computer but also in other locations around the world!

Create a Database

Access is a relational database management system (RDBMS) that uses a relational model to store and retrieve data. The RDBMS stores data in tables, which are collections of similar rows with related fields defined by the user. Each table contains one or more columns, each of which can have different types of information associated with it. For example, you might have one column for your employee’s name and another column for his department number.

Each row in a table relates directly to other rows. For example, if you want to list all employees who work at your company’s headquarters location along with their salaries per month:

• List all employees who work at headquarters location (including their salary)

Add and Delete a Table

To add a table to your database, follow these steps:

• Click the File tab, select New and then Tables.

• Enter the name of the new table in the Name field and click OK to create it (if you don’t want to enter any information at this point, click Cancel).

• Click on Databases in the left pane and select your newly created table from there (or simply double-click on it). The Data window shows all data stored in that particular field range or column: For example, if we enter * as our search criteria then all records where this string appears anywhere within our specified range will be displayed here – but remember not every value needs an entry in order for us to see what’s going on!

Build Relationships

Relationships are the links between tables. A relationship is defined by having a field in one table that matches a field in another table. Relationships are used to link data together and create a relational database.

There are three types of relationships: One-to-One (1:1), Many-to-One (M:1) and Many-to-Many (M:M).

One-to-One (1:1) A 1:1 relationship is when one record in a table can only be linked to one other record in another table. For example, an individual customer can only have one phone number. If you were to add a second phone number field to the customer table, an error would occur because there would be no way of knowing which phone number belongs to which customer.

Create a Subdatasheet

A subdatasheet is a table that is linked to another table. You can have multiple subdatasheets in a single table, and they’re useful for creating summary tables.

For example, you may want to create an overall sales report that includes each product sold by month and year as well as its total revenue for each year (see Figure 1). This would require four separate tables: one for each product type sold; one for total revenue by time period; and finally, one for total revenue by product type over time period.

The first step is creating a new worksheet (or tabular report) called “Sales Summary.” Selecting this option will open up your Access program window the same place where you entered data earlier and prompt you with questions about what types of information should be displayed on this particular sheet(s). Once satisfied with your choices here (#1-#6), click OK!

Set Table Primary Keys

The primary key of a table is used to uniquely identify each row in the table. Primary keys are used to enforce referential integrity, which means that every record must have an unique identifier. In other words, if you add a new employee to your database, you’ll want their name and social security number on its own row so they can’t be confused with anyone else’s data.

The only requirement for choosing what type of data will be stored in your primary keys is that they must be unique (not null) and non-nullable you may not use NULL as your primary key!

You can set up any type of information as a primary key: it doesn’t matter whether it’s text or numbers; just make sure there aren’t duplicate records in any column where this information appears!

Create Referential Integrity

Referential integrity is an important concept in relational databases. It ensures that the data in your database is consistent and complete, so you can be sure that all of the information has been entered correctly. For example, if you have a table called Employees with three columns: FirstName, LastName, and Salary (first name, last name, salary), then each employee record must include all three columns they cannot be missing any values or inconsistent with other records on their respective rows in this table. This concept is also referred to as referential integrity because it refers back to how certain values should relate to each other within a database structure such as tables or views.

Change the Data Type of a Field

Change the Data Type of a Field

You can change the data type of a field by clicking on it and selecting from the Tools menu or choosing Command + T (for Mac users). For example, if you want to make your field an Integer, select it and then click on Advanced in the context menu that appears. In this window you will see an option called “Data Type” along with several other options like Currency etc., which are all related to different types of data that can be stored in your database table. To change the default value for this field use one of these options:

• Leave as Text: This is just like leaving text alone except for one thing – when editing this value later on it won’t save automatically even though I told Access my intention was clear enough (i.e., save everything) but still had no clue what would happen next until I read through some documentation online!

Creating Relational Databases in Microsoft Access

Microsoft Access is a database management system that can be used to create relational databases. It is ideal for small businesses, as it allows users to manage their data and access it from anywhere through the internet.

Access can be used to create relational databases by creating tables, adding fields and relationships between the tables.

Conclusion

To summarize, the fundamentals of relational databases are:

The concept of a table is fundamental to relational databases. The data in a table is organized in rows and columns. Each row contains one or more attributes; each column has a name (column attribute) and a type (datatype). A query can be performed on multiple tables at once. Relationships between tables can exist both to create combined entities (where data from multiple tables is joined together) as well as hierarchically within individual tables so that each entry has at least one parent and child relationship with other entries (self-referencing). As Microsoft Access offers many features that support this form of database management, it makes for an ideal tool for implementing such functionality using SQL statements within Visual Basic code blocks.

There are many benefits to using Access as opposed to other tools like MySQL because it allows you access any database from any computer on your network without having to install software onto each device individually which would be very costly! You can also customize certain aspects about the user interface by changing colors etc… I hope this article has helped give some insight into why people use this product versus others like MySQL if they want something more secure or flexible than what’s available through standard SQL servers out there today.”