access database samples by markhardigan

VIEWS: 585 PAGES: 19

									                               Creating an Access Database

To start an Access Database, you should first go into Access and then select file, new.

Then on the right side of the screen, select Blank database.

Give your database a name where it says db1 and save the file as a mdb file

You will then be brought to the following screen:

3/2/2006                                                                           1
                               Creating an Access Database

In Access, you first need to create a table with your data. The table is similar to an Excel
Spreadsheet. It has rows and columns. In this menu below, Tables is selected and then
you can Create a table in one of three ways. Design view, by the wizard, or by entering

Once you create a table, you can then create queries to extract data and create a user
friendly Form to enter data into your table with a visual flare.

In this lesson, we will be creating a table in Design View. You can enter the field names,
data type and a description of the field.

3/2/2006                                                                             2
                              Creating an Access Database

Begin Entering the field names of your database in the description. In this Example we
are creating a database for Software Licenses on computers. It is important to know all
of your fields ahead of time. Think of the fields as column headings on an Excel

The data type for each field can be straight forward text, yes or no, a memo or even a
drop down for you to select from a list. When you build a table, think about how you
would like the user to interface with the data.

For instance, in the above example, we do not want the user to have to type in the
Software type each time. So when you go to data type, you can select from the dropdown
Lookup Wizard

3/2/2006                                                                           3
                              Creating an Access Database

This will then generate a series of menus for you to answer questions to.
Select “I will type in the values that I want”.

When you are finished with the table, go to save and name your table

Select OK. You will then be asked to create a primary key.

3/2/2006                                                                    4
                                               Creating an Access Database

                 A primary key is not necessary however when possible, you should create one. Primary
                 keys uniquely identify each record in the table. They can be created in one of 3 ways.
                 Auto number, Single primary key (as in a social security number) Multiple-field (two or
                 more fields make up the record.

                 Note that when you create a primary key, Access will not allow you to enter the same
                 value in the primary key field more than once. We will manually assign a primary key by
                 selecting the key on the tool bar.

  You must
  select the
  key item in
  the table
  before you
  hit the key.

Once you
select a key
you must
determine if
entries are

                 3/2/2006                                                                         5
                                                  Creating an Access Database

In this example,
we want to allow
because a person
may be entered
for more than
one license.

                   Save the table and then “x” out.

                   You have now created the frame work for entering the data. You can enter the data
                   directly into the table like you would for an Excel Spreadsheet, or you can create a User
                   friendly form.

                   Lets first do one entry with the table and then we will create a form.

                   Go to File Open and you can then select Table under the object menu.
                   Double Click on Software License Table to open it.

                   A spreadsheet will populate for you to begin entering data the same way you would with

                   3/2/2006                                                                           6
                                                  Creating an Access Database

                    Now we are going to switch to creating a form to appeal more to the user for data entry.

                                      Creating an Entry Form using the Wizard

                    Select form on the left side of the database window and double click on Create form by
                    using wizard. You will be creating the form based on the Software License Table

Select the fields
you want on the
form. If you
want all, select
the double
everything will
move to the

                    Click on next and then select the layout of your form.
                    3/2/2006                                                                          7
                              Creating an Access Database

You can select any one of the layouts to see how it will display. Make a selection and
click next. You can then select a style for the form.

Select Next and Apply a name for your form

3/2/2006                                                                          8
                               Creating an Access Database

Select Finish and the Option to Open the form.

The form now appears in Data Entry View. In order to modify it, you have to go into
Design view. Click on the Protractor to launch design view, so that you can modify it.

The Protractor initiates design view.

3/2/2006                                                                         9
                                 Creating an Access Database

   When you are in design view, you will see the grid of the form.

   Enlarge the form and modify it by adjusting the size of the boxes.

   While in the design view, you can move the various items around and you can also add
   buttons to Add, Delete, or Print records. When you go to add buttons, make sure you are
   in Design view.

Select Tool
box under

   The tool box will appear on the tool bar. There you can select an icon and then move the
   cursor to the form. Double click and then you can add a button. See the example below.
   Select the button tool on the tool bar and then double click on the form where you would
   like to see the button.

                                                                Select the

   3/2/2006                                                                         10
                              Creating an Access Database

When you select the button feature on the tool bar, a cross will appear on the form
Double click where you would like to see the button. When you do that, a screen will
appear showing you the various button options.

                                                     Button shows. Now decide
                                                     what you want the button to
                                                     do. Select a category and

In this example we will have the button be a find next button. So now that it is selected
hit Next on the wizard. Sometimes you can select different pictures for the button.

Click next. Give the button a name on the next screen.

3/2/2006                                                                           11
                               Creating an Access Database

Select finish.

The button appears on the screen.

You can move the button or resize it, just like you would any movable item in Word.

We want to resize and label the button to be like the other buttons.

To Create a Label, Select the icon the arrow is pointing to below. Then click on the

3/2/2006                                                                          12
                                  Creating an Access Database

Type in the label and move it over the button. You can right click on any button or label
to change its properties.

                            Filtering Data, Queries and Reports

You can filter data directly in a table. First you must go to the Table and click on the
data you want to filter by.
                               Select the data you want
                               to filter by. Here it is
                               Adobe Creative suite.


The results now just show you the people who have licenses for Adobe

To display all of the records again, click on the remove filter icon

All the records will re-appear.

You can also select out specific data by other methods.

3/2/2006                                                                             13
                                Creating an Access Database

Select the cell you want to filter and right click. A menu will appear

In this filter, we want to filter out all software that costs more than $200.00
You can then remove the filter again.

The right click menu also allow you to Exclude or Hide certain records. You can also
filter filtered data.

                                    Filter by Form

When you select filter by form, the filter icon is displayed with a form along its side.

3/2/2006                                                                             14
                               Creating an Access Database

Filter by form allows you to perform searches of a table, form or query in a database. It
allows you to quickly find and display records that meet specific criteria.

Lets try it! With our software license table open, select the Filter by form icon

The filter by form window then appears as it relates to the table.

To clear any previous filter, click on the Delete icon.     Notice the >200 will

Now we can do a new filter. Click on the cell beneath field name that you want to use to
filter the records. In this example we will select Software type. A drop down appears

Select one of the drop downs and then select the apply filter icon.

Click again to remove the filter. Remember you can filter with more than one field.

3/2/2006                                                                            15
                                Creating an Access Database

                    Creating A Query Using the Wizard

When you create a query to find information, you ask Access to find information that
meets specific criteria. It is like a filter, however it is saved, so you can run it over and

To begin, Select Queries under the object menu and also Create query using Wizard.

Make sure you have the correct table selected for your query. Select the available fields
and move them to the column on the right.

3/2/2006                                                                                16
                             Creating an Access Database

Click on finish.

Your Query has been created. Now to run it, just go to the Database screen and double
click on the Query.

3/2/2006                                                                        17
                              Creating an Access Database

                        Going into the Design of the Query

Double click on the Query and then select the protractor with the pencil

The view will change to what you see below:

At this point, you can change the criteria. For instance under Software type put

This would then create a Query for Contribute software
3/2/2006                                                                           18
                  Creating an Access Database

              Thanks for participating in the course.

                          User Services


                                   Digitally signed by Janice Mastrangelo
           Janice                  DN: CN = Janice Mastrangelo, C = US, O
                                   = Saddleback College, OU = ITC
                                   Reason: I am the author of this document
           Mastrangelo             Date: 2006.03.02 09:14:37 -08'00'

3/2/2006                                                                      19

To top