Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Mar 2010
    Location
    UK
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Basically the issue i'm having is, that i have a crosstab which displays data dependant on variables.

    this is all about clinics, and consultant cancellations.

    so one of my crosstabs shows the number of clinics canceled by consultants with 1-5 weeks notice, and only the cancellations made within the last month.

    displaceyed a little like this;

    Consultant 1 2 3 4 5
    MR Doc 1 4 8
    Ms Doc 1
    Dr Doc 1 1 1 1

    Obviously it looks a little better, but i cant put up a screenshot, due to policy.

    I created a report (containing 2 subreports, to allow 2 variants of this crosstab to be presented side by side)

    But what has happened is that the clinic that was canceled in week 2, has rolled over the 1 month limit, which means in the cross tab there is no longer a "2" column, so the report cant find it, and therefore doesnt run, leaving me with no report.

    I don't know how to fix it, and neither can my colleagues, but basically i need to find a way to have the crosstab display the weekly column, even if it is null, or maybe a wayfor the report to work if "2" isnt there?

    Any help would be GREATLY appreciated, Thank you.

    Chris.

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Quote Originally Posted by Christopher Theobald View Post
    But what has happened is that the clinic that was canceled in week 2, has rolled over the 1 month limit, which means in the cross tab there is no longer a "2" column, so the report cant find it, and therefore doesnt run, leaving me with no report.
    If you look at the SQL for your crosstab query, it probably ends something like "...PIVOT WeeksNotice".

    You need to tell Access all the possible values in WeeksNotice; so Access can effectively reserve a column for them, even if there is no data. Change your SQL to: "...PIVOT WeeksNotice IN (1,2,3,4,5)"
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Lounger
    Join Date
    Mar 2010
    Location
    UK
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts
    brilliant! Thank you, sounds quite simple really, going to be kicking myself all day for that one.

    thanks again for your help you've definatley saved me a headache!

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Quote Originally Posted by Christopher Theobald View Post
    brilliant! Thank you, sounds quite simple really, going to be kicking myself all day for that one.

    thanks again for your help you've definatley saved me a headache!
    I once had a physics prof who often used the term "intuitively obvious" when making an assumption or arriving at a conclusion. Well, this ain't one of those times!

    There is no way you would have known about this, so no need to kick yourself. The crosstab wizard does allow you to enter expected values, so you'd never routinely run across it.

    I don't remember where I learned it, but it has saved my butt many times, especially when using a crosstab query as the recordsource for a report (as you found out).
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by MarkLiquorman View Post
    You need to tell Access all the possible values in WeeksNotice; so Access can effectively reserve a column for them, even if there is no data. Change your SQL to: "...PIVOT WeeksNotice IN (1,2,3,4,5)"
    THIS IS GREAT! Thank you, after reading your suggestion to Christopher, I tried it, and also found out that you can also accomplish the same by putting values, separated by commas, in the field properties for the column heading grouping, in the "column heading" property while in the design grid rather than SQL window -- magical!
    Pat

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    A more general solution to this problem is to use a dynamic crosstab report. In this case the column headings in the report are not defined in advance, but set in code when the report is opened.

    I attach a demo which is not mine. I got it from the Lounge previously, but posts referring to it seem to have been deleted.
    (I could not find any, anyway.)

    [attachment=88311:CrosstabDemo2K.zip]
    Attached Files Attached Files
    Regards
    John



  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Quote Originally Posted by John Hutchison View Post
    A more general solution to this problem is to use a dynamic crosstab report. In this case the column headings in the report are not defined in advance, but set in code when the report is opened.

    I attach a demo which is not mine. I got it from the Lounge previously, but posts referring to it seem to have been deleted.
    (I could not find any, anyway.)

    [attachment=88311:CrosstabDemo2K.zip]
    It is not the column headings, it is the controlsource for the objects in the detail section. And Access2007 doesn't like to change the controlsource in the Open statement; no such problem with A2003 and before. So now we have to open the report hidden in design mode, change the controlsource, close it, then run the report.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I use this dynamic crosstab report in 2007 a lot without problems. (using mdb file format.)

    In this context Column Headings and Control Source are the same thing. The control source of the report controls relate to the column headings in the crosstab query.
    Regards
    John



Posting Permissions

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