Sum data by using a query

Enter the sample data manually

1. On the Create tab, in the Tables group, click Table.

   Access adds a new, blank table to your database.

   Note: You do not need to follow this step if you open a new, blank database, but you will need to follow it whenever you need to add a table to the database.

2. Double-click the first cell in the header row and enter the name of the field in the sample table.

   By default, Access denotes blank fields in the header row with the text Add New Field, like so:

   

3. Use the arrow keys to move to the next blank header cell , and type the second field name (you can also press TAB or double-click the new cell). Repeat this step until you enter all field names.

4. Enter the data in the sample table.

   As you enter the data, Access infers a data type for each field. If you are new to relational databases, you should set a specific data type, such as Number, Text, or Date/Time, for each of the fields in your tables. Setting the data type helps ensure accurate data entry and also helps prevent mistakes, such as using a telephone number in a calculation. For these sample tables, you should let Access infer the data type.

5. When you finish entering the data, click Save.

   Keyboard shortcut  Press CTRL+S.

   The Save As dialog box appears.

6. In the Table Name box, enter the name of the sample table, and then click OK.

   You use the name of each sample table because the queries in the how-to sections use those names.

7. Repeat these steps until you create each of the sample tables listed at the beginning of this section.

If you do not want to enter the data manually, follow the next steps to copy the data to a spreadsheet file, and then import the data from the spreadsheet file into Access.

Create the sample worksheets

1. Start your spreadsheet program and create a new, blank file. If you use Excel, it creates a new, blank workbook by default.

2. Copy the first sample table provided above and paste it into the first worksheet, starting at the first cell.

3. Using the technique provided by your spreadsheet program, rename the worksheet. Give the worksheet the same name as the sample table. For example, if the sample table is named Categories, give your worksheet the same name.

4. Repeat steps 2 and 3, copying each sample table to a blank worksheet and renaming the worksheet.

   Note: You might need to add worksheets to your spreadsheet file. For information on doing that task, see the help for your spreadsheet program.

5. Save the workbook to a convenient location on your computer or your network, and go to the next set of steps.

Create database tables from the worksheets

1. On the External Data tab, in the Import & Link group, click New Data Source > From File > Excel.

   The Get External Data - Excel Spreadsheet dialog box appears.

2. Click Browse, open the spreadsheet file that you created in the previous steps, and then click OK.

   The Import Spreadsheet Wizard starts.

3. By default, the wizard selects the first worksheet in the workbook (the Customers worksheet, if you followed the steps in the previous section), and data from the worksheet appears in the lower section of the wizard page. Click Next.

4. On the next page of the wizard, click First row contains column headings, and then click Next.

5. Optionally, on the next page, use the text boxes and lists under Field Options to change field names and data types or to omit fields from the import operation. Otherwise, click Next.

6. Leave the Let Access add primary key option selected, and click Next.

7. By default, Access applies the name of the worksheet to your new table. Accept the name or enter another name, and then click Finish.

8. Repeats steps 1 through 7 until you have created a table from each worksheet in the workbook.

Rename the primary key fields

1. In the Navigation Pane, right-click each of the tables that you created in the previous steps and click Design View.

2. For each table, locate the primary key field. By default, Access names each field ID.

3. In the Field Name column for each primary key field, add the name of the table.

   For example, you would rename the ID field for the Categories table to "Category ID" and the field for the Orders table to "Order ID." For the Order Details table, rename the field to "Detail ID." For the Products table, rename the field to "Product ID."

4. Save your changes.

Whenever the sample tables appear in this article, they include the primary key field, and the field is renamed as described by using the preceding steps.

Top of Page

Create a basic select query

1. On the Create tab, in the Queries group, click Query Design.

2. Double-click the table or tables that you want to use in your query.

   The selected table or tables appear as windows in the upper section of the query designer.

3. Double-click the table fields that you want to use in your query.

   You can include fields that contain descriptive data, such as names and descriptions, but you must include a field that contains numeric or currency data.

   Each field appears in a cell in the design grid.

4. Click Run to run the query.

   The query result set appears in Datasheet view.

