Keep your DVDs in order with an Access database by Helen Bradley

Keep your DVDs in order with an Access database

Helen Bradley explains how to track a DVD collection (or any other collection) using your own custom-designed Microsoft Access database.

Access databases

You’ve probably heard of Microsoft Access, but you may never have used it for fear that it’s too complicated to master and more than you need for simple lists. While it’s true that Access is a powerful database program, it’s also useful for recording and managing lists of data.


In fact, despite its size, Access is relatively easy to use and offers features such as wizards that step you through typical processes such as querying the database and creating data entry forms. This month, we’ll show you how to create a simple database table to store information about your DVD collection, although the process is similar for any type of collection. Along the way you’ll learn how to create the database, record information and query the data file.

Getting started
Consider this scenario: you have a large collection of DVDs and you’d like to keep track of them. If you lend your DVDs to friends, you can track this data too so you know where they are. To create a database to store the details of your collection, start by determining what data you need to record. To do this, work out what information you expect the database to provide you with. For example, to know who has borrowed a DVD you’ll need a field to record the title of the DVD and one to record the name of the person you’ve lent it to. This is a sample list of fields you may use to record your DVD collection: ID, Title, Category, MajorStars, Producer, Director, Year, Rating, Length, Borrower and DateLent.

Create a data structure
To create a database for this, launch Access. Choose File > New > Blank Database. Type a name for your database (DVDs) and click Create. When the Database dialog appears, click Tables in the Objects list and click 'Create table in Design view’. Here you enter the column headings for your table and each data type. Use the fieldnames ID, Title, Category, MajorStars, Producer, Director, Year, Rating, Length, Borrower and DateLent. All are character field types except ID, which is an AutoNumber field, Year and Length, which are Number fields, and DateLent, which is a Date/time field.

To enter a field, type the field name, press [Tab], type or select the field data type and then move to the next field. Continue until all the fields are entered. When you’re done, click the box to the left of the field name ID to select this row. Right-click and choose Primary Key to set this field as the data table’s primary key for sorting data. Click the View button at the far left of the toolbar and, when prompted, save your table and call it DVDs.

Enter your data
You’re now ready to enter some sample data into the file. Enter the details for five or six of your DVDs into the list by typing the details into one column, and press [Tab] to move to the next column. If there’s too much data to fit into one column, you can make it wider. To do this, click the View button in the top left of the window to return to Design view. Select the field and, in the General tab, increase the value in the Field Size area. When you’re done, return to Datasheet view by clicking the View button and continue to enter your data. Notice that the ID field is an AutoNumber field so Access automatically increments the numbers for you as each record is entered.

When all the data is entered, close the table by clicking the Close button in the top-right corner of the window. This returns you to the Database dialog, and you’ll be able to see your table listed in the Tables area of the dialog. To open the table at a later date, click it and press Open. You now have a fully functioning DVD database.

Track your data by printing the data file

1. Click Reports in the Objects list and double-click Create report by using wizard. Select the fields to include in the report and click the chevron button. Click Next. Click a field to use it to group the data. Click Next.

2. Choose the field to sort your data on, such as Title. (In other situations you may need to sort on two fields.) Click Next. Choose a layout such as Aligned Left 1 and Landscape orientation. Click Next.

3. Choose a report style to use. Click Next and type a name for your report. Select Preview the report and click Finish. You can print your report by clicking the Print button or adjust its layout by clicking the View button.

Quickly find a DVD

1. To find information in your file, such as the Comedy DVDs, create a query. Click Queries in the Objects list and double click Create query in Design view. In the Show Table dialog, choose the DVDs table, click Add and then Close.

2. The query is created using the on screen grid. Drag every field you want listed in the results into a column in the grid (you can also double-click a field name to move it there automatically). Choose all the fields you want to see. Include the Genre field, too.

3. To locate all the Comedy movies, type "Comedy” (including the quotes) in the Criteria line under the Genre field name and click the Run button on the toolbar (it has an exclamation mark on it) to run the query. Click the Design button to return to your query.
Helen Bradley



How can I enter data more easily?

Once you’ve entered the details of a few DVDs into your data file you might have realised that the Datasheet view isn’t particularly easy to work with. Not only is there a lot of data showing here, but if you move across to the right to enter details into one of the last columns then you can’t see the data from the first few columns.

A simple form that shows only the current record - in our case a DVD - would be easier to use. Access enables you to create forms for entering data and these can be designed with colour and neat headings so they’re more attractive and functional.

To create a form in Access use the Forms Wizard. Click Forms in the Objects area of the Database dialog and double-click 'Create Form by using wizard.’ During the first step of the wizard, your table will be selected. Click the double chevron to add all the fields in your table to the form. Click Next. Choose Columnar as the form layout and click Next. Choose an attractive Style from the list and click Next. Type the name DVD Data as your form name and click the 'Open the form to view or enter information’ option. Click Finish. The new form appears. To make an entry for another DVD, click the New Record button on the toolbar and type the data.