Access 2010 Joining two tables

Microsoft Access is a relational database system. That simply means that in the one file you can have multiple tables, forms, queries, and reports. It is pretty much like when you were back in junior high school and you kept all of your notes in a three-ringed binder with different sections for each of your individual classes. Access has a navigation pane on the left side that acts as a separator of each type of object. .

You can have many different tables in the same file; but if you need to get information from more than one table at the same time, you need to create a join between two tables. A join is simply a way to travel between the two tables. Think of the join line as a road used to travel between the two tables. While driving, you need to find a common intersection to be able to go onto the other road. Well, in joining tables, you need to have a common field to create the join. A common field is nothing more than the same field in the two separate tables. Let’s take a look at this. We have two tables that we are going to use as an example. We want to be able to get a list of all of the departments and the employees that are in those departments. To get to the relationship window, you go to the Database Tools ribbon and click on Relationships.

You may need to select the tables that you want to join. That brings up the dialog box that allows you to select the two tables that you need to join. Now simply select your tables.

Once you have selected your tables, close the Show Table dialog box. You may need to resize the table information so that you can see the fields in each table. For this example, we are using the Department table and the Employees table.

Now comes the easiest part! Simply drag the field of DeptCode from the tblDepartments where it is the primary key field over the field of DeptCode in the tblEmployees where it is the foreign key field. Now what comes up is this dialog box. Put a checkmark in Enforce Referential Integrity and I always like to Cascade Update Related Fields and now simply click the Create command button. You have now just created the join between these two tables and anytime you use these two tables in any query, the join line will be there.

All that is left to do is to close out of the Relationship window by clicking the close button and now you can create your queries pulling any information from either table. These tables will stay joined until you decide to delete this relationship. Creating join lines in Microsoft Access really is this simple!

IPMT Financial Function Excel 2010

The IPMT function allows you to work out the Interest payment based on the set criteria as outlined in the syntax below.

Syntax

IPMT(rate,per,nper,pv,fv,type)

Rate     is the interest rate per period.

Per     is the period for which you want to find the interest and must be in the range 1 to nper.

Nper     is the total number of payment periods in an annuity.

Pv     is the present value, or the lump-sum amount that a series of future payments is worth right now.

Fv     is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0).

Type     is the number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0.

The first thing you might want to do is set up your spread sheet and format accordingly.  This is an example of how I would set up my spreadsheet and what it will end up looking like. Please note on my example I have used both the PPMT and the IPMT functions.  I have added a short blog on the PPMT function on this site.

In the cell to the right of the INTEREST COMPONENT where you see “£2,479.17” is where you will enter your IPMT function.  This is what it will look like if you use your INSERT FUNCTION option;

You will notice that way I have suggested you set up your spread sheet mirrors the options shown in this box and that makes entering the correct information a pretty simple process.   Two things you need to remember are;

  1. to note is that you have to divide the rate by 12 months, so it will look something like this “B3/12
  2. enter the PV as a negative as this is the amount of money that is being paid back, ie, if you borrow £500,000 for a mortgage then this is the amount of money that you are in debt for.

This completed function will look like this;

On your screen the function will appear like this;