Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2012
    Posts
    6

    Unanswered: CrossTab Query - Report Issue ?

    Hello all ,
    I recently create some crosstab querys and now i need to implement WHERE Clauses or you can say some paremeters and basically this would be a search query .

    First of all this is the first time that i am trying to use parameters to crosstab and as i read , i need to define parameters throught the query parameters and i am not sure even if i made it correct.

    Here is my query:

    PARAMETERS [Forms]![Form1]![Year] Value;
    TRANSFORM Nz(Sum(Main.Duration))+0 AS SumOfDuration
    SELECT Employee.EmployeeName
    FROM Season INNER JOIN (LeaveType INNER JOIN (Employee RIGHT JOIN Main ON Employee.EmployeeID = Main.EmployeeID) ON LeaveType.LeaveID = Main.LeaveID) ON Season.SeasonID = Main.SeasonID
    WHERE (((Season.Aseason) Like [Forms]![Form1]![Year] & "*")) OR ((([Forms]![Form1]![Year]) Is Null))
    GROUP BY Employee.EmployeeName
    PIVOT LeaveType.LeaveType;

    (((Season.Aseason) Like [Forms]![Form1]![Year] & "*")) OR ((([Forms]![Form1]![Year]) Is Null))

    The Season.Aseason .. etc .. basiccaly is the parameters that i want to use so i am not sure if the PARAMETERS that i have wrote is correct .
    Anyway, when i am in the query and i run it the resutls are correct.
    Then i create a report based on my query and when i open it , it ask me the parameters and i am inserting for example 2012 , but it keeps asking me again its like a nested .
    When i am writing to it 2013 it produces an error and it says : .. Does not recognize " as a valid field name or expression .
    My first thought its that the query is not correct and the second if i need to change some options from the report properties .
    Also the user will fill the query parameters throught a form as you can see [Forms]![Form1]![Year] so there would not be any popup/parameter window .

  2. #2
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    The parameters should probably be a single variable name that gets set when the query is called. I would use Parameters theYr Value; (double check the syntax as I am assuming yours is correct). When I run the query is run, you can open it on your form, and assign the year at that time. Another way is to put the parameter into a table and do an inner join. Your method might work, but I would put it into a sub query first. Then use that query to populate your cross tab query. I have found that this will greatly speed up your query. -- John M Reynolds
    John M Reynolds

  3. #3
    Join Date
    Sep 2012
    Posts
    6
    Thank John M Reynolds for your report , i already solved the problem but thanks again for the effort and your time

Posting Permissions

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