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:
- Retrieve or select information.
- Modify or update information in some record (like address changes of customers).
- Delete records.
- Insert a record (like a new customer).
- Sort the records according to some criteria (fields).
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
| Filters | Queries |
| --- | are saved objects |
| show all fields | may 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:
- Text: (up to 255 letters).
- Number.
- Date/Time.
- Currency.
- AutoNumber (Access automatically adds a number to every newly created record).
- Yes/No.
- OLE object (OLE means Object Linking and Embedding. Could be a Excel File or a Word file, or whatever ...).
- Hyperlink.
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.