Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    dynamic crosstab problem (2000 (SP3))

    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* <img src=/S/cooked.gif border=0 alt=cooked width=50 height=46> 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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: dynamic crosstab problem (2000 (SP3))

    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.

  3. #3
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: dynamic crosstab problem (2000 (SP3))

    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).

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: dynamic crosstab problem (2000 (SP3))

    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.
    <code>
    "Male","Female"
    </code>
    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.

  5. #5
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: dynamic crosstab problem (2000 (SP3))

    God bless your cotton socks! My hero. <img src=/S/clever.gif border=0 alt=clever width=15 height=15>

    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....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •