Microsoft Access - Create a DVD catalog

Helen Bradley

Learn how to restore order to your DVD collection with a database. I'll show you how to get started tracking a DVD collection (or any other collection) using your own custom designed Microsoft Access database.

You have probably heard of Microsoft Access, but you may never have used it fearing that it is complicated and more than you need for simple lists. While it is true that Access is a powerful database program, it is also useful for recording and managing simple lists of data. In fact, Access in spite of its size, is relatively easy to use and offers features such as Wizards which step you through typical processes such as querying the database and creating data entry forms.

I'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 will learn how to create the database, record information and query the data file – all the basic skills needed to manage any data file.

Getting started

Consider this scenario; you have a lot of DVDs and you'd like to keep track of them. If you lend your DVDs to friends you can track this data to 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 will need a field to record the title of the DVD and one to record the name of the person you have 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, 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 are Number fields and DateLent is a Date/time field. 

To enter a field, type the field name, press Tab, type or select the field data type and then continue 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.

The first step is to create a structure for recording your data.

Enter your data

You're now ready to enter some sample data into the file. Type 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 to enter the data there. If there is too much data to fit into one column, for example, you may have too much information to go into the MajorStars column, you can make the column 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 to allow more room. When you are 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 where you will see your table listed in the Tables area of the dialog. To open the table at a later date, click it and click Open.

Create a Data Form 

A data form can make it easier to enter DVD details into your data file. Once you have entered the details of a few DVDs into your data file you might have realised that the Datasheet view is not 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 – you can't any longer see the data from the first few columns. In all, its not a very easy method of entering data. In fact a simple form that shows only the current record – in our case a DVD – would be easier to use. Access lets you create forms for entering data and these can be designed with colour and neat headings so they're good looking and functional.

To create a form in Access use the Forms Wizard. Click on Forms in the Objects area of the Database dialogue box and double click Create form by using wizard. In 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 on the screen – to enter another DVD, click the New Record button on the toolbar and type the data.

Forms are an attractive way to enter data and view successive records.

Printing your data file

It's one thing to put data in a database, sooner or later you'll need to get it out too.

Step 1: Start the Report Wizard

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 to move each of them into the Selected fields pane. Click Next. Click a field to use it to group the data, for example, Category. Click Next.

Step 2: Choose your layout

Choose the field to sort your data on, such as Title. (In other situations, for example, when working with names you may need to sort on two fields, set the first field to Last name, and the second field to First name.) Click Next. Choose a layout such as Aligned Left 1 and Landscape orientation and click Next.

Step 3: View your report

Choose a report style to use, click Next and type a name for your report, select Preview the report and click Finish. Your report appears on the screen and you can print it by clicking the Print button or adjust its layout and design by clicking the View button.

Finding a DVD in your collection

The Access Query Design tool makes finding data in your DVD file a simple process.

Step 1: Start your query

To find information in your file, for example, to find all 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.

Step 2: Select the fields

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 automatically there). Choose all the fields you want to see - include the Genre field too.

Step 3: Specify a Search value

To locate all the Comedy movies, type "Comedy" (including the quotes) in the Criteria line under the Genre field name. 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.

Short Cut and Quick Tips

To copy a field's value from the previous record (saving retyping it), press CTRL + ' (apostrophe).

Add a picture

Add a picture to a form in Design View by clicking the Image button and drag to create the shape, choose the image to use and click Ok.

Force field completion

To force a field to be completed, in table Design view click the Field and in the Field Properties area set Required to Yes.

Default data

To place default data in a field (eg country UK), in table Design view, click the field and click the General tab and type the entry in the Default Value area.

Hyperlink data

To include hyperlinks in a data file, create a field using the Hyperlink data type. In Datasheet view, right click the field and choose Hyperlink, Edit Hyperlink.

Find data in a field

To find data in a field such as Tom Hanks is the MajorStars field where he is only one of a number of stars, enter in the Criteria area for that field: Like "*Hanks*"

(c) 2019, Helen Bradley, All Rights Reserved.