Access07 Heights

Microsoft Access 2007 A flying tour: Heights database

Database and table design


From this the structure of the database, tables, records and fields can be developed.
 * The database will be called organisation
 * An MS-Access database can contain many tables (Entities, groups of related data), so we’ll call the table tblPeople
 * Each Table contains the actual data, organised into records (related data) and fields (individual pieces of data).
 * Each record is defined in terms of the individual fields, here we’ll use FullName (to store the person’s name) and Height (To store the person’s height).

Finally work out the metadata (data types and sizes) for each attribute (field).



Physical design
MS-Access is a fully-featured DBMS, where the:
 * tables and data (often referred to as the back-end), and
 * forms, reports (front-end)

may be contained in one disk file.

Access 2007 uses .accdb extension, whereas previous versions used .mdb (MS-Access does allow the back-end and front-end to be split)

Notes
 * the mdb or accdb databases should only be opened by Access versions they were created. (Although you can create earlier mdb files in Acc2007).
 * Using a split front-end back end will allow different versions of the front-end to access the back-end, so long as the back-end is created in the earliest version.
 * An Access database containing only the tables and data is used on the Internet and the front-end is created using Active server pages (.aspx), or PHP.

DB.1: Create the database
Once the design is completed you can fire up MS-Access and create an electronic database.
 * Start MS-Access.
 * From the Center Panel select Blank Database, a blank Database panel will appear on the right.
 * Select the folder icon next to the Filename



DB.2: Create the database
Or select the directory to save your database in.
 * Navigate to your Home drive (or the drive to put your database – memory stick?)
 * Create a folder if you need to e.g. DB500
 * Name the database org.accdb (Note: this is an Access2007 only database – you can create a DB for an earlier version by changing the “Save as type”)

Press [Enter] or click on [Ok]

The Blank Database pane will be shown,

Click [Create]

DB.3: Create the database
The Database Window will be displayed

T.1: Create the table (people entity)

 * Change to design view.




 * You will be asked to enter the name of the table. Enter tblPeople


 * Now think that you are going to create a blank form where you can insert the names and heights, so you need to DESCRIBE what you are going to enter, not the actual DATA.

T.2: Add fields (entity attributes)

 * PeoID (Autonumber) = a unique number for your people, called the key field.
 * PeoFullName (Text, 30 {change field size from 255})
 * PeoHeight (Number, integer {+/-32767})



Note: If you want to know what other number types there, click on Field size then press [F1]

T.2q: Questions/Tips
1. Why is it better to assign a number to each person rather than use their full name as an identifier?

2. What problems can occur when entering data if the field is called FullName (clue: Think of Chinese names)?

3. Heights is entered as an integer (+/-32767). What unit of measurement should be used (m, cm,mm)?

4. The identifier is a long integer. How many people can be uniquely identified?

5. Why is it a good idea to add a prefix (like Peo) to field names?

6. Why do we add prefixes like tbl to tables?

Tips
 * It is not good practice to use words like "Name", "Print", "Report", "Input" that could be used in other ways as field names.
 * Try to get used to using object programming conventions with field names. Use CamelCase words. Any new words start with a capital (e.g. FullName, LastName)

T3. Add some data

 * Change to Datasheet view (allows you to add some names). It will ask if you want to save the table, click [ok]




 * Add the following names.

Tips Notes
 * Use the [Tab] button to move from field to field.
 * When you enter data a pencil will show at the start of the record. This means that the record is in the computer’s memory and until the pencil disappears the data is not transferred (called committed) to the database. If you want pressing the [Esc] key will cancel the data entry.


 * The record is not saved until the pencil disappears. This occurs when you move onto the next record or you select Records | Refresh from the menus.


 * To resize a field, place the mouse cursor between the FullName and Height field names until it changes to a double arrow then click and drag to the right to enlarge the FullName field.

Printing the datasheet
It is possible to printout the datasheet.

Use the Office Button, then choose Print > Print Preview

The Printer icon can be used to send to the Printer.



Tip You should always preview a report before sending it to the printer

Notes This is a printout of the data. Later we will produce a report where selected data is displayed and headings are added. When presented as a report we say that information is displayed.

Sorting the data
Databases are usually made up of many many records. Large databases can run into millions of records in a table. So how do you sort that data. MS-Access allow the data to be sorted using the ascending or descending sort buttons.

Exercise


 * Click the Drop arrow in the Field name PeoFullName(at the top of the colum) and choose Sort A to Z (or Z to A).


 * Reset the table by sorting on PeoID


 * When you are finished close the table (tblPeople).