Instructor: Robin Wood - robin 'at' wctrain dot net
Marinas and Workorders by Technician
Launch Access
At the prompt select Blank Access database and click OK.
Select the Save in location for the database, give the database a name, and click Create.
At the Objects dialog box, double click Create table in Design View.
Enter the desired field names, select the data type, give the field a description, and and complete the appropriate properties in the General tab. Repeat this process for each field.
To set the Primary Key of the table, right click the desired field name and select Primary Key. The primary key of a table must be unique. Often when collecting personal data a social security number or telephone number will be used as a primary key. An ID field may be created with the data type set to AutoNumber if no unique data is available.
To save the table, click the Save icon 
, type the table name and click OK. 
Click the Open icon 
, navigate to the database file and click Open or click File > Open.
Open a Table
After opening the database, double click the desired table to open it.
Alternatively you can click the database table once and click the Open 
icon or right click the desired table and select Open from the menu.
This will open the table in datasheet view. Here you can enter or edit existing data.
If you need to edit the table structure you can switch to design view by clicking the View icon 
. The View icon will allow you to switch between design and datasheet view 
.
Creating AutoFormsClicking the New Object drop down arrow will allow the user to quickly create forms used to input data. Select AutoForm from the drop down and select AutoForm.
This form can be used to input, edit, and view data.
Introduction to Queries (external web site)
Querying is the most common method used to retrieve data. You can use a query to essentially ask Access a question about your database. For example you may want to find out how many records have a certain zip code or which records owe more than a certain amount.
To create a query select the table that you want to query in the object dialog box. Click the new object icon drop down arrow and select Query from the menu. Select Design View and click OK.
The query grid will appear. At the top of the window you should see a table box with a list of fields, the bottom of the screen will contain a blank grid. I would recommend that you maximize the screen in order to see all of the objects. Click the Maximize button in the upper right corner of the Window.
Resize the query window by moving your mouse pointer just below the horizontal scroll bar in the middle of the query window. An arrow should appear (see image below), hold down your left mouse button and drag the grid down about 2 inches.
Resize the Query Field list by moving your mouse pointer to the bottom border of the field list window. Hold down your left mouse button and drag the window down when the double headed arrow appears.
After you resize the query window your screen should look similar to the image to the left. The query field list displays all fields in your table. You can add fields to the grid below by double clicking the desired field name.
Add all of the fields that you want to display in the query. Once the fields are added to the grid you can add the criteria. In the example to the left I added two fields, name and zip code. To find all records in a certain zip code, type the desired zip code in the Criteria box below the zip code field as illustrated in the example to the left.
To run the query, click the Run icon 
in the query toolbar at the top of the screen. The results will display. These results can then be printed (File>Print) or saved (File>Save). To return to the query grid click the Design View icon 
in the query toolbar.
You can clear the query grid in Design View by clicking Edit > Clear Grid.
Removing a Field from the Grid If you accidentally add a field to the query or you just want to remove a field from the query hover your mouse pointer over the desired field name. A down arrow will appear, click your left mouse button and press CTRL+X (Cut) to remove the field from the grid.
Adding Additional Tables to the QueryYou can query on more than one table. By default Access will only show the table that was selected when you initially selected Query from the New Object icon. Add additional tables by right clicking on a gray area (next to the field list) and selecting Show Table... From the menu that appears click the desired table and click Add.
Right click the field list title that you want to remove and select Remove table.
Type the desired criteria exactly as you want it to appear into the criteria box. Note: You cannot make typos when you're using criteria, when you make a typo Access will not return the correct records.
You may want to find records that are greater than a certain amount. In the criteria box type > and the amount. For example >100.
You may want to find records that are less than a certain amount. In the criteria box type < and the amount. For example <100.
Wildcards are useful when you want to find partial matches. For example you want to find all records that begin with the letter F, all records that contain the word paving, or when you're unsure of the spelling of a record. Wildcards are used to perform this type of query and there are two wildcards in Access a * and a ?. The * (asterisk) is used to represent multiple characters, a ? (question mark) is used to represent a single character.
In order to run a query using wildcards you must use the word LIKE in the criteria. For example we want to find all records that the City begins with the letter F, the criteria would be: like F* (note: quotation marks will be added around F*)
Let's say we want to find all residents who live on a particular street (North or South Main). The address field also contains the house number and the word Street. The criteria to find all residents who live on North or South Main would look like this: Like *Main*.
Update QueryYou may need to conduct a mass update of records in an access database. For example, zip codes may change for a certain section of the city. This is especially useful if you add a new field to the table and you need to quickly populate the field with default data. To do this you can perform an Update Query. To access the update query click Query from the menu and select Update Query.
The query grid will change and a Update To row will appear above the criteria row. You can either update all records in the table or select records using criteria. In the example below the zip code will be updated to 94599 for any records that contain the word "main" in the address field. To run the query click the Run icon . 
To exit Update query mode click Query > Select Query from the menu.
Calculated QueriesYou may need to perform mathematical calculations on two or more fields in a table. To do this add all desired fields to the query grid. Right click in the first blank column in the query grid (see image to the right) and select Zoom from the menu.
In the zoom box type the desired formula using the following format:
field title: [field_name]math operator[field_name]
Field title: the title that appears at the top of the new column. The field title must be followed by a colon (:).
Field name: Field names must be surrounded with brackets []. You must use a field name that exists in your table. It is best to copy the field name from the field list exactly. If you make a typographical error on the field name the query will not run properly and you will be prompted enter a parameter value when you run the query.
Math operator: Math operators are the same as Excel:
+ Addition
- Subtraction
* Multiplication
/ Division
To run the query click the Run icon
Complete tutorial from Florida Gulf Coast University
Access 2000 Intermediate