databasedev.co.uk - database solutions and downloads for microsoft access

Query Basics

Microsoft Access Database Queries:

When working in a database, most users will only need a certain group of records at any one time. Some may want to look at sales transactions for the previous week, while others may only need to look at a list of addresses in a particular area.

Whatever the reason, database programs have the ability to search for any record a user needs and manipulate the data in many different ways. These are accomplished by creating a QUERY.

Once tables have been established inside of a database, a person can develop a query to select a group of fields from those tables, select only records that adhere to a specific set of criteria, and ready those records for use in a report.

The user can take advantage of using WILDCARD CHARACTERS or COMPARISON OPERATORS to assist in finding close matches. The following two tables show the symbols used for each of these, and examples of each.

Microsoft Access Wildcard Characters

WILDCARD CHARACTERS
SYMBOL AND USAGE WHAT IT DOES SAMPLE DATA
M* Finds all records that begin with M, with no limit on extra characters Murphy, McCall, Martin
*M Finds all records that end with M, with no limit on extra characters Tim, mum, I'm
*M* Contains the letter M anywhere within it Time, McDonalds, gemstone
? Single character that can represent any other character F?nd: Find, Fund, Fend, Fond
?? Two characters that can represent any two consecutive characters F??d: Food, Ford, Fred

Microsoft Access Comparison Operators

COMPARISON OPERATORS
SYMBOL WHAT IT REPRESENTS
> Greater Than
< Less Than
>= Greater Than or Equal To
<= Less Than or Equal To
<> Not Equal To

(NOTE: Greater Than or Equal To, Less Than or Equal To, and Not Equal To must be typed as shown, since there are no single characters to represent them)

Here is the result when we look for all records in the "Courses" table where "Word" is anywhere within the class name. The criteria would be written as *Word*.

Results of wildcard query

Here is the result when we take the same table and find all classes that are less than or equal to 2.0 hours in length. The criteria would be written as <=2.0

Results of comparison query