Hi all,
I have tried this example from Microsoft help to create cross tab query for multiple values but not lucky enough to get the results expected because it confused me a lot where I see to figure out this line :
Field: Expr1: Year([OrderDate]) & " " & "Order Total"
--------------------------------------------------------
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. 1. Open the sample database Northwind.mdb and create the following crosstab query 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
2. Create the following crosstab query 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
3. Create a query based on the Order Total and Order Count crosstab queries. You will use CompanyName as well as 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
Running the Multiple Values query results in a table that looks like the following: 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
NOTE: You need to add the table name to the expression if the field specified for the concatenation is a field in more than one of the tables joined in the query. For example, in Step 1 you would change: Year([OrderDate]) & " " & "Order Total"
to: Year([Orders].[OrderDate]) & " " & "Order Total"
---------------------------------------------------------
Thank in advance for any help and explain steps by steps easy to understand than above example.
I have tried this example from Microsoft help to create cross tab query for multiple values but not lucky enough to get the results expected because it confused me a lot where I see to figure out this line :
Field: Expr1: Year([OrderDate]) & " " & "Order Total"
--------------------------------------------------------
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. 1. Open the sample database Northwind.mdb and create the following crosstab query 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
2. Create the following crosstab query 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
3. Create a query based on the Order Total and Order Count crosstab queries. You will use CompanyName as well as 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
Running the Multiple Values query results in a table that looks like the following: 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
NOTE: You need to add the table name to the expression if the field specified for the concatenation is a field in more than one of the tables joined in the query. For example, in Step 1 you would change: Year([OrderDate]) & " " & "Order Total"
to: Year([Orders].[OrderDate]) & " " & "Order Total"
---------------------------------------------------------
Thank in advance for any help and explain steps by steps easy to understand than above example.