5. Optionally, switch to Design view and adjust your query. To do so, right-click the document tab for the query and click Design View. You can then adjust the query, as needed, by adding or removing table fields. To remove a field, select the column in the design grid and press DELETE.

6. Save your query.

Add a Total row

1. Make sure that your query is open in Datasheet view. To do so, right-click the document tab for the query and click Datasheet View.

   -or-

   In the Navigation Pane, double-click the query. This runs the query and loads the results into a datasheet.

2. On the Home tab, in the Records group, click Totals.

   A new Total row appears in your datasheet.

3. In the Total row, click the cell in the field that you want to sum, and then select Sum from the list.

Hide a Total row

• On the Home tab, in the Records group, click Totals.

For more information about using a Total row, see the article Display column totals in a datasheet.

Top of Page

Calculate a simple grand total

1. On the Create tab, in the Queries group, click Query Design.

2. Double-click the table that you want to use in your query.

   If you use the sample data, double-click the Orders table.

   The table appears in a window in the upper section of the query designer.

3. Double-click the field that you want to sum. Make sure that the field is set to either the Number or Currency data type. If you try to sum values in non-numeric fields, such as a Text field, Access displays the Data type mismatch in criteria expression error message when you try to run the query.

   If you use the sample data, double-click the Shipping Fee column.

   You can add additional numeric fields to the grid if you want to calculate grand totals for those fields. A totals query can calculate grand totals for more than one column.

4. On the Query Design tab, in the Show/Hide group, click Totals.

   The Total row appears in the design grid and Group By appears in the cell in the Shipping Fee column.

5. Change the value in the cell in the Total row to Sum.

6. Click Run to run the query and display the results in Datasheet view.

   Tip: Note that Access appends "SumOf" to the beginning of the name of the field that you sum. To change the column heading to something more meaningful, such as Total Shipping, switch back to Design view, and click in the Field row of the Shipping Fee column in the design grid. Place the cursor next to Shipping Fee and type the words Total Shipping, followed by a colon, like so: Total Shipping: Shipping Fee.

7. Optionally, save the query and close it.

Calculate a grand total that excludes some records

1. On the Create tab, in the Queries group, click Query Design.

2. Double-click the Order table and Order Details table.

3. Add the Order Date field from the Orders table to the first column in the query design grid.

4. In the Criteria row of the first column, type Date() -1. That expression excludes the current day's records from the calculated total.

5. Next, create the column that calculates the sales amount for each transaction. Type the following expression in the Field row of the second column in the grid:

   Total Sales Value: (1-[Order Details].[Discount]/100)*([Order Details].[Unit Price]*[Order Details].[Quantity])

   Make sure your expression references fields set to the Number or Currency data types. If your expression refers to fields set to other data types, Access displays the message Data type mismatch in criteria expression when you try to run the query.

6. On the Query Design tab, in the Show/Hide group, click Totals.

   The Total row appears in the design grid and Group By appears in the first and second columns.

7. In the second column, change the value in the cell of the Total row to Sum. The Sum function adds the individual sales figures.

8. Click Run to run the query and display the results in Datasheet view.

9. Save the query as Daily Sales.

   Note: The next time that you open the query in Design view, you might notice a slight change in the values specified in the Field and Total rows of the Total Sales Value column. The expression appears enclosed inside the Sum function, and the Total row displays Expression instead of Sum.

   For example, if you use the sample data and create the query (as shown in the previous steps), you see:

   Total Sales Value: Sum((1-[Order Details].Discount/100)*([Order Details].Unitprice*[Order Details].Quantity))

Top of Page

Create the totals query

1. On the Create tab, in the Queries group, click Query Design.

2. Select the tables that you want to work with, and then click Add.

   Each table appears as a window in the upper section of the query designer.

   If you use the sample tables listed previously, you add the Products and Order Details tables.

