Access Project
DATABASE PROJECT TIPS
BEGIN WITH THE END IN MIND
Begin this project by writing down what you want out of your application. Is it a nice sales report? A
query that finds a specific record from among hundreds of records? Or do you want your system to
produce customer address labels? Start there and work backwards! Determine the fields that will be
required for each report and query.
PROMPTED QUERIES
What is a Query? A query is simply a question that you ask your data.
How many homes are currently for sale in my zip code? That’s a query.
How much money did we spend on office supplies last quarter? That’s a query.
Even Google searches are queries! You are asking Google to return a list (results) for all of the web
pages that contain the words in your search box.
Queries can become more useful, if you prompt the user for a Minimum or Maximum Value
Take, for example, a used car database. Here is the VEHICLES table:
A potential buyer might be interested in seeing all of the vehicles in a specific price range: Less than
$2500 (as in budget wheels).
SELECT *
FROM VEHICLES
WHERE SellingPrice < 2500;
You could hard-code < 2500 in the query, but what if the user really only has $2,000 to spend?
That is where the power of a prompted query comes in!
In Query by Example (Design) view enter the following in the criteria box for the [SellingPrice] field:
< [Enter the Maximum Price in Dollars: ]
Now the user controls what their Maximum Desired Price is!
PROMPTED QUERY DATA TYPES
You can prompt for numbers, text strings and dates.
Numbers are easy to prompt for. You are usually looking for something equal to, greater than, or less
than a specific number (or a number between two other numbers).
Text strings work in a similar fashion for strings that are equal to the input.
For example - say you just met someone at your workplace with the last name of Kahn. You then want
to send him an e-mail message to invite him to lunch. You could write a query and have it prompt the
Employee Address Database for the last name (in case you want to re-use this query in the future with
another last name).
Extending text queries with the LIKE operator
What if you are a terrible speller? You just met a customer and all you can remember is their last name
started with a “G” – a query that prompts for the ENTIRE last name is not going to help you find them.
That is where the LIKE operator comes in: You can prompt for:
Like [Enter the first few Letters of the Customer's Last Name: ] & "*"
When you run the query, you will see:
If you enter “G” and click OK you will get all of the customers with their Last Names starting with “G”:
You can use Date values in the criteria box. For example,
[Enter Date of Hire: ]
You can use multiple parameters in the criteria box. For example,
Between [Enter the start date: ] And [Enter the end date: ]
will generate two prompts when you run the query.
SECURING YOUR DATABASE WITH A PASSWORD
This should be done near the end of your project. That way you are not prompted each time you open
your database during the development phase. Once you are ready to turn in your database, do the
following:
In Access, you have to open the file in “Exclusive” mode. To do that, start Access and AVOID THE
TEMPTATION TO CLICK ON A RECENT FILE LINK! Instead, click on the Open Other Files option as shown
below:
Then, BROWSE your computer as follows:
Locate the file on your computer, then click on the Open drop-down dialog box (as shown below) and
select Open Exclusive:
In Access 2013, click on File, Encrypt with Password as follows:
IMPORTANT NOTICE:
Be sure to set your password to “password” (all lower case) for this project ONLY. That
way your instructor will know what your database password is and will be able to grade
this assignment.
HOWEVER – IN THE REAL WORLD YOU SHOULD NEVER, EVER SET
YOUR PASSWORD TO:
password Letmein
Password logmein
P@ssw0rd football
Password1 baseball
12345678 Monkey
Abcdefgh Batman
abcd1234 trustno1
“your name” + 1 “your username” + 1
Or anything that is easy to guess, like your license plate number, your birthday,
your significant other’s name, etc.