R. Craig Collins > Common > How To: Access
How To: Access © R. Craig Collins, 2005-2020
Using Word and Excel for basic Database functions (pdf)
Access Database Theory (pdf) Step by Step creating a database (pdf)
Access Basics, tutorials (pdf)
YouTube on Access https://youtu.be/2sbgzbuSaNY
The following is a very QUICK look at making a database. You should follow this up with Step by Step creating a database (pdf)
The goal is going to be to create a thank you list for gifts received. However, to make this database as flexible as possible, we will separate the gift giver's information from the gift given. The first step is to create the first table, which will be our address book.
Your screen may differ
Enter the information you see below... note that you will right click on the first row after entering First_name and the other info, to make it the primary key/key field. To help you verify information entered is correct, you will also change the field size of State to 2.
Save the table as Address.
In order to get the input mask you see below for the phone number, select the
[...] to the right of Input Mask, and choose the Phone number option. You may
be asked to save the table before you can complete this step.
Now it is time to create a form to input the information into the table.
Select all the fields from the Address table by using the >> to wind
up as you see below.
Make up at least 5 unique records, with names, addresses, etc.
Now, create a table to hold the information about the gifts you received, as
shown below. Note the first line again is First_name, and is
again the primary key/key field (spelling must be identical to the address table
for this field), and note the use of currency.
As before, create a form, and put in one gift from each person in your address book.
Example Craig; car; great; garage; $25,000; and Hyundai of Texas.
In order to use both tables, we need to bind them together based on that identical
common field... we do this with a relationship.
Add both tables, then close the Show Table dialog box. Click on one of the
First_name fields, and drag it over, and drop it on the other First_name field.
In this case, each addressee will only give one gift, so it would be a one
to one relationship. (In a different circumstance, it would probably be a one-to-many
relationship, such as one salesman would have many customers...)
One the relationship is made, you can make the two tables work together using
a query, made with a query wizard.
The resulting query should include everything from both tables.
You can then refine the query to display only gifts that cost more than $10
by clicking the first button under the tool bar to go to Design view, and add
> 10 as the criteria for cost. To run the new query, click the [!].
After saving the changes to this query, you could now use the report query wizard to generate a printable version of the results... now you have experience in all of the basic jobs in Access.