Joins - Links between Tables in a Database

Joins- Links Between Tables

To retrieve information from multiple tables, there must be a link between the tables. This is commonly known as the key field. It is a unique field within each record. For example, in our telephone address book example, the key field would be telephone number. For Access or other database program to link two tables together, it must create a link between these key fields which must be of the same data type in both tables.

Joins

There are several types of links or joins.

Equal join pulls records that have a match in both tables.
For example, if you were pulling information from a Customer Table and an Order table, if Customer A was in your customer table but had not placed an order, no information about Customer A would display but the information for Customer B would print since there is a matching record in both tables.

Left join pulls all records from the left (first) table if there are no matching records in the right table. In this case Client C would not display since he cannot be found in the order table. Conversely, if you were pulling from the right table, no information about Client D would appear since that company is not in your Client table.

Right join pulls all records from the right table and only matching records in the left table.

If tables in a query aren't joined to one another, either directly or indirectly, the database doesn't know which records are associated with which, so it displays every combination of records between the two tables. Therefore, if each table had 10 records in it, the query's results will contain 100 records (10X10). This result set of every possible combination is called a cross product or Cartesian product. This type of query can take a long time to run.

The point of this explanation is that you must consider what information you actually want and then decide upon which join to use. Equal joins are the most common however if you do need to use an outer join, make sure you place the tables in the correct order.

What is a Database?

What is a Database?


A database is an organized set or collection of related data that is stored in a file. The data in a database is entered in as records that consist of items or fields of data. Everyone has a database- take a look at your address book. An address book is a set of related information about a person (his name, telephone number and perhaps their address, fax number etc.). It is also organized - it is sorted alphabetically by name.


Each record is essentially a row of related information. Each field within the record contains information that pertains to that record. For example, a telephone directory can be considered a database. Each record has specific and unique information. The following fields or columns of information would typically be found in a telephone directory: first name, last name, address, and city and telephone number.

The following example shows a sample database:

Last Name, First Name, Address, City and Telephone are columns within the database table and are called Field Names. In other words, they identify the type of data shown in the columns below. First Name is the field name while John, Joan and Nancy are fields within individual records. Each row or record holds unique information. Only John Smith lives at 302 Milton Street in Indianapolis and has the telephone # 876-2256. In this case, the unique field would be telephone number.

A relational database allows you to create smaller and more manageable files that can be joined to extract or compare data on an as needed basis because the data in one file is linked to another via a unique field.

A database management system (DBMS) controls the structure of a database and access to the data. In other words, it creates, manages and protects that data as well as providing access, which also allows the user to integrate and share data across several files.

I thought I would change topic for the moment and talk about some important concepts to understand before creating a database.

I am off on vacation starting tomorrow so I probably won't be blogging. Have a wonderful 4th of July.

Database Design


The core of good database design is planning. Before actually creating a database, you should have a good idea of the kind of data your database will contain and how that data should be broken down. It is recommended to plan your database out on paper before beginning the creation process.

When creating databases, there are rules that most designers follow which help them create consistent, efficient well thought-out databases. This set of rules is called normalization, which dictates that your database tables will eliminate inconsistencies and maximize efficiency. The goal of normalization is to reduce data to its simplest structure with minimum redundancy and maximum data integrity.
Some of the important goals of normalization are:

• All fields should be broken down so that data cannot be divided further. For example, the field Name could be broken down further to last name, middle name, and first name.

• Each table must have one unique key field called a primary key. That is to say, there must be one field that identifies a record and does not allow duplicates. An example of this would be a social security number or customer number.
• All fields must directly refer to the primary key. For instance, in a customer table, you would only include information related to that customer, such as name, address, etc. You would not include a field called “Product name” in a customer table.

• A field cannot contain more than one value.

Thus, a normalized database stores each piece of information in its own table, all fields are broken down to their lowest possible level and each piece of information can be referred to by the its primary key. This is an important concept and is the entire premise behind a relational database.

Stock Charts

It's been awhile since I have written anything here. If you have been checking, my apologies - you know how the summer goes- between working, chauffering Kelsey to summer school and attempting to improve and expand my garden my time just evaporates. If you click on the RSS feed button, the blog will be delivered to your email and that will save you a trip here.

Today, I wanted to talk about some very basics in creating a stock chart. Yes, given the current stock market situation, I thought people might be interested in knowing how to do this even though it will be a depressing exercise if you track over time. However, some of you will find it useful.

Excel has four different types of stock charts.

• High-Low-Close
• Open-High-Low-Close
• Volume-High-Low-Close
• Volume-Open-High-Low-Close

The High-Low-Close chart is often used to illustrate stock prices. When you chart the data, the High and Low values are displayed with vertical lines while the open and the black bars represent close prices.

It is important to remember that you must organize your data in the correct order to create this and other stock charts. In other words, if you opt to create a High-Low-Close chart then your data needs to be ordered High Price, Low Price, Closing Price.
Creating Stock Charts


It is important to remember that you must have the correct number of series that the chart requires. Excel also requires that the data be in the proper order.

1. Select your data

2. Click Insert and Select Other Charts icon

3. Select the type of stock chart you wish to create




 

When the stock chart is created, a whole series of layouts and chart styles are displayed on the ribbon

Here is another example:

I made up data. When I used real data, it was too sad.

SUMIF

SumIf is a great function that a lot of people don’t know up about. Basically it combines two very popular functions -the SUM function and the IF Function. SumIf tests specified cells and if those cells meet certain conditions or “criteria” the selected cells are then summed up.


Think of the possibilities: You may wish to sum up receipts by different lockboxes or sum up the value of past-due invoices. Or you may just want to add up negative numbers in a column quickly or add up the returns or overtime for a particular day.

I am using a very simple example to illustrate SumIf. In the example below, I tested to see if any of the house prices in cells A3: A6 exceeded 150,000 so that I could sum the related realtor commissions. In looking at the example, you can see that there are two houses that meet this criterion (at row 4 and 5) and that the corresponding realtor commissions are in Column B.

The house prices at row 4 and 5 met the specified criteria so the corresponding values in Column B, (12,500 and 10,000) were added up to calculate total commissions on houses with prices over $150,000.


So, basically, the IF component checked to see if any houses were over $150,000 and then the SUM component added up the corresponding realtor commissions associated with those houses in Column A.


Two notes of caution of a SUMIF.

*Notice that I started my range at A3 instead of A2.It is important to remember not to include your column headings as part of the data range when you select your data as this may result in an incorrect answer. If you have a terrible memory and don’t like to fuss around then select the entire column by clicking on the column letter so that you don’t have to worry about it.

*If you use IFs frequently, you may have already noticed the quotation marks around the criteria. SumIF is not considered a logic function. It is considered a Math and Trig function so the syntax is a bit different and quotation marks are needed if you type the criteria in as I did.

Excel is usually forgiving and will automatically insert them if you forget. If the criterion used is a cell reference, such as B12, then you would not need to use the quotation marks; however, the contents of the cell would need to include the operator if there was one.

An alternative way to use SUMIF  if shown below.  This is an example of a rollup. I have personnel data and I want to add up salaries IF they meet the shift criteria that I have in cell G2.

 Here I have told Excel to add up all the salaries in Column F if the Shift information in Column E meets the criteria specified in cell G2. In this case, I want to add up all the salaries for everyone on Shift 1.

(To make it even more useful, use a data validation list in cell G2 containing the different shift numbers).