ACCESSCreating Simple QueriesQueries are a powerful way to find multiple records which meet one condition or multiple conditions. You can set up the query to list the found records in a certain order and to not print certain fields even if they have been used to search for the records. To create a query you need to click on the Queries section of the database window and click the New button to create a new query.
We are going to use the Design View to create our query but you can use the Wizard to do the same job and follow the instructions which lead you through the process. Make sure Design View is selected from the New Query window as below and then click the OK button to start designing your query.
You now need to select the source of data for your query from the window which is shown below, in our case the Employees table. Click the appropriate table in the window which appears and then click the Add button to add it to the area at the top of the query. It is also possible to run queries on the results of queries and you could choose an existing query from the Queries section of the window below if necessary.
You should now be able to see a window as below with a box containing the fields in your table at the top of window and a datasheet type area at the bottom where you can enter your criteria. The query below searches for everyone with a salary greater than £9,000, prints only the First Name, Surname and Department and sorts by ascending Date of Birth. Note that the ticks are omitted from the ‘show’ line for Date of Birth and Salary as these fields are not to be printed, even though they are necessary for the design of this query.
If you wanted to see the Structured Query Language (SQL) which is behind the query you have created you can click on the View drop-down menu and choose SQL View. The SQL for the query we have created is shown below: SELECT
Employees.[First name], Employees.Surname, Employees.Department When you have finished designing your query, click on the floppy disk Save icon and a small window will appear for you to enter a meaningful name for your query, this is shown below.
Click on the OK button and you will see the Queries section of the database window with your new query listed. To run the query, click on it and then click the Open button as you would to open a table or form or just double-click the query name. You can see the results of the query we have created below.
When you have finished with the results from the query, simply click the cross in the top right corner of the window to close it and you will be returned to your database window. Click here to return to the Access index. |
