MS Access Select 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 'MemberAddress' next to 'field:'. In the next column, select 'MemberName'
• 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 5c: SQL Query Design Using Access GUI

Compare the process above with typing an SQL command. There may be little difference at this point, but they add up as the queries get more and more complicated.

To issue an SQL command that performs the same query, click on the 'Design' icon (set square) on the top toolbar in the Query section. Close the 'Show Table' dialog box without adding any tables. Select the 'SQL' icon on the top left hand side of the toolbar. Now, enter 'SELECT MemberAddress, MemberName from GymMembers' and click on the run query – exclamation point '!' icon. You will see the results of screenshot 5b again. Throughout this tutorial, you may type all the queries we use into Access in this way and run them to see the results.

Now that we have the basic SELECT query taken care of, let us try something new. When we need all the columns in a table in the same order they appear within it, there is really no need to specify each column's name in the SQL command. We may use the asterisk (*) symbol instead. This symbol stands for 'all columns' when used in this context

Select * from GymMembers

Screen Shot 5d: Result of SELECT * from GymMembers

If we want a list of members and their addresses in alphabetical order, we could use the ORDER BY clause after the SELECT statement

SELECT MemberName, MemberAddress from GymMembers ORDER BY MemberName

Screen Shot 5e: Result of ORDER BY query

The actual syntax for the ORDER BY clause is as follows

ORDER BY COLUMN NAME1 ASC, COLUMN NAME2 DESC, ...

In other words, we can order first by one column, then by the next column and so on. If our table had separate columns for first and last names, we could order first by last name and then by first name. Specifying the letters 'ASC' or nothing at all next to the column name sorts the column in ascending order, specifying 'DESC', on the other hand, will sort it in descending order. Run the following query in MS Access and see the result.

SELECT MemberGender, MemberName from GymMembers ORDER BY MemberGender, MemberName DESC

Sometimes, there is a need to leave out duplicates when we get an answer to a question. For example, we need to know which of the enrollment schemes have been frequently subscribed to and which ones have few or none at all. Obviously, the MemberPayment table has as many entries for a scheme as there are members who have opted for the scheme. Ten members may have opted for scheme 1, and five for scheme 2 in which case 1 will appear ten times and 2 will appear five times. We only want a list of the schemes in use; we don't want to see duplicates.

SELECT DISTINCT SchemeID from MemberPayment ORDER BY SchemeID

Running the above command will produce the following output

Fig 5f: Result of query using the DISTINCT clause

We can clearly see that only schemes 1, 2, 3, 4, 5, 9, and 10 have been used. Run the same query without using the distinct keyword and see what you get.

Say, you have a special promotion for women and you want to send flyers to all female members. How would you get a listing of female members only and their addresses? SQL offers the 'WHERE' clause to help you do this.

SELECT MemberName, MemberAddress from GymMembers WHERE MemberGender='F'

The result set looks like this:

Screen Shot 5g: SELECT - WHERE Query Results

Basically, the ‘where’ clause made the command go to the database and grab all the rows of information where the column MemberGender contained 'F'. Now, let us try doing the same thing using the MS Access GUI