To Temple College Class  logo R. Craig Collins > BCIS 1305/ITSC 1309 > Access Sample

Access Sample © R. Craig Collins, 2005/16

The Birthday loot:
Barney gave me a toaster, cost $10 from HEB
Betty gave me a DVR, cost $50 from Target, and
Wilma gave me a car, cost $50,000 from Central Texas BMW.

I need to write some thank you notes, but I would like to be able to re-task this information for later.

Option 1, great at thank you notes: I might enter the info to a word processing mail merge where info would be kept in a table, but it could be hard to pull just some info out.

Option 2, I could build a spreadsheet table to record the information, and spreadsheets are great at sorting; but terrible at making a 'thank you' note...

...sounds like a job for a database.
(After reading this document, you can refer to YouTube video )

Some database terms:
Field (the equivalent of a column in a table), a group of related characters, such as 'last name' or 'zip code.'
Record (the equivalent of a row in a table), a group of related fields, usually describing an individual.

Example Last Name First Name Middle Initial Address
Me Collins Robert C 555 Main, Anywhere, USA
My Dad Collins Robert C 555 Main, Anywhere, USA

Note: there are two records with the same info (since middle name was not used)
Primary Key, a key field, this uniquely identifies ONE individual.

Example Last Name First Name Middle Initial Address ID No. (key)
Me Collins Robert C 555 Main, Anywhere, USA 555-12-1212
My Dad Collins Robert C 555 Main, Anywhere, USA 555-12-1213

Often, a good candidate for key field is just to add a sequence number... such as record #1 or record #2

File, a series of related records is called a file, such as all the College 'Address' records table is a file.
Database, a series of related files is a database.

If you segregate your data, to protect who can assess certain parts, you now have a relational database...
In order to have a working relational database each database must share a field, often the key field.

So I need to build my data dictionary, where I describe my birthday loot.
Here are the fields I want to use so I can write a great thank you,
and since I am planning ahead, I also will grab some info so I can re-task the data later:
name, gift, room (where I'll keep the gift), adjective (to describe the gift), cost, and store where purchased.

That way I could write the following letter

Planning a generic letter that a database could personalize

Dear [name],
thanks for the [gift].
I will think about you
every time I go into the [room].
After being run through a database, it will replace each field name with information from ONE record, letting me print three 'personalized' letters.
Dear Barney,
thanks for the toaster. I will think about you
every time I go into the kitchen.

Dear Betty,
thanks for the DVR. I will think about you
every time I go into the Living room.

Dear Wilma,
thanks for the car. I will think about you
every time I go into the garage.

Plus I could re-task this data to also generate the following letter:

Dear Target,
I recently got a toaster.
I believe it cost $10.
I'd like to get a refund.

Same info, different uses :)

But, I sure don't want to return the BMW... so I only want to generate a return letter, if certain criteria are met, such as Gift Cost < $15.

More database terms:

Form, a method of adding records to a table (input)
Query, a method of selecting records (process), and
Report, a method of printing relevant parts of records (output)

Building the Database using Access (Some screenshots are from older versions, but still applicable)
YouTube video

After creating a blank database and saving on your O:\ Drive, choose the triangle under View, and switch to Design View

Upon switching views, you will be prompted to save the table before proceeding.
Access Save table

Table: Design View, with [...] selected to open an input mask (your fields may vary from those shown)
Table, Design View

Close the table [x].

Now we need a form to populate the table.
Starting the Form Wizard

Forw Wizard

Make sure you are in Form view
Form View

If you have multiple tables, you must make a relationship. Start with Database Tools, and choose Relationships

Add your tables, then drag the common field of one, and drop it on the other to create the relationship.

An example would be to have an address table, and a gift table.
If we have a Name field in each one, we can make the relationship, and segregate the date until it needs to be temporarily combined.
The school does this when it sends a bill or grades, the appropriate tables are temporarily joined, and the report is printed.
But only the people with certain permission can see a students grades, or address, or amount owed, etc.
Making the Relationship

Now it is time to sort our lists, this is done with a query. We begin with the Query Wizard.
Query Wizard

For now, we just need a simple query

Include all the fields from all the tables you wish to use.
Query Wizard

Open the Query, and change to Design View
Query, Design View

Now, we change the cost criteria to less than $15, and !Run the query.
Query Criteria

Once we have run and saved our queries, we can run reports on each query.

See Lab 6 for details...

YouTube video