Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 304458 - Last Review: March 27, 2007 - Revision: 6.0
How to create a crosstab query with multiple value fields
This article was previously published under Q304458
Moderate: Requires basic macro, coding, and interoperability
skills.
This article applies only to a Microsoft Access database (.accdb and .mdb).
For a Microsoft Access 2000 version of this article,
see
209143Â
(http://kbalertz.com/Feedback.aspx?kbNumber=209143/EN-US/
)
.
For a Microsoft Access 97 version of this article,
see
109939Â
(http://kbalertz.com/Feedback.aspx?kbNumber=109939/
)
.
In a Microsoft Access crosstab query, you can specify only
one field or calculation as the value. You may want to show more than one value
in the query.
The following example shows two columns of information
per company, a count of the number of orders, and the order totals for each
year:
Company Name 1998 Orders 1998 Total 1999 Orders 1999 Total
--------------------------------------------------------------
ABC Company 12 $855.00 15 $1010.25
XYZ Company 1017 $22045.57 1050 $25345.29
This type of query is sometimes called a Multiple Value Field
query.
To create a Multiple Value Field crosstab query, you must
create a separate crosstab query for each value that you want to calculate. You
can then use a select query to join these crosstab queries to display the
results that you want.
The following example uses the sample
database Northwind.mdb to create a query that displays results similar to the
example in the "Summary" section earlier in this article. It shows the number
of sales and total for each year for each company.
- Open the sample database Northwind.mdb, and then create the
following crosstab query that is based on the Orders, Order Details, and the
Customers tables:
Query: Order Total
------------------
Type: Crosstab Query
Join: Customers.[CustomerID] <-> Orders.[CustomerID]
Join: Orders.[OrderID] <-> Order Details.[OrderID]
Field: CompanyName
Table Name: Customers
Total: Group By
Crosstab: Row Heading
Field: Expr1: Year([OrderDate]) & " " & "Order Total"
Table Name:
Total: Group By
Crosstab: Column Heading
Field: Expr2: Sum(CCur([UnitPrice]*[Quantity]*(1-[Discount])))
Table Name:
Total: Expression
Crosstab: Value
- Save this query as Order
Total.
- Create the following crosstab query that is based on the
Orders and the Customers tables:
Query: Order Count
------------------
Type: Crosstab Query
Join: Customers.[CustomerID] <-> Orders.[CustomerID]
Field: CompanyName
Table Name: Customers
Total: Group By
Crosstab: Row Heading
Field: Expr1: Year([OrderDate]) & " " & "Order Count"
Table Name:
Total: Group By
Crosstab: Column Heading
Field: OrderID
Table Name: Orders
Total: Count
Crosstab: Value
- Save this query as Order
Count.
- Create a query that is based on the Order Total and the
Order Count crosstab queries. Use the CompanyName, the Order Total, and Order
Count fields for the years whose results you want to view. The following
example uses the last two years of Order Total and Order Count in Microsoft
Access.
Query: Multiple Values
----------------------
Join: Order Total.[CompanyName] <-> Order Count.[CompanyName]
Field: CompanyName
Table Name: Order Count
Field: 1997 Order Count
Table Name: Order Count
Field: 1997 Order Total
Table Name: Order Total
Field: 1998 Order Count
Table Name: Order Count
Field: 1998 Order Total
Table Name: Order Total
- When you run the Multiple Values query, you will see a
table that looks similar to the following table:
1997 Order 1997 Order 1998 Order 1998 Order
Company Name Count Total Count Total
--------------------------------------------------------------------
Alfred's Futterkiste 3 $2,022.50 3 $2,022.50
Ana Trujillo 2 $799.75 1 $514.10
Antonio Moreno 5 $5,960.78 1 $660.00
Year([OrderDate]) & " " & "Order Total"
Year([Orders].[OrderDate]) & " " & "Order Total"
For
more information about crosstab queries, click
Microsoft Access Help on the
Help menu, type
crosstab queries in the Office
Assistant or the Answer Wizard, and then click
Search to view the topics returned.
APPLIES TO
- Microsoft Office Access 2007
- Microsoft Office Access 2003
- Microsoft Access 2002 Standard Edition
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate
Be the first to leave feedback, to help others about this knowledge base
article.
(Optional) Name
(Optional)
Public URL Or Email
Comments
No
HTML -- Text Only Please