Database Introduction

Databases

In the dark recesses of a long time ago (in computer terms - about 10yrs ago),

there was the Database

Introduction	Re-design	Macros	Data Integration
Wizards	Relationships	Modules	Advanced Databases Studies

Introduction

The database was simply a large table with columns (fields) and rows (records). In a way it was similar to a spreadsheet with columns and rows....but

Whilst it could not do some of the fantastic calculations that Excel can perform, it could do one or two things a whole lot better.

Massive Storage of Data

Because it did not need to have to store cell attributes along with data in a cell, the amount of storage space was dramatically reduced and therefore a file could hold a very large amount of data.

Quick Retreival of Data

Because of it's compactness, data could be searched for, found and retrieved very much faster than from a spreadsheet or word processing document.

In addition you could determine exactly what you wanted to look for by giving the request criteria: e.g.

Name = Smith, Age = >30, City = London, Salary >30,000

This would then carryout a search and retrieve all Smiths aged over 30 who live in London and earn more than £30,000 per year.

A spreadsheet or document would be too cumbersome and slow if it had to store thousands of records within a file - and so for larger users who had that number of records, the database should became a necessity.

The advances since then were not just in speed and capacity, but in the very structure of databases.

It was soon realised that instead of having one gigantic table, it would be more efficient to have a number of smaller tables.

Further to this it was also realised that the individual tables could act together if links were placed between them - these were called relationships and thus the database was called 'a relational database'.

The building of a database was still a laborious task, but companies such as Microsoft soon developed Wizards which could take you through 'the building' step-by-step, with simple questions leading to the finish of the database structure. This meant that you only had to have minimal knowledge to create a database. The databases today have such wizards plus a great deal of flexibility which can be achieved by learning a little more about other database functions.

Here we'll cover the use of a Wizard and then some (not too much) additional info to make the database a little better.

Using Wizards to create a Database

Starting Microsoft Access gives the options of:

Creating a blank Database
Creating a new Database using a Wizard
Opening an existing Database

Here we'll assume that you're creating a new database, so which to pick? Option 2 or 3.

Well, normally without some good skills already, you would probably choose the Wizard option. However this will zoom through the complete database creation procedure without giving you any ideas about what has been done.

Generally speaking higher level wizards do more for you but are not as flexible, the main wizards create all elements from a common template. You can often speed more time re-designing the result, than starting from a blank database and using individual lower-level wizards.

We will use wizards but the lower-level ones, which individually create a Table or Form or Query or Report - starting from a blank database.

Click the radio button for the 'Blank Database and then click OK

In the 'New' box that appears there are two tabs, the 'General' and 'Databases' tabs. In the 'General' tab, select and OK the 'Blank Database' - (the MDZ files in the Databases tab are the main wizards).

If you are new to Access or have only ever opened existing databases before, the next step may throw you off-guard - you have to name the database you are about to create.

This will obviously be easier if you have already thought what purpose the Database will serve - e.g: a Customers database. For simplicity you could call the database something like: TestDBase1.

