Introduction

Kristi, I attempted to find the database you did your thesis on. I can't find it. I can only find some of the raw hospital admissions data. So rather than re invent the wheel I have got the sample database that comes with Access. It called Northwind.

If you want to also practise some data importing I will put the Admissions data up so you can practise on that. Please let me know if you want me to do so.

To be able uses these notes affectively you're going to have to have them open and access open at the same time and switch from one to the other or have the screen split in two so you can see the notes and access at the same time. I assure you know how to do that

The method I am going to use to train you is to give you an exercise to do and you have to try and work out myself how to do it. I will then provide step by step instructions of how to do what. Only read the instructions if you get stuck. I'll also ask you how to do things as we go along. If you get stuck, e-mail me

I was unable to email you the crack because Gmail won't let me email a executable file, even if I put it in a Zip file. So I have put the crack files on glens server. You will have to download them one at a time by clicking on each of them below: You may have to right click and select “save as” on the fist one. Im not sure it it is needed, but included it just in case.

http://www.greig.net.au/geoff/SPSSCrack/recoil.nfo

http://www.greig.net.au/geoff/SPSSCrack/SPSS14.0patch.exe

http://www.greig.net.au/geoff/SPSSCrack/spssutil.dll

Note where you save them. Perhaps then copy them to the folder were SPSS is installed (see below) and then run one with the .exe at the end of it. (double click on it to run it).

To find out where SPSS is installed go to the icon that you uses to start it (if it is on the desktop) and right click on it and click properties. The “Target” will show the folder where it is. If you start it from the start menu right click on what you click on to start it, and go to Properties, then the same as above.

The Northwind Sample database can be downloaded by clicking here http://www.greig.net.au/geoff/Samples.zip

It is a zip files so after downloading it you will have to unpack it. In XP you right click on it and chose the unpack option. Remember where you unpacked it to.

Start access. You will notice that at some point it will ask you to register it and tell you how many time you can use it without registering. Hopefully you will have enough time to use it without registering. If not try registering anyway as it is a copy that we do own

1. From the file menu click on open, open the file Northwind.mdb. A another window will open telling you something about the sample click on the OK button to Close that window. There is also a main switchboard window, close that also
2. Click on each of the objects to see what each contains. I'm pretty sure you know what tables and queries are. You probably don't know too much about forms report's, pages, macros and module's are. I'm not too good on anything other than tables and queries myself

1. Open each of the tables to see what is in them. Without going any further you should be able to describe the relationship of each table to each other table. For example, how does the categories table relate to the products table? How does the order details relate to the orders and in tern relate to the customers and products?
2. From the tools menu click on relationships. May be expand the window so that you can see it all. This relationship screen, which you can change, shows the relationship of one file/table to an other.
3. Can you see why this database is not designed very well?
4. There are addresses in multiple files, in a good design there should be an address file linked to the other files that way if the same person was an employee customer or supplier there would only be one address for them
5. You also need to understand the difference between the way files can be joined This is especially important when doing queries. It is called inner joins and outer joins. I have trouble working out the difference myself. Will do more on this when we do queries.
6. Lets create a new file Called Weather Conditions. To make it easy will only have three columns, date, temperature, cloud cover. Go ahead and see if you can create this file. My steps in doing so are below.

1. Ensure you are in the tables object, click create table in design view
2. in “field name” type in “Whether date”, In data type with the pull down menu select the date/time.
3. go down to the next line and enter temperature for the field name, the type would be number
4. again go down to the next line and enter “cloud cover” as the field name and text as the data type
5. Close the window, it will ask you for a files name which you will make “Weather” and if you want a primary key to which you should say yes
6. Now how do you link the table to the other tables?

1. From the tools menu select relationships
2. On the relationships window in a blank area right click and select Show Table, click on Weather and then Add.
3. Weather in now in the relationship window
4. In the Weather table drag the Weather Date to the order Date in the Orders table. An Edit Relationships window will open.
5. Click on the Join Type button. Do you understand the difference between the 3 options. This is the inner and outer or strait join I mentioned before.
6. Leave it at 1, click ok, Click create. See what happened
7. Repeat the above process for the other dates in the Orders Table. It will be slightly different
8. How do you change or remove relationships?

1. Enter some data into the weather table for dates that exist in the orders table

1. Look at some of the existing queries to see if you can understand what they do
2. Add a new query that shows orders when the temperature is over 20 degrees
3. Add a new query that show all orders that beyond a certain date that are greater than a certain value
4. Here is a link to a page that explains how to build and modify an access query using the Northwind example http://databases.about.com/od/tutorials/l/aaquery1.htm
5. Try making up you own queries and make them as complicated as you can. If you can't do what you want ask me

HINTS

1. I was having trouble working out where all the columns were coming from in the example queries. It turns out that if you want all the columns in a table that you select the first entry in the field drop down list when you are creating or modifying a query in design view. Eg. in the Products table the first field is Product*, notice the asterisk after the name product. This means all fields in the name

From: http://searchsqlserver.techtarget.com/sDefinition/0,,sid87_gci212885,00.html

A relational database is a collection of data items organized as a set of formally-described tables from which data can be accessed or reassembled in many different ways without having to reorganize the database tables. The relational database was invented by E. F. Codd at IBM in 1970.

Wikipedia also describes it well here:

http://en.wikipedia.org/wiki/Relational_database

Basically they allow you to summarise information.

Right Click on this http://www.greig.net.au/geoff/BrisWeatherfromBOM.ods and then click on save link as, to download the weather spreadsheet. Note where you save the down loaded file to

Open it in Open Office Calc. It is very similar to Excel. Have a look at the sheet. Let say we want to know the total or averave wind speed (Column O)for all days by wind direction (column N). See if you can work out how to do it yourself first. Pivot tables are called DataPilot in Open Office.

My way of doing it.

1. Select the cells that you want to work on by Clicking on B9 and then whilst holding down the Shift and CRTL keys press the right arrow, then then down arrow, Perhaps also the END key to get to the end of the data.
2. From the Data menu click on DataPilot and then Start. Ok for current selection.
3. Drag “9 am Wind direction” to the “Row fields” box. Drag the “9 am Wind Speed” to the “Data fields” box. Double click on the “9 am Wind Speed” and select “Average”, click ok. Click on the “More” button. In “result to” change it to “new Sheet”. Click on the ok at the top right.

You should now have a new sheet which shows the average wind speed by wind direction.

Some Test exercises for you

1. Average temperature by 9 am cloud amount
2. Total Rainfall by wind direction
3. Try combining both the above in one pivot table (not sure if it can be done)

These are called Expressions in Access and enteer in the ????

In the Northwind database in the products table their are columns for “Unit Price” and “Units in Stock” Let's say we want to have a value in stock by multiplying the two of these together.

1. Select the queries object, click on create query in design view
2. Click on the products table, Click add, click close for the table view
3. From products double click on product name, unit price, and units in stock
4. In the next available space right click on the field and from the drop down menu select “Build”. This is the expression builder
5. Double click on the plus beside tables so that you can see all the tables
6. click on Products, double click on unitprice, click on the astris button (multiply), double click on units in stock, click OK. Press the enter key. It all should saying like Expr1: and the formula. You can change Expr1 to “value of stock”. Exit the query window, it will ask if you want to change the name
7. Double click or press enter on the name of the query to run it. Value should be the quantity times the price.

A few test exercises for you

1. Created a new query that multiplies the price times the quantity on order
2. Create a query that only shows the items that need to be reordered. They need to be reordered if the units in stock minus units on order is less than the reorder level
3. Create a query that shows we're the unitprice in the order details table is different from the unitprice in the products table