The Microsoft Engine Does not Recognize... as a Valid Field Name in CrossTab (3 Viewers)

[MajP]

I have a query that has a subquery and works fine. It looks at a percentage in table one for a given ID and compares it to the percentages in table 2 for a given id and returns another value where the difference between the percentages is min.

SELECT A.reg_number,
       A.epd_type                                AS NewEPD_Type,
       A.epd_percent,
       (SELECT TOP 1 B.val
        FROM   table2 AS B
        WHERE  B.epd = A.epd_type
        ORDER  BY Abs(B.[perc] - A.epd_percent)) AS Nearest
FROM   tblnew AS A;

Gives results like this

I want to make that into a CrossTab with Reg_Number as row header, and NewEPD_TYPE as Column Headers and show Nearest.

TRANSFORM First(qryvlookup.[Nearest]) AS firstofnearest
SELECT qryvlookup.[reg_number]
FROM   qryvlookup
GROUP  BY qryvlookup.[reg_number]
PIVOT qryvlookup.[NewEPD_Type];

The Cross tab complains about the fields within the Subquery. I tried with and without table aliases and get the same error.

I assume this is a limitation to the crosstab where I am trying to transform a value based on a subquery? Can the subquery (join) be done differently?

 

[Mark_]

For testing, does it work if you remove the "As NewEPD_Type"? Same with if you just add in A.epd_type? Alternately have you tried using NewEDP_Type instead of a.edp_type?

I remember having an issue like this with nested queries getting confused when parent fields were returned with a new name. Working it out as an SQL statement showed what was missing, but I haven't had to do this with a crosstab.

 

[MajP]

For testing, does it work if you remove the "As NewEPD_Type"? Same with if you just add in A.epd_type? Alternately have you tried using NewEDP_Type instead of a.edp_type?

I started with no table aliases, and got the error. Then tried aliasing table names and then field names. Every combination still gave me the same message relating to the subquery.

 

[isladogs]

This seems odd.
I've just successfully done something similar, successfully creating a crosstab based on a subquery with a similar layout to yours.

TRANSFORM First(SQ.TextField) AS FirstOfTextField
SELECT SQ.TextField
FROM qryTable2Subquery AS SQ
GROUP BY SQ.TextField
PIVOT SQ.ID;

No errors whether or not I use an alias.

Can you supply a dummy database with the original table , subquery & crosstab to help troubleshoot