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

Question

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

Have you tried moving the contents of the mainMeas query right into the crosstab query? — DHW
Hi @DHW, thanks for helping me! I've tried your suggestion (i.e. what I've understood of it: basically replacing the SELECT and FROM clauses of the crosstab query by those of the TestqryMeasurement and making the necessary adjustements). I still get the same error message unfortunately! — jxrossel
Sorry - I am away from Access so I cannot type out a working query for you. But try this.. #1: make one query that has three fields (idTest, idMovement, and MeasDate). Group by the two id fields, and min the MeasDate field. Call this query 'SubMeas'. #2 Create a new query that has tblMeasurement and SubMeas added as tables to the query. Join the two tables with the idTest and idMovement fields. Add in all the tblMeasurement fields that you need, and add in the MinOfMeasDate field. You should now be able to change this query to a crosstab type query and get the results you are after. — DHW
@DHW Yes! That was the solution, avoiding the subquery by joining the query with a subset of itself. Neat and no loss of performances! Cleverly done, thanks a lot! Do you want to post an answer or do you prefer I write it myself? — jxrossel
You go ahead - its good practice so others can see the solution. — DHW

jxrossel jxrossel · Accepted Answer · 2013-10-03T12:09:30

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!

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

1 Answers