CODEWITHSUNDEEP
ms-access
jxrossel
jxrossel

Reputation: 91

ms-access crosstab query based on a query containing a subquery

I have a problem with a crosstab query based on a query that contains a sub-query. The query itself works fine, but when I use it in the crosstab query, I receive an error message related to an unrecognized field name and pointing to a field used in the subquery. The strange thing is that I can define a pivot-table or a pivot-chart on this query without trouble.

So, the query TestqryMeasurement is defined by:

SELECT *, 
    (SELECT min( subMeas.MeasDate ) FROM tblMeasurement AS subMeas 
     WHERE subMeas.idTest = mainMeas.idTest AND subMeas.idMovement = 
     mainMeas.idMovement) AS FirstMeasDate, 
    DateDiff("d",FirstMeasDate,MeasDate) AS DaysSinceFirstMeas
FROM tblMeasurement AS mainMeas;

It is mainly used to determine the time spent between the first measurement and the current one for a given movement in a given test. When running this query, I get the correct results.

The crosstab query is then written:

TRANSFORM Sum(TESTqryMeasurement.[Amplitude]) AS SumOfAmplitude
SELECT TESTqryMeasurement.[DaysSinceFirstMeas], 
    Sum(TESTqryMeasurement.[Amplitude]) AS [Total Of Amplitude]
FROM TESTqryMeasurement
GROUP BY TESTqryMeasurement.[DaysSinceFirstMeas]
PIVOT TESTqryMeasurement.[idMovement];

When I run this, I get the message:

... database engine does not recognize 'mainMeas.idTest' as a valid field name or expression

This field is used only in the sub-query of TestqryMeasurement which, by itself, works.

Any idea?

NB: I have Ms-Access v.14, part of MS-Office 2010

-- EDIT --

I manage to get things working by replacing the sub-query by a VBA function which returns the date of the 1st measurement. This solution is nonetheless not too good because the loss of performance of the crosstab-query is huge (with or without the calculated field). The weird thing is that the query itself has comparable performances with the VBA function or the equivalent sub-query...

Upvotes: 0

Views: 2175

Answers (1)

jxrossel
jxrossel

Reputation: 91

The answer has been given to me by @DHW. The idea is to avoid the subquery altogether by joining the query with a subset of itself, the subset containing the results of the desired subquery. This not only works, but also avoids the loss of performance met when the subquery is replaced by a call to a custom VBA function.

In my case, the solution is given by:

1) Defining the query - qryFirstMeasDate - giving the first measurement date for each test and movement:

SELECT idTest, idMovement, min( MeasDate ) AS FirstMeasDate
FROM tblMeasurement
GROUP BY idTest, idMovement;

2) Joining this query with the main measurement table to get the overall measurement query qryMeasurement:

SELECT tblMeasurement.*, FirstMeasDate, DateDiff("d",FirstMeasDate,MeasDate) AS   
   DaysSinceFirstMeas
FROM tblMeasurement INNER JOIN qryFirstMeasDate ON 
   (tblMeasurement.idMovement = qryFirstMeasDate.idMovement) AND 
   (tblMeasurement.idTest = qryFirstMeasDate.idTest);

3) Writing the crosstab query on qryMeasurement as done in the question post.

Thanks again to DHW!

Upvotes: 1

Related Questions