3. Double-click the table fields that you want to use in your query.

   As a rule, you add only the group field and the value field to the query. However, you can use a calculation instead of a value field — the next steps explain how to do so.

   1. Add the Category field from the Products table to the design grid.

   2. Create the column that calculates the sales amount for each transaction by typing the following expression in the second column in the grid:

      Total Sales Value: (1-[Order Details].[Discount]/100)*([Order Details].[Unit Price]*[Order Details].[Quantity])

      Make sure that the fields that you reference in the expression are of the Number or Currency data types. If you reference fields of other data types, Access displays the error message Data type mismatch in criteria expression when you try to switch to Datasheet view.

   3. On the Query Design tab, in the Show/Hide group, click Totals.

      The Total row appears in the design grid, and in that row, Group By appears in the first and second columns.

   4. In the second column, change the value in the Total row to Sum. The Sum function adds the individual sales figures.

   5. Click Run to run the query and display the results in Datasheet view.

   6. Keep the query open for use in the next section.

      Use criteria with a totals query

      The query that you created in the previous section includes all the records in the underlying tables. It does not exclude any order when calculating the totals, and it displays the totals for all categories.

      If you need to exclude some records, you can add criteria to the query. For example, you can ignore transactions that are less than $100 or calculate totals for only some of your product categories. The steps in this section explain how to use three types of criteria:

   7. Criteria that ignore certain groups when calculating totals.    For example, you will calculate totals for just the Video Games, Art and Framing, and Sports categories.

   8. Criteria that hide certain totals after calculating them.    For example, you can display only the totals greater than $150,000.

   9. Criteria that exclude individual records from being included in the total.    For example, you can exclude individual sales transactions when the value (Unit Price * Quantity) drops below $100.

      The following steps explain how to add the criteria one by one and see the impact on the query result.

      Add criteria to the query

   10. Open the query from the previous section in Design view. To do so, right-click the document tab for the query and click Design View.

       -or-

       In the Navigation Pane, right-click the query and click Design View.

   11. In the Criteria row of the Category ID column, type =Dolls Or Sports or Art and Framing.

   12. Click Run to run the query and display the results in Datasheet view.

   13. Switch back to Design view and, in the Criteria row of the Total Sales Value column, type >100.

   14. Run the query to see the results, and then switch back to Design view.

   15. Now add the criteria to exclude individual sales transactions that are less than $100. To do this, you need to add another column.

       Note: You cannot specify the third criteria in the Total Sales Value column. Any criteria that you specify in this column applies to the total value, not to the individual values.

   16. Copy the expression from the second column to the third column.

   17. In the Total row for the new column, select Where and, in the Criteria row, type >20.

   18. Run the query to see the results, and then save the query.

       Note: The next time you open the query in Design view, you might notice slight changes in the design grid. In the second column, the expression in the Field row will appear enclosed inside the Sum function, and the value in the Total row displays Expression instead of Sum.

       Total Sales Value: Sum((1-[Order Details].Discount/100)*([Order Details].Unitprice*[Orde r Details].Quantity))

       You will also see a fourth column. This column is a copy of the second column, but the criteria that you specified in the second column actually appears as part of the new column.

Top of Page

Create a crosstab query

1. On the Create tab, in the Queries group, click Query Design.

2. Double-click the tables that you want to use in your query.

   Each table appears as a window in the upper section of the query designer.

   If you use the sample tables, double-click the Orders table and the Order Details table.

3. Double-click the fields that you want to use in your query.

   Each field name appears in the a blank cell in the Field row of the design grid.

   If you use the sample tables, add the Ship City and Ship Date fields from the Orders table.

4. In the next blank cell in the Field row, copy and paste or type the following expression: Total Sales: Sum(CCur([Order Details].[Unit Price]*[Quantity]*(1-[Discount])/100)*100)

5. On the Query Design tab, in the Query Type group, click Crosstab.

   The Total row and the Crosstab row appear in the design grid.

6. Click the cell in the Total row in the City field and select Group By. Do the same for the Ship Date field. Change the value in the Total cell of the Total Sales field to Expression.

7. In the Crosstab row, set the cell in the City field to Row Heading, set the Ship Date field to Column Heading, and set the Total Sales field to Value.

8. On the Query Design tab, in the Results group, click Run.

   The query results appear in Datasheet view.

Top of Page