Microsoft Access Basics
From Extension Collaborative Wiki
Microsoft Access Lessons
How to create a File and Table -- Lesson One
- Click on File and Click on New
- Click on Blank Database
- File Name, enter File name, Ex, example
- Click on Create
- Select Create table by using the wizard, by double left clicking on the Icon
- Select The Business Radio button
- After Select the Business Radio button, notice the Sample Tables, From Sample Table select Mailing list
- :
- The Sample Fields column, select the fields desired
- :
- To select Fields, one field at a time, Click on the Field name and click once on > Button, this will select the field or to select ALL the fields, this can be select by selecting the >> button.
- To remove a Field, on the Column of Fields in my new table, click one on a Field and click once on the < button.
- Click on Next,
- From Table Wizard, Name the table, ex. Mailing list
- Select Yes, set a primary key for me
- Click on Finish
- The Table will be displayed, look like Excel table. Do not enter data, Close the table go to lesson on Steps to create a Form
How to Create Data Entry Form -- Lesson Two
- Click on Forms, under the Objects Column
- Double Left Click on Create form by using the wizard
- Under Tables/Queries, make sure you have the right Table selected
- Under Available Fields, Select all the fields by left clicking on the >> button
- All the Fields will be moved under the Selected Fields, click on Next
- From the Layout, Select Justified and Click on Next
- From Style, Select Blends and Click on Next
- Name your Form, Data Entry Mailing List and Select Open the form to view or enter information and click on Finish
How to Create a Query -- Lesson Three
Click on Queries, under the Objects Column
Double Left Click on Create query by using the wizard
Under Tables/Queries, make sure you have the right Table selected
Under Available Fields, Select all the fields desired by left clicking on the > button
When the Fields desired are moved under the Selected Fields: Click on Next
Give the Query a title, ex, Health and Click on Finish
This will open up the Queried items, Click on the X on upper Right Corner and close the Query Highlight the Query and Click on Design Icon
Under the Comment Field, in the Criteria row, type *health*, this is * and health is the variable we want to query by and end with *
Click on the X and when prompt to save Query, click on Yes
Double click on the Query name and this will Query your Table by your criteria
How to Create a Mailing Label -- Lesson Four
From the Objects column, Click on Reports
Click on Icon that says New
From the New Report Dialog box, Select Label Wizard, and from the same Dialog box, Choose the table or query where the data comes from and click on OK
From the label wizard select the type of Labels used in the office, ex Avery 5160 and click on Next
Select the font and color, I use Arial, font size 8 and click on next
This is important, arrange the Fields like they should print on the label, Ex First name {space} last name
Address
Select the field by click on the > button once, Select the first name field, hit the space bar once and select the last name field and hit the enter key once Select the address and hit the enter key once, Select the City, hit the space bar and select the state, insert a comma and hit the space key once and select the zip and click on next
From the sort wizard select the field you want to sort by, ex, select zip and click the > button once and click on next
Name the label and click on finish
Double Click on the label created and click on File and Print and the labels will be printed
How to Create a List Box -- Lesson Five
( Note: List boxes are used to create pull downs in forms as means to populate the information in the fields.)
From the Objects column, click on Table
Click on Create Table on Design View
Field Name, name it Farm Type and Text for Data Type
Click on the X and Click on Yes to Change changes to the Table, Name your Table, Farm type, this needs to make sense for your database, when prompt is want ms access to select an primary key, Click on No
Double click on new table and enter the parameters, Dry land, Irrigation, and both
Click on the X and this will save changes
Click on the original table health and Click on the Design Icon, Scroll to the last Field name and in blank Field click once and type Farm Type on the data type, click the pull down and click on look up wizard
From the Lookup wizard, Select I want to lookup column to look up the values in a table or Field and click on Next
Select your table , ex Farm type and click on Next
From Lookup wizard select the Fields and click on the > key to move the Field to the Selected Field section and click on Next
On sort or, click on next button and you will see the data on the Lookup table we created and click on Next
Click on wizard, Farm type is good name for lookup column, so enter Farm Type and click on Finish
When prompted to Save, click on yes
From the Objects, Click on Forms and click on your form and click on Design Icon
Locate where you like the new field and click on the Icon that says Field List Scroll down and Left click on Farm Type Field and holding the left mouse down drag and drop on the form where you like for the pull down field to be located
Click on the X and when prompt if like to save the form, click on Yes
How To Create a Calculated Field -- Lesson Six
Go to the table and click on the design view
From the Field Name column, scroll to the bottom and type in the names for the new Fields, ex, Dry Land and Irrigation, the data type for both select numbers
Once the new fields have been added click on the X and when prompt to save changes, click on Yes
From the Object Column, Click on Form and select your form by clicking once and click on the Design Icon
Click at the bottom of the Footer and when get the double arrow, left click and hold down and pull the form down to make it bigger, will need the room to add the new Fields
Go the menu bar and locate the Field List and click on it once and this will display the available fields.
Left Click on Dry Land Field and hold the left mouse button down, drag and drop at the bottom of the form, resize to desirable size
Go back to the Field list and click and drag the irrigated acres field and drop in the form to desired area
Now we want to create a Text box so we can Calculate the Numbers, from the tool bar, select the ab, which is the text box, left click once and move the cursor to the form area, notice a plus sign and ab box at the end of pointer.
Left click on desired area and drag to the right and create a Field in the form
Displayed is a Text Box with number and entry field with wording unbound in it Right Click the Text box with number and left click on Properties
Left click on the Format tab once and to the Right of Caption Type in Total
Click on the X and close and Save Changes
Right Click on the unbound field and left click on Properties and left click once on Data tab
In the Control Source row, towards the end is small icon with 3 dots, left click that once, this will display the Expression builder
From the Expression Builder, click on the equal sign once and that will insert equal sign on the box
From the middle column, find the dry land acres field and double left click it, this will insert it in the box
Now click the + (plus sign) to add the plus sign, at this point you should see =[Dry Land]+
Now from the center column double click the Irrigated acres and this will complete the formula =[Dry Land]+[Irrigated Acres]
Click on OK
Click on X and save the changes
Double click on the form and add number 50 in the Dry Land field and 50 in the Irrigated Field and see the Total change to 100
How To Create Mail Merge With MS Access and MS Word -- Lesson Seven
Open MS Word
From Menu bar click on tools and go to Letters and mailings and from the Dialog Box select Mail Merge
From the Right Panel, Select the document type, in this example I am going to merge to a letter so select Letters
Follow the step by step at the bottom, we are at step 1
Click on Next:Starting document to move to the next step
Notice we are now Step 2 of 6, Under Select Starting document I am selecting Use the current document
Next click on Next:Select recipients
Now lets select the recipients, they are on the ms Access table example we created in the previous lesson
Click on Browse and go to My documents and select the ms Access database
Select the database and click on open
From the select table notice that there are Tables and Queries to merge with the document In this lesson we are going to use Table
From the mail Merge Recipients, this example will illustrate how to query certain records
Scroll to the Comment column and click the pull down and select advance
From the field and sort we need to select our criteria . From the Field Section, click the Pull down and Scroll down and select Comments
From the Comparison field, Select Contains, this will search the records
From the Compare section, type in Cattle and click ok, this will sort all records out that had cattle on the comment section
Now we are on Step 3 or 6, we need to write your letter
Write your letter
From the right Panel, select Address Block
From the Insert Address block Dialog Box, select the desired Block
Notice that as select different once, the preview window displays the address block
Next Select the Greeting line
From the Greeting Line, Select the Greeting Line format.
Step 4 of 6, click on Preview your letter, this will display the greeting block and Address block and letter preview
Step 5 of 6, Select Complete the merge, this will merge all the names with the letter
Step 6 of 6 Letters are ready to be printed
Use the Query Designer to Create Update Query -- Lesson Eight
In This update Query, we are going to update the State Column, the current information is FL and going to change it to Florida. Click on Queries, under the Objects Column
Double Click on Create query in Design View
From the Show Table Dialog Box, Select the table, in this case the table is called Mailing list, and click on Add
Click on close and close the Show Table Dialog Box
Scroll the Available fields and Click on the State Field and holding Down the left mouse button, drag and drop in the Field Section
From the Menu bar, click on the Query and Click on Update Query, a new Item will be displayed under the Field: and Table: heading, this new Item is Update To:
In the Update To: Field type Florida
Click on Query on tool Bar and left Click on Run
A Dialog box will be displayed indicating how many records are going to be changed, Click on Yes The changes have taken place, click on the X and save your query, Name your Query and click on Ok
Go to the Forms and Open your Form and Notice as you move thru the records the State has changed
Change The Tab Order in a Form -- Lesson Nine
Do one of the following: Change the tab order in a form
On the View from menu bar and scroll down and click Tab Order.
Under Section, click the section you want to change.
Do one of the following:
If you want to create your own custom tab order, click the field name for the control you want to move.
Click the selector again and drag the field to the desired location in the list. Click OK.
Click OK again to Save the New Tab Order
