K
kohai
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
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