Results 1 to 2 of 2

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
    13
    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.

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
  •