Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343

    Unanswered: Crosstab reporting problems

    Hi all
    Ive asked something like this before and didnt quite get a work around for the issue.

    The sql for it is :-
    TRANSFORM Count([Task])
    SELECT [datefrom], [dateto], Count([Task]) AS [Total Of Task]
    FROM People
    GROUP BY [datefrom], [dateto]
    PIVOT [Period];

    It is the PIVOT [Period]; that is causing me the issue. The period is a number from 1 to 12 tied to GROUP BY [datefrom], [dateto]


    ie 1 02/01/06 01/02/06
    2 02/02/06 29/03/06
    3 30/03/06 28/04/06

    etc etc

    the issue is when i set up a report with the row data to the left and the "column" pre set in text boxes as control source........[1] [2] [3] [4] etc across the top of the report if the from to date does not have any dat attacged to it I have a a meeage box stating

    the Microsoft Jet database engine does not recognise [5] as a vaild field name or expression.

    I kow that its because the cross tab in effect has not created [5] and the reports text box is looking for it - SO is there a way of telling the system that if control source does not exits the do something else [5]

    Like always Im stuck on this and for the life of me I cant work around it as the reports have a formal government layout and cant really be made ad hock (dynamic)

    gareth

  2. #2
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    One way to do this is to add 12 records to your source data (one record for each period). You can put dummy data in for the key fields but make sure the Period has the period number (one for each record).

    Then when you run the crosstab the dummy records will force each period column to be created. The side effect is that you end up with a dummy line in your crosstab. But this can be easily filtered out.

    If you don't like the idea of adding the dummy records to your original table then just append them using a union query before the crosstab.

    hth
    Chris

  3. #3
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    Hi Chris - Thanks for that. I had considered the dummy data. However Im going through ODBC to another system (suppose I should have said that as well sorry). So placing zeros of something is not in scope. Ill look at the append and see whats capable there.

    Thanks again for the input

    Gareth

  4. #4
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Quote Originally Posted by garethfx
    Hi Chris - Thanks for that. I had considered the dummy data. However Im going through ODBC to another system (suppose I should have said that as well sorry). So placing zeros of something is not in scope. Ill look at the append and see whats capable there.

    Thanks again for the input

    Gareth
    Append was probably too strong a word. You are not really appending the data but just creating a view with the data stuck on the end. Here's an example query:

    SELECT PersonID, Period, Score
    FROM MasterData
    UNION
    SELECT "Dummy" AS DummyPersonID, "P1" AS DummyPeriod, 0 AS DummyScore
    FROM MasterData
    UNION
    SELECT "Dummy" AS DummyPersonID, "P2" AS DummyPeriod, 0 AS DummyScore
    FROM MasterData
    UNION
    SELECT "Dummy" AS DummyPersonID, "P3" AS DummyPeriod, 0 AS DummyScore
    FROM MasterData
    UNION
    SELECT "Dummy" AS DummyPersonID, "P4" AS DummyPeriod, 0 AS DummyScore
    FROM MasterData
    UNION
    SELECT "Dummy" AS DummyPersonID, "P5" AS DummyPeriod, 0 AS DummyScore
    FROM MasterData
    etc... for all the required periods.

    Note how the original data is unaffected but we've glued on the dummy data. You then use this query as the source for your crosstab.

    hth
    Chris

Posting Permissions

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