Here you see the Queries view of the database window. To start the Simple Query Wizard, double-click the “Create query by using wizard” option.
The first window asks you which fields you want in your query. Remember the question you are asking “How much time has been worked on each project?” To select a field, first select the table or query the field is in from the Tables/Queries drop-down list.
For this query you need two fields: the Hours Worked field from the Hours table and the Project Description from the Projects table. Click the Tables/Queries option and select the Hours table.
The Available Fields box now displays fields from the Hours table. You can move the field to the Selected Fields box by double-clicking the
field name or by selecting the field name and clicking the right arrow. Double-click the Hours Worked field to select it.
Now you need a field from the Projects table. Select the Projects table from the Tables/Queries option. The contents of the Selected Fields box remains the same, but the contents of the Available Fields box changes to reflect the fields in the
Projects table. Now select the Project Description field by double-clicking it.
Often you will select more fields for a query, but these two will suffice for this query. Click the Next button to see the next window of the Simple Query Wizard.
This window asks you what type of query you want, a 1) detail query or 2) a summary query. A summary query will group your data and perform calculations if you select them. This is the Simple Query Wizard’s strength. Select the Summary option.
When you create a summary query, Access groups records in the way that makes sense according to the relationships defined in the database. In this case, it makes sense to group the Hours Worked values by project. When you select the Summary option, the Summary Options button becomes active. Summary Options enable you to choose how to summarize your data. Click the Summary Options button.
The Summary Options dialog box displays check boxes that allow you to choose how to summarize your data. You can choose the type of calculation that makes sense for the field that you are summarizing,in this case, Sum. Access displays field names on this dialog box according to its analysis of the fields you have selected for the query. Also notice that Access has an option to Count the records in the Hours field. This count is done within the grouping. If you chose this option, Access would calculate how many Hours Worked records are entered for each project. Select the Sum check box for Hours Worked and click the OK button.
You are done with this window of the wizard. Click Next to go to the final window.
In the last window of the wizard, Access suggests a name for the query, which you can change, and asks how you want to see the query: in Datasheet view (Open the query to view information) or in Design view (Modify the query design). It is always a good idea to use a descriptive name for queries, otherwise you may find yourself opening every query to find the one that you want. Click in the text entry box and rename this query Hours by Project. Leave the option, “Open the query to view information” selected.
Click the Finish button to see the results of the query.
Here are the query results. There is one record for each project. The hours spent on each project have been summed and appear in the column called Sum Of Hours, a new field that the query has created.
