Set Up Query To Look Like A Pivot Table

G

Guest

I'd like to set up a query that looks like a Pivot Table, but it not acutally
a Pivot Table.

I'm trying to report the Sales activities over the months of the year.
There are two fields that represent the Revenue (one field per Revenue Type
(DIRECT and INDIRECT)). How do I, or can I set up a query that will display
January Revenue for both Direct and Indirect in the first two columns, then
the next two columns, show the revenue for Direct and Indirect for Feburary,
and so on...?
 
G

Guest

hi.
queries within queries.
make a query that selects january revenue(direct and
indirect)
make a query that selects feburary revenue(direct and
indirect)
make a third(master)query that uses the first 2 querys as
record sources.
drag january revenues from the january query into it and
feburary revenues from the febuary queries.
i use this technique to create running balances by month
for our inventory. each query selects po's or wo's and
sums them for the month. the queries get the dates for a
special form i created.
 
G

Guest

I was trying to avoid having to create 12 different queries. Actually, it's
more like 108 queries. I have to report the revenue for 9 different
organizations in the company and there is a field in the database that
identifies the "Area". I'd have to create one query per Area per Month.

If this is the only way to accomplish it, then I guess I better get cracking!

Thanks,
Frank
 
P

PC Datasheet

Apply the procedure below. If you need help setting this up, I can help you
for a very reasonable fee

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


Create A Crosstab Query With More Than One Value



Note: You can create multiple values by combining a crosstab and cartesian
query. The following is an example using Northwind. The example creates a
crosstab query showing both a Quantity and Quantity * Price value for each
product.

1.. Create a new table TblXtabColumns with a single field [FldName].
2.. Add two records to TblXtabColumns, "Quantity" and "QtyXPrice"
3.. Create a crosstab query with the table [Products], [Orders], [Order
Details], and
[tblXtabColumns].
1.. Join Orders and Order Details on OrderID
2.. Join Products and Order Details on ProductID
3.. Do not join tblXtabColumns to any other table
1.. Pull down ProductName from Products. Set its Total property to Group
By and set its Crosstab property to Row Heading
2.. Enter the following expression in the second query field:
Expr1:[FldName] & Month([OrderDate])
1.. Set its Total property to Group By and set its Crosstab property to
Column Heading
1.. Enter the following expression in the third query field:
DaVal: Sum(IIf([FldName]="Quantity",[Quantity],[Quantity]*[Order
Details].[UnitPrice]))
1.. Set its Total property to Expression and set its Crosstab property to
Value
1.. Pull down OrderDate from Orderss. Set its Total property to Where and
leave its Crosstab property blank.
1.. Set the criteria for OrderDate to:
Between #8/1/1994# And #9/30/1994#

The full SQL is:

TRANSFORM Sum(IIf([FldName]="Quantity",[Quantity],[Quantity]*[Order
Details].[UnitPrice])) AS DaVal

SELECT Products.ProductName

FROM tblXtabColumns, Products INNER JOIN (Orders INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order
Details].ProductID

WHERE (((Orders.OrderDate) Between #8/1/1994# And #8/31/1994#))

GROUP BY Products.ProductName

PIVOT [FldName] & Month([OrderDate]);


The crosstab query returns four columns per Product:






Where "8" and "9" are the months August and September.
 
G

Guest

hi again,
it's the only way i have found. other more creative MVP's
may have found others. the problem seems to be selecting
the same field(date in this case) and giving multiple
criteria for the same field. it seems to confuse accesss.
soulution - multiple queries.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top