Re: Crosstab Query Question

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Allen Browne (AllenBrowne_at_SeeSig.Invalid)
Date: 11/21/04

  • Next message: Andreas Andreasson: "refreshlink??" 
    Date: Sun, 21 Nov 2004 16:10:27 +0800

    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. 

    -- 
Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Joe Williams" <JOE@ANYWHERE.com> wrote in message
news:uJM3f64zEHA.3908@TK2MSFTNGP12.phx.gbl...
> 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
>
>
> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message 
> news:uunOr72zEHA.3028@TK2MSFTNGP10.phx.gbl...
>> 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
>>
>>
>> "Joe Williams" <JOE@ANYWHERE.com> wrote in message
>> news:unO$EYyzEHA.1204@TK2MSFTNGP10.phx.gbl...
>>> 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.
  • Next message: Andreas Andreasson: "refreshlink??"
    • Quantcast