A database program

  A c c e s s  

CPT 105 · Franklin College · Erich Prisner · 2002-2007

Here is the Access project

Objects of a database

Tables

Any relational database "consists of" several tables. This is where the information is stored.

Forms

A form may be very useful both when creating a database for inputing new records, as well as for updating records. It presents the fields of the single record in question in a preformatted way, where the characteristics of each field (i.e. numeric, or text, or ...) are clearly visible.

Queries

Queries select records from (one or several several) tables based on some criteria specified in the query. The answer is another spreadsheet, that can be sorted, and updates automatically whenever the database is updated.

Reports

Reports are used to present query or table information in a nicer way, usually for printing it.

How to work with databases

Users of databases usually have to do the following:

Example: The Northwind sample database

Open Access. Choose "Open an existing file" and select the Northwind example. After clicking OK on the dialog, you see the database window.

The database window can show all objects of the database: tables, queries, forms, reports, pages, macros, modules

Click first on "Tables" on the left, and you will find all existing tables to the right, together with three icons for creating new tables.

Have a look on some of the tables. Change between Table Design View and Datasheet View by rightclicking on the blue top frame of the window.

Relationships

Let us see the relations between the tables. Either close the tables, or bring the database window to the top. Click on the relationships icon or click in the menus "Tools"-"Relationships".

This relationships window shows all 8 tables, together with the fields in these tables. Notice that fields in different tables are connected by a line. Usually it is the primary key in one table and the foreign key in another. All relationships in the example are one-to-many relations, meaning .... Note that the "Order Details" tables is the only table without a primary key.

Investigate these one-to-many relationships by opening the "Employees" table. and clicking on one of the "+" signs to the left. A subtable of the "Orders" table pops up. Compare this to the original "Orders" table. Click another "+" in the subtable to get a subtable of the "Order Details" table. Why aren't there any "+" signs in this subtable?

Working inside a table

How do you find data in a table? Open the "Products" table and find me all products that cost 14$. Click in the "Unit Price" columns and choose "Edit"-"Find". You can also find data by sorting the records according to the entry of that field. Rightclick in the field and choose "Sort", or choose "Records"-"Sort" from the Menu bar.
You can also increase prices: Choose again the "Find" option, but now you choose "Replace" and replace each price of $14.00 by $15.00.

Filters

A third possibility of finding data in a table is the Filter option. Put the cursor in some "New Orleans Cajun Delights" field, and choose "Records"-"Filter(by selection)" ot this button How can you check the result from the Customers table?

The "Filter by Form" button is more powerful, allowing formulas and also more than one criteria as filter. You also have to press the apply/remove filter button .

Filters are no objects, their results are not permanent, but you can save a filter as query using the Menu "File-Save as Query".

Queries

The FBI wants to know: Did the employee Steven Buchanan have contact with the company Ernst Handel? And if, what kind of products were ordered?

We go to the database window and click on "Queries". Then we click on "Create query in design view" and choose the tables we want to use by selecting and clicking on "Add". We close this dialog and select the fields in the chosen tables. We add some search criteria for some of the selected fields and click the "run" button.

Make sure that all tables used are connected by some relationships. Maybe you have to add intermediate tables to your query just in order to accomplish this. Usually the so-called inner join is used: Only those records show where there is some matching entry in the related field. If you want to show all records from one table, no matter whether there is some match in the other table, you use one of the two possible outer joins.

Multiple criteria

When multiple criteria are used, keep in mind that criteria in the same row are considered to be joined by "AND", whereas criteria in different rows are joined by "OR".

Calculated fields

Queries may contain columns (fields) that do not occur in the tables used, but are rather computed using several of these fields. Write something like "[Field1 Name] + [Field2 Name]" if field1 and field2 have numbers as data types, or [Field1 Name] & [Field2 Name]" if they are text fields, into the "Field" field. Thereby "Field1 Name" and "Field2 Name" should be names of existing fields in your tables. Run the query and look. Note that you have to include field names that are not just one word into brackets []. Examples are [Actor First Name]&" "&[Actor Last Name] in the movies database. Of course you may include criteria for the calculated fields as well.

Difference between Queries and Filters

FiltersQueries
---are saved objects
show all fieldsmay only show some fields
---statistics and calculated fields possible
both show only some (the selected) records
both can be used to enter and edit data

Forms: What a user should see

If you maintain a database, you are working with tables, queries, and other objects. Users however shouldn't look in the tables or queries---they should rather work with forms and reports only. These are the user interfaces. Queries, forms, and reports do not contain any data, but are rather "views" of the data in the table.

Forms

Forms allow user view on tables or queries (combining information from several tables). We use the form wizard, and we only use columnar forms (one record per page), but there are other possibilities.

For read-only forms, you may want to "lock" or, even "disable" certain or all fields of the form. Right click on the field and choose. For input forms, you may also select default values here.

Form Controls

Everything showing on a form is a "form control". Form controls are either bound---on a source in a table, then it can aslo be used for entering and editing data--- or unbound. Bound form controls are Text box, List box, combo box (combination of the previous two), check box and toggle button for yes/no fields, Option button, option group, and bound object form for OLE objects. Unbound form controls are label, tab control, unbound object frame, line and rectangle, and command button for running macros.

Subforms

A subform is a form within a form, commonly used for forms depending on several tables involving one-to-many relationships. You select two such related tables when you create your form using the wizard.

Filters in Forms

Type a word into w textfield, press the filter by selection button .

How to create or update a database

Creating databases from scratch is hard work. Essentially you have to design the tables and their relationship, and then you have to type in the data. Very often, however, existing data in the form of Excel sheets, for example, is used. Such tables may be imported easily using the "import" menu.

When designing a table you have to think about the data type or data structure of the fields used, see also the corresponing issue in programming languages. We have seen this before in Excel (to a lesser extend). Fields could have one of the following types:

You best specify the data type of your fields in the design view.

After assigning a primary key in each table, you have to define the relationships between your tables. Go to the relationships window and include all tables. Click on the primary key of one table and drag it to the corresponding foreign key of another table to create a one-to-many relationship. It is usually best to let Access ensure referential integrity, although this makes updating data slightly more complicated. Remember that in order to model many-to-many relationships, you have to create an intermediate table, like the table "OrderDetails" in the "Northwind" example.

Entering data ...

... directly in the tables

Start with the "one" side of any one-to-many relationships, before you go to the "many" side. In the "movie" example for the project, add actors and movies first before you update the "MoviesandActors" table.

... using forms

This is more comfortable than the above method, but there are the same restrictions for the order.

Create tables through action queries

Some so-called action queries, like make-table queries or append queries allow you to create tables or update information.