Can't get to work as Crosstab

[Guest]

Hi,

I am trying to get the following query to work as a crosstab query, with the
dates as row header and the ID's as column headers and the PE's as the
values.

Depending on the ID selected, the selected ID will have its PE shown, while
all of the other ID's will have their PE relative to the selected ID (B / A).

I have it working as a select query, but if I switch it to crosstab, I get
an error that it does not recognize the PEDate as a valid field name or
expression. If I change the reference to the actual table name, it still
doesn't work. What am I missing?

SELECT a.PEDate, a.ID,
IIf(a.ID=10,a.[NTM_PE_MEDx125],a.[NTM_PE_MEDx125]/(SELECT b.[NTM_PE_MEDx125]
FROM PE_FYNTM as b
WHERE (b.ID=10) AND(b.PEDate = a.[PEDate]))) AS ISPE
FROM PE_FYNTM AS a
ORDER BY a.PEDate, a.Sect;



Thank you for your help.

Regards,

kohai

 

[George Nicholson]

I would use 2 separate queries: do your select query and then base your
crosstab off of that.

My experience is that Crosstabs are very, very single minded. Your Iif() and
WHERE are simply beyond its capabilities (IMO)

HTH,