If the Create In box is 'My Documents' leave it like that, or choose a different folder to create to. Put TestDbase1 into the Filename box (don't take out the .mdb - this identifies it as a Microsoft database). Leave the File Type window alone. Click the 'Create' button.

A Database Window now appears. You will see tabs for: Tables, Queries, Forms, Reports, Macros and Modules. There are 3 buttons in the Database Window: Open, Design and New.

Tables:

Tables are the basis of the database (as mentioned in the Introduction), so the first step is obviously to create a table using the New button in the Tables tab.

Clicking the New button gives the 'New Table' box with the choices: Datasheet View, Design View, Table Wizard, Import Table, Link Table. Click 'Table Wizard' and then OK.

The 'Table Wizard' box appears and has three sections.

Here you will:

1. Select a type of table from the 'Sample Tables' window.

2. Select sample headings (fields) for the 'Sample Fields' window .... and

3. Transfer them to the 'Fields in my new table' window on the right.

Before you do this, don't worry that you cannot see field name for every field you might want - just pick a name that has a similar structure (text or date or code..etc - YOU CAN RENAME fields anytime you wish.

Using the direction arrows > (single transfer) or >> (All sample fields in the selected Sample Table)

and < or << (when the cursor is in the left window) to transfer back (for example if you need to undo your choice).

You can experiment and change your mind about fields names, and be aware that you can select different 'Sample Tables' in the left window with each giving a different set of sample fields to choose from.

Again don't worry about missing out a field, you can also add new fields later.

When you have transferred fields across, you can select any field and Rename it with the button provided. Click the NEXT button when you have done.

Next, you must name the table to distinguish from any other tables you might create later. Type a name into the window that relates to the table e.g: Customers. In this section you have also to decide about a primary key.

Primary Key

If you have a customers table which will sort or find entries by say, a Surname and that is the title of your first field - then let Access create the primary key for you using Surname as the primary key.

Generally Access will pick the first column to be the primary key field as this would be the most important one for you. Either let Access choose the key or you do it. (If you want to choose, you will get an extra box with a window, where you will have to select the field from a drop-down list box).

Last Step

The last box will ask if:

You want to modify the design you have already made. Or..
Enter data into the new table directly, (like filling out the rows in a ledger).
Let Access design a Form that will make it easier to enter data through the form into the table.

For ease of use, I almost always choose the last option.

When done, click the finish button - the table will have been created and subsequently you will either have a blank table with your fields extending to the right, or a Form with which to begin entering data into the table.

If you did not let Access create a Form for you based on the table, then in the Form tab you can use the NEW button to create one.

Creating More New Forms

Of coarse you can create other forms that might use only some fields from the same table, or from new Tables you might create.

A Finished Database Upon Which To further Improve

With a table or tables created and corresponding Forms in place, you have a viable Database.

The forms can be used not just to enter data which will pass to the Database table ...but also can be used to view data from the database.

As this is basic entry and retieval functionality which is a vital part of any database - you now have a working Database.

However to make it a really useful database it needs one more main element - the ability to retrieve specific data upon request - upon a query given by the user.

The Query

A simply query is not complex to perform. It requires you to make a New query and then, add your request into the field you are interested in - the request is called a 'criteria'.

Example:

You want all the records where the customer lives in London.

You place the criteria: =London into the criteria row of the City field of the query. e.g:

Your First Query:

In the Query tab of the Database Window, click the New button

In the 'New Query' box click 'Simple Query Wizard', then the OK button

In the 'Simple Query Wizard' box: select your table in the 'Tables/Queries' window.

In 'Available Fields': select and transfer the field you will be placing criteria into, and any other field(s) of the table, which you need to show in the result (if you selected the criteria field only, then on running the query you would get a result with only one column of data in it, from the criteria field)

Click the Next button, and do it again in the following box.

Finally in the last box, type a name for the query in the title window and select the option to open and view information. Click the Finish button to complete.

The query result window will show, click the X at top left to close it

In the Query tab of the Database Window, select the new query and then click the Design button.

Enter criteria into the field(s) you want to query (as per the example above)

Close the design window, and save the changes.

Finally to run the query, click the Open button in the Query tab.

The query can be run at any time which means that because changes are made to the table (new entries, amendments, deletions), the results of the query will always give up-to-date information.

Using a database which uses Tables, Forms and Queries gives you all the elements needed to make a useful working database.

Re-Designing

The 'Design' button remains on-screen throughout the different database window tabs, eg: in the Tables windows, Query window, Forms window…etc. To redesign select the relevant tab, select the required element in the tab and then click the Design button.

Table Re-Design:

Select the table and click the Design button.

The table design has two sections:

Top: Showing a list of the table's fields and their types. New fields can be added here, or fields deleted or field types changed.

Bottom: Gives details (properties) about the field currently selected in the section above.

It is beyond the scope of this quick help file to go into this further, but you can investigate, getting on-line help by:

Looking at the explanation notes to the right when clicking in any property line.
Pressing F1 in any property line

DO USE:

The Field Size property to change the maximum length of input.

The Default Value if this particular field will invariable have a standard input e.g for a City field, London - could be the default where 90% of your business involves London. The user can always change it.

The Required property if set as Yes means the user cannot skip this field and must put data into it.

The Allow Zero Length property will allow a blank field if set to Yes.

BE CAREFUL of:

The Input Mask property -a mask means the user must type in data in the exact format you have set and whilst something like a postcode mask might seem a good idea, postcode formats do vary across the country or in different countries e.g:

LL0 0LL (L=letter, 0=Number)

IG4 6PZ (a mask of LL0 0LL)

EC2A 5SS (a mask of LL0L 0LL)

Query Re-Design:

Click the Query, click the Design button

In the top section are the tables, in the bottom the properties and criteria.

You can add a new table to the query if it is linked to the table being queried ( See Relationships), or drag new fields from a table to the bottom section.

Right clicking the mouse in a field will give properties you can change. It will also give you a Build, an expression builder to help with more complex criteria.