• dynamic crosstab problem (2000 (SP3))

    Author
    Topic
    #425098

    I need to make a monthly stats report for a service agency. There are distinct sets of stats that have to be reported on that call for three separate queries. 1) A count of all the males and females for the given period; 2) a count of all the new clients and their immigration status; 3) a count of all the returning clients and their immigration status. To achieve this, I have an underlying select query to prep the data and create unique clients (Since we want to count people, not the number of times they came in) then I run this through 3 separate crosstabs to provide the 3 different sets of data.

    I have a dynamic crosstab working (based on post 134439) beautifully for the separate crosstabs since the number of columns varies in the crosstab. So far so good. I *thought* cooked it would be a simple matter to put these three as subreports on a main report that I would use as a shell to organize the info. Sadly I get the following message:
    “You can’t use a pass-through query or a non-fixed column query as a record source for a subform”.

    Any suggestions short of printing out three separate reports? At the very least I would like them all to print at the same time, preferably on the same sheet of paper. The client would like it all to appear in a long grid, not three separate ones. I am well and truly stumped with this one.

    Viewing 0 reply threads
    Author
    Replies
    • #978346

      Couldn’t you fix the column headings? For example, the first crosstab will have something like Male and Female as column headings, I suppose, and for the others, you could specify all possible immigration status values as column headings.

      • #978395

        Could you expand on this? Are you suggesting fix them in the crosstab or fix them in the report? If in the crosstab, how would I do that? If in the report, what do I do with empty columns when an immigration value doesn’t appear? There are a maximum and reasonable number of columns for the immigration status values so it would be easy to do (only 7), I’m just not sure how to go about this since I thought the crosstab created the columns based on what data was actually entered (e.g. if there were no Family Class clients that month then there is no data for that possibility).

        • #978396

          Open one of the crosstab queries in design view.
          Click in an empty part of the upper section of the query window.
          Activate the Properties window.
          Enter a comma-separated list of the column headings in the Column Headings property, in the order you want them, e.g.

          "Male","Female"

          Close and save the query.
          The query will display all specified columns, even if there are no data for one or more of them.
          In fact, now that your column headings are fixed, you can create an ordinary report based on the crosstab query – you don’t need all that code any more. But it’s not necessary to do this.

          Repeat for the other crosstab queries.
          You should now be able to use the reports as subreports.

          • #978431

            God bless your cotton socks! My hero. clever

            I’ll post back if I still have problems. Too bad I wasn’t smart enough to ask these questions about 5 development hours ago….

    Viewing 0 reply threads
    Reply To: dynamic crosstab problem (2000 (SP3))

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: