You are going to create a music information system. This could store details of a personal CD collection or it could be a much larger project designed to help customers in a music store. The data might be put online so users could view it from a web browser and the level of data might extend to tracks and musicians so users could find CDs without knowing the title. In an age of online 'jukebox' music applications, where users can compile their own selection of MP3s it could be used to help users identify their choices.
To start with you will have to set up a new database and then define the first table. A database consists of a number of tables, along with some other objects we shall meet later. A table consists of rows or records, which are made up of data items known as fields.
Click on 'Create a New File'. You will now see the second panel:
Choose 'Blank database' and choose an appropriate location for the file you are about to create. Enter a suitable name (such as 'music') and click Save to confirm. Your database will be created and Access will now display the database control panel:
On the left of the panel is a list of the objects that can be added to an Access database. The default option for a new database is Tables, which is highlighted in orange. In the larger panel is a list of actions the user can take and this is also where objects created by the user will appear. It is best not to maximise this panel as this will hide the grey worktop area where other objects will appear.
If you already have a database in another format such as an Excel spreadsheet you can cut out a lot of work by importing it. See here for details.
Double click the Create table in Design view button to open the table design form. Enter the following information:
| Field Name | Data Type | Field Properties |
| SerialNumber | Text | Size=20 |
| CDTitle | Text | Size=100 |
| Artist | Text | Size=50 |
| DateReleased | Date/Time | Short Date |
| Label | Text | Size=20 |
| Category (or Genre) | Lookup wizard | Size=20 |
| NumberOfTracks | Number | Integer |
The Number of tracks field is set to an integer to save space. The default numeric type is Longint, which allows values up to 2,147,483,647, which is rather more than the average number of tracks on a CD. The integer type stores values up to 32,767, which is still much larger than we need. We could set the type to Byte, which stores numbers up to 255, surely enough for the number of tracks on a CD. Still, integer it is.
The Category field will include terms such as 'pop', 'jazz', 'folk', 'hip hop', 'garage', 'rock', 'classical', etc. Rather than type these every time and run the risk of spelling things differently (pop/popular, for example) or making up new categories on the fly we can define a list of possible categories that the user can choose from. To do this change the type for this field to 'Lookup Wizard...', whereon you will see the following dialogue box:
Later we will explore the possibility of creating a separate table to list the categories. For now we will choose the second option and type in the values we want. Click on Next and enter the items in the table. We only need 1 column so there is no need to change this. Use the down arrow key to move to the next empty space. Click Finish and accept the field name 'Category' as the name of the lookup (an alternative title would be 'genre').
A table in Access must have a primary key that uniquely identifies each record in a table. For our CD table we might use the Title as the unique identifier but any number of artists may produce a 'Greatest Hits' compilation so this will not be enough. We could use the combined fields of Title and Artist to counter this but long-standing artists may have more than one 'Greatest Hits' compilation so we would need the date as well - it's very unlikely that they would bring out two CDs with the same name on the same day!
For reasons that will become apparent later a better solution to the question of the primary key is to use the unique identifier found on every CD. Note that this unique identifier applies to all copies of a particular CD and not to individual copies. For books the ISBN performs a similar function, identifying a book in the abstract but not distinguishing between actual physical copies.
To set the primary key to the SerialNumber field click in the small grey square to the left of the field name and then click on the yellow key icon in the toolbar to set the primary key - you will see a similar key symbol in the row you selected.
Identifying the primary key is an important task as it can have far-reaching effects on other aspects of the database. One of the rules of database design is that all fields in a table must be fully dependent on the primary key and this is generally easier if a single field can be used.
To save the table click on the disc symbol in the toolbar and enter a name for the table - 'cds', for example.
Close the Design view of the table; the table you just created appears in the list of objects below the three actions.
Double click on the cds table icon in the panel to open the table in Datasheet view. Enter some data.
Note the spreadsheet-like layout of the table. You can change the width of the columns just like a spreadsheet. Note also the Record controls at the bottom of the window with buttons for rewind, left 1, right 1 and fast forward. This will become more useful as you add more records.
When you have finished entering records close the table window.
There are some other fields we can add to the database, one to hold a picture of the album cover (like entries at Amazon) and another to hold a hyperlink to a web page. To see the table in Design view either:
Double click the table icon to open it in Datasheet view and then use the View icon in the datasheet toolbar to switch to Design view
or:
Click once on the table icon to select it and then click on the Design button in the database control panel toolbar.
The toolbar changes according to context. The one shown above is for the Datasheet view; the View button is on the left.
The two new fields can go at the end of the current list. If we wanted to insert them between existing rows we would right click on the grey square of the row below where we want to insert the new field and choose Insert Rows from the popup menu.
Enter the new field information: picture, type OLE Object and website, type Hyperlink.
Save the changes, close the window and switch to Datasheet view.
Adding a picture to a database is a complex business because the data type is different to what databases were designed to handle. Databases are fine with text, which they can store internally, but objects like pictures, sounds and video (multimedia objects) cannot be handled in the same way. Objects of this type are sometimes referred to as 'BLOBs', or Binary Large OBjects, and there has recently been some interest in developing 'object oriented databases' to allow them to be included. As multimedia objects become more significant in daily applications so there is a greater need to store them in ways they can be accessed, both online and in proprietary systems.
To add a picture to a database we can do one of two things:
Note that you cannot see images or pictures in table view, the only view we have so far encountered. Later you will create an Access form to display your data and this is where you will be able to see your image.
Another type of field you may want to insert is a hyperlink so that users of the database could insert a web page (URL) or email address - choose the Hyperlink option from the drop-down list. To insert a hyperlink in the table choose Insert/Hyperlink and use the dialogue box to find the file.
Files in the current directory are displayed in the main list. Change the 'Text to display' field to suit your needs.
Click on the Web button in the file dialogue to activate your web browser - you can then copy and paste the URL from the URL bar or switch between web browser and Access to place the address of the current page in the Address field of the dialogue box. Alternatively, just type the address straight into the field.
To enter an email address click on the button on the left and fill out the details in the dialogue box.
The text you type into the Text to display box will appear in the Datasheet field as an underlined hyperlink. To edit it later use the arrow keys to enter it (you can't use the mouse as this activates the link) and press the F2 key to edit it.
If you specify a web address in the hyperlink then clicking the link will open your browser; if you specify an email address then clicking the link will open your email client (e.g. Outlook). We will see later how we can enhance a hyperlink with a smart tag.
It will be useful to record whether or not a CD is available to buy. This is a two-state condition so a Yes/No field is appropriate. Add a suitable field now.
This concludes the setup of the first table.