MS Access Select - Where Query

• Open the HealthyGym database by double clicking on it
• The window will show all the tables in the database. Click on 'queries' under 'Objects; in the left hand side of the window.
• Now, click on 'Open Query in Design View'
• Select 'Gymmembers' in the Show Table pop up window. Click on 'Add'. The Gymmembers table will be added to the blank top half of the Query design area. Click on Close.
• On the bottom half of the design screen, select 'MemberName' next to 'field:'. Select 'MemberAddress' on the next column. Select 'MemberGender' in the third column.
• You will see a check mark in a box every column next to 'Show:'. Now, click on this checkmark in the MemberGender column. The box under MemberGender should not contain a check mark.
• Now, in the same column, next to 'Where:' enter F in single quotes under 'MemberGender'.
• Now, click on the run query - exclamation '!' icon on the top toolbar
• You will get the same results as above
• Close this window (answer 'no' when you are asked whether you want to save this query).

Screen Shot 5h: SQL WHERE Query Design Using Access GUI

In our example, we used the equals '=' symbol to specify what we were searching for (MemberGender = 'F'. We may also use the following operators after the WHERE clause:

Operator	Description	Example
=	All data that Equals value	SELECT * from MemberPayment WHERE PaymentAmount=50
<>	All data that does not equal value	SELECT * from MemberPayment WHERE PaymentAmount <> 50
>	All data that is Greater than the value	SELECT * from GymMembers WHERE MemberExpire > 12/1/2006
<	All data that is less than the value	SELECT * from GymMembers WHERE MemberExpire < 12/1/2006
>=	All Data that is greater than or Equal to the value	SELECT * from GymMembers WHERE MemberExpire >= 12/1/2006
<=	All data that is Less than or equal to the value	SELECT * from GymMembers WHERE MemberExpire <= 12/1/2006
AND, OR	Used to Separate values being compared	SELECT * from GymService WHERE ServiceCost = 20 OR ServiceCost = 50
BETWEEN	Between two values including those two values	SELECT * FROM GymMembers WHERE MemberExpire Between 12/1/2006 And 2/1/2007
LIKE	Used to search for a string of alphabets, numbers, or other characters	SELECT * from GymMembers WHERE MemberName LIKE 'Jackie Smyth' OR MemberName LIKE 'Sid Harris'
IN	a Used to search for a set of values	SELECT * from GymMembers WHERE MemberID IN (1,5,10,15,20)

The WHERE clause is one of the most useful features of the language. Try out all the examples in the above table in MS Access and see what you get (See the notes at the end of the chapter first). The =, > and < signs in between keywords are used mainly for numbers and dates while the LIKE clause is used for comparing words and other character strings. We can use the '=' operator for comparing short strings but using LIKE makes more sense. Quotes should only be used for strings and never for numbers or dates in original SQL. The AND and OR keywords may be used to connect different values for comparison in the following way:

·          SELECT * from GymMembers where Expirydate <= 11-01-2006 AND MemberGender LIKE 'F'

Get me all the female members whose membership expires on or before 1 November, 2006.

·          SELECT MemberID, PaymentExpire from MemberPayment where PaymentAmount>50 AND PaymentActivate>=10-1-2006

Get me the IDs of the members and membership expiry dates of all Super and Plus memberships that are set to start after 1 October, 2006.

·          SELECT * from EnrollmentScheme where SchemeName IN ('RegularMonthly','Quarterly','Halfyearly','Annual')

Get me information about the RegularMonthly, Quartery, Anuual, and HalfYearly enrollment schemes.

·          SELECT * from GymMembers WHERE (MemberName LIKE 'Sunny Wong' OR MemberName LIKE 'Chloe Gerard') AND MemberGender LIKE 'F'

Get me the information of a female member whose name is either Sunny Wong or Chloe Gerard. In the above statement, the clauses within brackets are performed first and the result set of this query is examined for the conditions outside the brackets. That is, Sunny Wong's and Chloe Gerard's records are picked up first and then the member gender columns of these two are examined to see who is the woman of the two.

As illustrated above, we may use the LIKE operator to compare strings. However, most of the time, we only know part of the string we are searching for. Can SQL get us information about such strings? The '%' keyword is used as a wild card in matching strings in SQL. One of the shortcomings of our database is the fact that our GymMember table contains both the first and last names in the same column. How would someone look for all the members whose last names begin with an 'A'? Look at the following query

Select * from GymMembers where MemberName LIKE '% A%'

What we do know is that the first name of each member is followed by a single space ' ' and then the last name. The above command basically says 'Give me the information of all members whose name contains a single space followed by an A'. Run the query in MS access. You will get something like the following:

Screen Shot 5i: Result set from SQL LIKE using Wildcards

Notes:

• When you enter dates in MS Access use the hash '#' symbol around the dates. For example, 12-1-2006 in the above query should be changed to #12-1-2006#
• The wildcard character in MS Access is not %. Use * instead. For example, our wildcard example should be entered as "Select * from GymMembers where MemberName LIKE '* A*' "
• Always cut and paste the SQL commands so that the entire command is on a single line -there should be no line breaks
• In some versions of SQL the <> operator may be written as !=

In this chapter, we learned SQL basics. We can now get information out of a database. In the next chapter, we will see how to actually put information into the database or change existing information.