INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

How To

How can I get a crosstab to have more than one value by dhookom
Posted: 1 Dec 03

You can create multiple values by combining a crosstab and cartesian query.

Create a sample using the Northwind database to create both a Quantity and Quantity * Price value for each column.
-Create a new table tblXtabColumns with a single field [FldName]
-add two records to this table
    "Quantity"
    "QtyXPrice"
-create a crosstab with the table [Products], [Orders], [Order Details], and
[tblXtabColumns]
-join the first three tables as expected but don't join tblXtabColumns to
any other table
-your Column Heading expression is
    Expr1:[FldName] & Month([OrderDate])
-your Value expression is
    DaVal:IIf([FldName]="Quantity",[Quantity],[Quantity]*[Order
Details]![UnitPrice])
 Sum
-I set OrderDate for the first three months of 1998
The full SQL is:

TRANSFORM Sum(IIf([FldName]="Quantity",[Quantity],[Quantity]*[Order
Details]![UnitPrice])) AS DaVal
SELECT Products.ProductName
FROM tblXtabColumns, Orders INNER JOIN (Products INNER JOIN [Order Details]
ON Products.ProductID = [Order Details].ProductID) ON Orders.OrderID =
[Order Details].OrderID
WHERE (((Orders.OrderDate) Between #1/1/1998# And #3/31/1998#))
GROUP BY Products.ProductName
PIVOT [FldName] & Month([OrderDate]);

You will get two columns per month QtyXPriceN and QuantityN.

Back to Microsoft: Access Queries and JET SQL FAQ Index
Back to Microsoft: Access Queries and JET SQL Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close