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 NURS6411

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. Discussion: Microsoft Access Queries and Reports NURS6411.

ADDITIONAL INFORMATION;

Fundamentals of Relational Databases in Microsoft Access

Introduction

Microsoft Access is a database program that allows you to store and manage data. It’s very easy to use, and it’s great for simply storing basic information about your business or family as well as more complex data such as financial information.

Access stores relational database data in tables.

Access stores relational database data in tables. A table is the container for data and can contain rows or columns. Rows are records, and each record contains one or more fields that represent different pieces of information about an entity (a person, a product or service). The most common example of tables would be the following:

• Customer Table

• CustomerID – Unique identifier for each customer record

• Name – First name of customer

• Address – Address line 1 (Street)

• City/State/Zip Code – Line 2 (City), 3-4 (state), 5-6 (zip code)

Relationships can be established between tables by joining them on a common field.

Relationships can be established between tables by joining them on a common field. For example, you might have the following tables:

• Customers with their contact information (first and last name, phone number)

• Employees with their employee data (first and last name, social security number)

In this case, you would want to create a relationship between the Customers table and the Employees table based on their address information. When creating relationships in Microsoft Access, you must specify which fields will be used for linking purposes as well as what type of relationship should be established between them (one-to-one or many-to-many). The types of relationships are:

• One To One – Two entities are related by sharing an attribute value that cannot be shared with other entities within one relational database instance

Tables can be joined to themselves through the use of alias names.

Alias names are used to join tables to themselves, and they can be used in queries as well. The following example illustrates this technique:

• First, create two tables on your Access database: Customers and Orders .

• Next, create a third table called Authorized_Users that contains all of the users who are authorized by their company’s system administrator (the person who created the database) to view data from one or more other databases owned by this same company. This step is optional but recommended because it ensures that only authorized users may access data from multiple sources at once!

• Here’s how you’d add an alias name between your Customers and Orders tables:

It is possible to join three or more tables together.

You can join two or more tables together in a query. Joining allows you to combine the results of multiple queries into one output, which makes it easy to view all the information in one table.

You can use the UNION operator to combine results from multiple queries and return them as a single result set. The INTERSECT operator does something similar but works on just one column (not necessarily an entire row). The EXCEPT operator removes duplicate rows from your query results; this is useful when you have many pages of data that have identical content but don’t necessarily match each other exactly.

The JOIN keyword indicates how you want the tables linked.

The JOIN keyword is used to link tables together. It can be used to link more than two tables and even other data sources, such as a database or spreadsheet.

To create a query, you enter the following syntax:

SELECT * FROM Table1 JOIN Table2 ON …;

Access allows you to join only three tables in a query.

You can join only three tables in a query. If you need to join more than three tables, it is not recommended because of performance issues and the possibility of corrupting data.

If you must join more than three tables, create a second query and use that one instead (or better yet, don’t do it!).

A query can retrieve data from one or more tables or queries, but also multiple instances of the same table.

A query can retrieve data from one or more tables or queries, but also multiple instances of the same table. This is useful when you want to see what data is in your database, but not necessarily all of it. You can use this information to create reports and charts that show only those rows that meet certain criteria.

This feature is called a join and it works by looking at the relationships between two databases: the first database contains some columns that are related to another column in another database (or table). The second database contains any other columns needed for this relationship to be valid; thus, if you have access only to one side of these associations the first you won’t be able to create any meaningful reports/charts since they wouldn’t make sense without knowing what goes into both sides beforehand!

Data can be linked to other data sources such as Access databases, Excel spreadsheets, text files, and even web pages!

A link is a relationship between two pieces of data. You can use linked data to link your Access database with another file, such as an Excel spreadsheet or text document. This allows you to store information in one place and then retrieve it from another location when needed (such as when importing information from a web page).

To create a link:

• Open Microsoft Access and open the form where you want to add the new field(s). Click on File > New > Linked Table View Source.

• In the Create New Source dialog box, enter a name for this source and choose an appropriate format for displaying its contents (e.g., VBA code). When done adding fields by clicking Add Field… button available at toolbar along with other options like adding columns etc., click OK button again to save changes made so far in our example above – we have created two new columns accordingly viz.; `Name` column which contains names of people associated with each record while `Address`column contains addresses being entered into respective records respectively.”

Microsoft Access is a great way for you to get your hands on a real-life relational database.

Microsoft Access is a great way for you to get your hands on a real-life relational database. You can use Access to create and manage databases, store data, retrieve data and make queries.

You can also use Access as an application development tool by creating forms, reports and macros in Visual Basic 6 or VBA (Visual Basic for Applications).

Conclusion

In this article, we’ve covered the basics of how Microsoft Access works. We started with an overview of what databases are and then moved on to show you how they can be used to store and retrieve information from a database. Then we covered some of the common requirements for creating relational databases in Access before finishing off with some tips on how best to use them for your own projects!