Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003
    Posts
    268

    Unanswered: Dynamic Building of Multicolumn Crosstab Query

    Problem:
    Here is my dilemma. I want to dynamically create a multi column crosstab
    query and display the respective results in a report.

    Background:
    I have a table with multiple measures for multiple facilities for multiple qtrs. Each row contains a Numerator and a Denominator. A required field for the report is a Rate field which is num/den where den<>0
    So each row will have
    FacID, Measure, QtrYear, Num, Den

    This is easily turned into a crosstab query for only one value at time.
    facID, Measure - row headings
    QtrYear - column headings
    Num - Value

    Issue: I can only do one value at a time
    Issue: I want to be able to report for only the most current 4 qtrs for which I have data
    Issue: The column names will constantly change if I join together each of the three cross tab queries to make a multi value

    What I tried:
    I tried dynmacally creating a table, with a column for each qtr for each num, den, and rate. then cycle through the existing table and plug the values in for each of the columns for each of the respective rows.

    Does anyone have any idea on this one? I am sick of pounding my head on my desk on this one....

  2. #2
    Join Date
    Jul 2004
    Location
    Lorain, OH
    Posts
    11

    Talking Great Example Solution For You

    Hello-

    There is a great example of a solution that I have been working with that may help you. It uses a multiple value entry for each cell of the crosstab query, allowing you to pull the values out of the cell in the report using code.

    Search for crosstab and report in msdn.com and there will be a response by Duane Hookum with the link in it for the website where the example is downloadable for you.

    Vincent DeLuca
    VDeluca7@aol.com

Posting Permissions

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