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.