R. Craig Collins >
BCIS 1305/ITSC 1309 >
Access Sample
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 https://youtu.be/2sbgzbuSaNY )
Some database terms:
Note: there are two records with the same info (since middle name was not used)
Often, a good candidate for key field is just to add a sequence number... such as record #1 or record #2 |
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) |
Building the Database using Access (Some screenshots are from older versions, but still applicable)
YouTube video https://youtu.be/2sbgzbuSaNY
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.
Table: Design View, with [...] selected to open an input mask (your fields may vary from those shown)
Close the table [x].
Now we need a form to populate the table.
Make sure you are in 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.
Now it is time to sort our lists, this is done with a query. We begin with the Query Wizard.
For now, we just need a simple query
Include all the fields
from all the tables you wish to use.
Open the Query, and change to Design View
Now, we change the cost criteria to less than $15, and !Run the query.
Once we have run and saved our queries, we can run reports on each query.
See Lab 6 for details...