Results 1 to 3 of 3

Thread: Dynamic Rerport

  1. #1
    Join Date
    Apr 2004
    Posts
    1

    Unanswered: Dynamic Rerport

    Hello,

    I have a crosstab query that I need to do a report for. I can manually create the report without any difficulty. Here's the my problem.

    The query provides a list of team names, total standings points, and the points from each "game date".

    I do not want to have to "Recreate" the report every time a new date is added to the crosstab query. Is there a way to have the report created on-the-fly/dynamically by the user so that it will always include the most current data. (points are loaded manually each week after the results have been turned in to the league).

    The report has 3 groupings (2 with group headers): 1st level-Age Group (w/header), 2nd level-League(W/header), 3-Sum of Points (used only to sort standings in decending order). The problem is that if I create the report now, it will only contain data for the current week, the next week, it will not add the new data to the report unless the design is amended to add it (thus the need to create dynamically)

    The crosstab querry uses another queary as the source. The source querry is based on two tables that have a 1-many relationship the tables/fields are as follows:

    GameInfo Table
    GameNumber (PK)
    GameDate
    GameTime
    Field
    AgeGroup
    League
    Area
    GameReport

    GameStats2 Table
    GameStatID (PK)
    GameNumber (used to link to GameInfo table)
    Team Name
    Team Score
    Points



    The source query for the crostab query has the following:

    Both GameInfo Table and GameStats2 table with 1-many relationship

    Team Name -from GameStats2
    GameDate - from Gameinfo
    Points - from GameStats2
    AgeGroup - from GameInfo
    League - From GameInfo
    GameNumber - from GameInfo

    Here is the SQL for the crosstab:

    TRANSFORM Sum(JFtest2.Points) AS SumOfPoints
    SELECT JFtest2.AgeGroup, JFtest2.League, JFtest2.[Team Name], Sum(JFtest2.Points) AS [Total Of Points]
    FROM JFtest2
    GROUP BY JFtest2.AgeGroup, JFtest2.League, JFtest2.[Team Name]
    PIVOT Format([GameDate],"Short Date");



    Any Help would be appreciated.

  2. #2
    Join Date
    Oct 2003
    Location
    US
    Posts
    343

    Re: Dynamic Rerport

    Yes you can create a report on the fly. Research on "create control" in help. The report that you are planning to create dynamically will require alot of vba programming so make sure you are up for it.


    Originally posted by JF Sly
    Hello,

    I have a crosstab query that I need to do a report for. I can manually create the report without any difficulty. Here's the my problem.

    The query provides a list of team names, total standings points, and the points from each "game date".

    I do not want to have to "Recreate" the report every time a new date is added to the crosstab query. Is there a way to have the report created on-the-fly/dynamically by the user so that it will always include the most current data. (points are loaded manually each week after the results have been turned in to the league).

    The report has 3 groupings (2 with group headers): 1st level-Age Group (w/header), 2nd level-League(W/header), 3-Sum of Points (used only to sort standings in decending order). The problem is that if I create the report now, it will only contain data for the current week, the next week, it will not add the new data to the report unless the design is amended to add it (thus the need to create dynamically)

    The crosstab querry uses another queary as the source. The source querry is based on two tables that have a 1-many relationship the tables/fields are as follows:

    GameInfo Table
    GameNumber (PK)
    GameDate
    GameTime
    Field
    AgeGroup
    League
    Area
    GameReport

    GameStats2 Table
    GameStatID (PK)
    GameNumber (used to link to GameInfo table)
    Team Name
    Team Score
    Points



    The source query for the crostab query has the following:

    Both GameInfo Table and GameStats2 table with 1-many relationship

    Team Name -from GameStats2
    GameDate - from Gameinfo
    Points - from GameStats2
    AgeGroup - from GameInfo
    League - From GameInfo
    GameNumber - from GameInfo

    Here is the SQL for the crosstab:

    TRANSFORM Sum(JFtest2.Points) AS SumOfPoints
    SELECT JFtest2.AgeGroup, JFtest2.League, JFtest2.[Team Name], Sum(JFtest2.Points) AS [Total Of Points]
    FROM JFtest2
    GROUP BY JFtest2.AgeGroup, JFtest2.League, JFtest2.[Team Name]
    PIVOT Format([GameDate],"Short Date");



    Any Help would be appreciated.

  3. #3
    Join Date
    Oct 2003
    Location
    US
    Posts
    343

    Re: Dynamic Rerport

    sorry typo error.. search for "CreateReport" and "CreateReportControl"


    Originally posted by khan
    Yes you can create a report on the fly. Research on "create control" in help. The report that you are planning to create dynamically will require alot of vba programming so make sure you are up for it.

Posting Permissions

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