Creating queries Introduction What is a query? A query is a sorted extraction of records from one or more database tables. This selection is performed according to criteria you have specified. An example of query could be: "Who are all the suppliers in London?" or "Who are all the clients that are more that 35 years old and who did not come for the last 3 years?" Examples countless, and you can create as many queries as you want. A list of useful queries is actually pre-defined in the demo version. You have to get used to queries, before creating some on your own. Once the query is executed, the result can be printed or serves as a selection for a mailing. It can also be exported to a file and be used in other softwares. Queries are defined in a language called SQL language. Of course, you don't need to know this language to create your own queries. A wizard will assist you in creating your own queries. Of course, if you already know the SQL language, you can also write your queries directly with it, which will increase even further your investigation possibilities. Using pre-defined queries To perform one of the pre-defined queries, click on the Queries option in the file selection panel: Once the file is opened, the table of queries and their descriptions is displayed on the screen. Select for example the Patient distribution. This query shows the number of patients for each city. To perform the query, double click with the mouse on the relevant name in the list or select Execute in the file menu. Once the query is executed, the result is displayed in the window. As it is the case for other files, the data may be manipulated and you may thus sort the rows, re-arrange the columns, print, export the result etc… WARNING : Any record that is removed from a query will also be deleted from the main file (database table)! So do not forget that query results are simply selections of files records . The number indicated in the top of right hand-corner of the window shows the number of rows in the query. Exporting the result to Microsoft Excel To export the result to Microsoft Excel (if Excel is properly installed on your computer), you just need to click on the corresponding button in the toolbar: Excel is launched and the data are transferred to a new spreadsheet. Any type of manipulations is then possible, sorting of information, performing graphs etc…   Creating a new query In this section you will learn how to create new query. A simple example is used so that the principle can be easily understood .You will have ample time to perform others complex queries later! In our example, we want to display the list of all the sells price is between 200 € et 400 €. Check that you are already in the query screen and click on New to launch the wizard. Once the wizard opened, click on Next and select the option Answer questions of the wizard. Click once again on the Next Button, Select the items table from the list, since it is the one, which will be used for the selection. Click on Next. Select then the relevant fields amongst all those provided by the software to perform the query: Select the field, to choose the order in which it will be sorted : We need to select the Selection criteria, In fact, the query could be executed as is at this point. However, if it was, all the records of the items file would be. Click on the Criteria selection button to proceed : And specify the series of relevant criteria, in our chosen example, the sells prices ranges between 200 and 400 Euros : The selection code appears in the main window. It is a SQL code corresponding to the specified criteria. As the present query is rather simple, the SQL code is fairly understandable Click a last time on Next to assign a name to the query and save it : When you click on Finish to add the new query to the list of existing queries. The query creation process is now complete. To view the query’s result, simply double click in the list to execute it : The result is displayed in a new window : Modify a query If you later want to modify the query (for example to obtain the list of sells whose price ranges between 200 € to 600 €) without having to start the operation all over again. To do so, simply open the query from the list by clicking on Consult from the toolbar : The query code appears and you can then modify its content. If the modifications are complex to modify, it can be faster to launch the wizard again.