Results 1 to 3 of 3

Thread: Crosstab query Access2003 - How to restrict results parameterized?

  1. #1
    Join Date
    Oct 2016
    Posts
    1

    Crosstab query Access2003 - How to restrict results parameterized?

    Hallo!

    I have a query (Access2003) as basis for a form (VB.NET), in which measured values of certain properties (tied together in test series) at 10 metering points should be presented as follows (metering points horizontally):

    Property Point1 Point2 Point3 Point4 ... Point10
    --------------------------------------------------------
    Prop1 value 1 value n empty etc. ....
    Prop2 value x empty value y etc. ....
    ... etc. ....
    ... etc. ....
    I have created a query that returns the desired result, but unfortunately for all in the database existing test series and not only for a specific one:

    TRANSFORM Sum(qryPointMeasuredvalue.Measuredvalue) AS SumofMeasuredvalue
    SELECT qryPointMeasuredvalue.idxProperty, qryPointMeasuredvalue.TestSeriesNr
    FROM qryPointMeasuredvalue
    GROUP BY qryPointMeasuredvalue.idxProperty, qryPointMeasuredvalue.TestSeriesNr
    PIVOT qryPointMeasuredvalue.Point;

    It is however no problem to restrict the results of the query to one specific test series as e.g. test series 1 as follows:

    TRANSFORM Sum(qryPointMeasuredvalue.Measuredvalue) AS SumofMeasuredvalue
    SELECT qryPointMeasuredvalue.idxProperty, qryPointMeasuredvalue.TestSeriesNr
    FROM qryPointMeasuredvalue
    WHERE (((qryPointMeasuredvalue.TestSeriesNr)=1))
    GROUP BY qryPointMeasuredvalue.idxProperty, qryPointMeasuredvalue.TestSeriesNr
    PIVOT qryPointMeasuredvalue.Point;

    but I have to parameterize the query like

    TRANSFORM Sum(qryPointMeasuredvalue.Measuredvalue) AS SumofMeasuredvalue
    SELECT qryPointMeasuredvalue.idxProperty, qryPointMeasuredvalue.TestSeriesNr
    FROM qryPointMeasuredvalue
    WHERE (((qryPointMeasuredvalue.TestSeriesNr)=['TestSeriesNr']))
    GROUP BY qryPointMeasuredvalue.idxProperty, qryPointMeasuredvalue.TestSeriesNr
    PIVOT qryPointMeasuredvalue.Point;

    and in this case Access gives an error for not recognizing ['TestSeriesNr'] as a regular fieldname or -expression.

    How should I make the query, so that I get the desired result for the (only one) specified test series?

    Many thanks for your input!

    kaheiho

  2. #2
    Join Date
    Nov 2020
    Posts
    35
    Try using PARAMETERS clause http://allenbrowne.com/ser-67.html#Param

    Parameter should not be the field name and not within apostrophes.
    Last edited by June7; 02-20-2021 at 12:34 PM.

  3. #3
    Join Date
    Aug 2022
    Posts
    22
    In Microsoft Access 2003, you can use a parameterized query to restrict the results of a crosstab query. A parameterized query is a query that allows you to enter a parameter or value at runtime, which is then used to filter the results of the query.

    Here's an example of how you can create a parameterized crosstab query in Access 2003:

    1. Create a new query in the Design view.
    2. Select the Crosstab query option from the Query Type drop-down menu.
    3. Add the tables and fields that you want to use in the query, including the field that you want to use as the parameter.
    4. In the Crosstab row, add the field that will be the row header.
    5. In the Crosstab column, add the field that will be the column header.
    6. In the Crosstab value, add the field that will be used to calculate the values in the crosstab.
    7. In the Criteria row of the field, you want to use as the parameter, enter a prompt surrounded by square brackets, like this: [Enter a value].
    8. Run the query, and when prompted, enter a value for the parameter. The query will return the results that match the parameter value.

    You can also use a form to input the parameter value instead of being prompted each time, this way you can use the value from the form as a variable in the query.

    It's important to note that, if the query is complex, the performance may be affected by the use of the parameterized query, in those cases you may consider using an unparameterized query and filter the results using a form or a report.

Tags for this Thread

Posting Permissions

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