Crosstab Query Question

  • Thread starter Thread starter Joe Williams
  • Start date Start date
J

Joe Williams

How do you create a crosstab query based on a category that the user can
define? For example, in my program the user can specify as many different
categories as they want, and then I would like a crosstab query that will
give me a summary of sales by category. (So category would go from right to
left along the top of the report, and user would go from top to bottom)

All of the examples I have seen regaring crosstab queries are
"pre-programmed" with column headers which is what I am trying to avoid.

Thanks!

Joe
 
You will have to build this query statement dynamically, i.e. use VBA code
to create a string that contains the correct query statement.

To see an example of what you need to achieve, you can mock up a crosstab
query. You will have the Category in the grid as the Column Headings. Drag
Category into the grid a 2nd time, and this time choose Where. In the
Criteria row, enter a few example values, such as:
IN ('Dog', 'Cat', 'Bird')
Now switch it to SQL View (View menu, from query design).

In your VBA code, copy the first part of the SQL statement into a constant,
e.g.:
Const strcStub = "TRANSFORM ... WHERE "
End it will the WHERE and space.
Then make another constant from the part following the WHERE clause, i.e.:
Const strcTail = "GROUP BY ...;"
You do not want an IN list in the PIVOT clause.

Now all you need to do it build the text for the WHERE clause and concatente
it in the middle. For an example of building a WHERE clause from the items
in a multi-select list box, see:
http://members.iinet.net.au/~allenbrowne/ser-50.html
Although the example is about setting the WhereCondition for reports, the
WHERE clause will be exactly the same.

Now you have the 3 parts for your SQL statement, you can assign it to the
SQL property of your query. This example assumes that the form that shows
the multi-select list box to the user has a command button to click to view
the query, and its Click Event Procedure will look something like this:

Private Sub cmdShowQuery_Click()
Const strcStub = "TRANSFORM ... WHERE "
Const strcTail = "GROUP BY ...;"
Dim strSQL As String

'code form the link above here.

strSQL = strcStub & strWhere & strcTail
dbEngine(0)(0).QueryDefs("Query1").SQL = strSQL

DoCmd.OpenQuery "Query1"
End Sub
 
Thanks Allen,

Related question: Is there any way to have TWO value variables in a crosstab
query for each column heading or are you limited to just one? For instance,
for each column head of category I would like to have a count of sales as
well as a total $ of sales. Row heading would be salesperson.

Possible? If not, how would you tackle it? Thanks

_joe
 
Don't think I ever tried using multiple Column Heading source fields in one
crosstab. I'd be surprised if there was a simple way to do that.

All that comes to mind is a subqeury to get the other total for the
combination of RowHeading + Column Heading. I guess if you are writing the
query dynamnically, you may be able to generate another field for each of
the categories in the multi-select list box.
 
You can apply the following to your situation:
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
Top