1. The document discusses how to create cross-tabulation queries in Microsoft Access to summarize data from tables.
2. It provides steps to construct cross-tab queries that summarize fields like product number, quantity ordered, and date.
3. Examples show summing quantities ordered or subtotals for each product number by date to analyze product sales over time.
1. The document discusses how to create cross-tabulation queries in Microsoft Access to summarize data from tables.
2. It provides steps to construct cross-tab queries that summarize fields like product number, quantity ordered, and date.
3. Examples show summing quantities ordered or subtotals for each product number by date to analyze product sales over time.
1. The document discusses how to create cross-tabulation queries in Microsoft Access to summarize data from tables.
2. It provides steps to construct cross-tab queries that summarize fields like product number, quantity ordered, and date.
3. Examples show summing quantities ordered or subtotals for each product number by date to analyze product sales over time.
1. The document discusses how to create cross-tabulation queries in Microsoft Access to summarize data from tables.
2. It provides steps to construct cross-tab queries that summarize fields like product number, quantity ordered, and date.
3. Examples show summing quantities ordered or subtotals for each product number by date to analyze product sales over time.
Exercise 7 Create CrossTabs Suppose you want to create summary information for the Products. For example, the number of products ordered in a particular month or the value of inventory shipped for a particular product in a particular month, we use cross tabs to help us summarize the data as shown in the examples below.
Steps to create Cross Tab queries:
1. Click Queries tab on the Database window and select Create query in Design view
Figure 1: Database Window
2. Add Order and OrderDetails Tables.
3. Select Query/Cross Tab or click
Figure 2: Crosstab Query option
4. Try to construct the following query:
a. Choose the fields: “ProductNo”, “QtyOrder” & “Date”
b. For the “ProductNo” Column
Field ProductNo Total Group By CrossTab Row Headings
c. For the “QtyOrder” column
Field QtyOrder Total Sum CrossTab Value MANA 120 College of Business Administration Access Tutorial Exercise 7 Marquette University Page 2
d. For the “Date” column
Field Date Total Group By CrossTab Column Heading
5. Execute your query by clicking
*You will see the sum of Products sold by ProductNo on the Dates of the transaction
Another Option: If we want to summarize the “Subtotal” instead, we would change the query as follow:
a. For the “ProductNo” Column, no change
b. For the 2nd column
Field Subtotal Total Sum CrossTab Value
c. For the “Date” column, no change
*You will see the sum of the subtotals of Products sold by ProductNo on the Dates of the transaction
NOTE: You can summarize using COUNT, AVG, MIN, MAX for the Totals row.
MINI-EXERCISE I. Construct a Cross-Tabulation summary of CustomerID vs ProductNo and the total number of products the company ordered for that product in the month of January.
Query Exercise I. List the company, the products and the total number of products the company ordered for that particular product. II. List the company, the products, the total shipped for that product and the total valuation of all the products shipped. (Valuation = QtyShip * SellingPrice). In this query, be careful because a product may have been shipped three times, I only want the sum of all the shipments, not separate shipments information.
