Subquery Assistance

[John]

I have been fighting with this most of the day and can't seem to spot why
this will not work:

SELECT tblProjInfo.Period AS ConstPrd, Format([Period_Dte],"mmm-yy") AS
RptDte, tblProjInfo.PlanPct AS OriginalPlan, tblProjInfo.CurPCT AS
CurrentPlan,
(SELECT TOP 1 tblDupe.PlanPct FROM tblProjInfo AS tblDupe
WHERE tblDupe.Period = tblProjInfo.Period
AND tblDupe.Period < tblProjInfo.Period
ORDER BY tblDupe.Period DESC) AS PriorPCT
FROM tblProjInfo
GROUP BY tblProjInfo.Period, Format([Period_Dte],"mmm-yy"),
tblProjInfo.PlanPct, tblProjInfo.CurPCT
HAVING (((tblProjInfo.Period)>=0));

The Period field is an integer 0 - 52. The PlanPct field is a number,
formated as Percent. When this runs, all the fields show up, but there is no
data in the "PriorPCT" field (subquery), just blank.

If anyone can spot what is causing this I would be greatful...

 

[John Spencer]

SELECT tblProjInfo.Period AS ConstPrd, Format([Period_Dte],"mmm-yy") AS
RptDte, tblProjInfo.PlanPct AS OriginalPlan, tblProjInfo.CurPCT AS
CurrentPlan,
(SELECT TOP 1 tblDupe.PlanPct FROM tblProjInfo AS tblDupe
WHERE tblDupe.Period = tblProjInfo.Period
AND tblDupe.Period < tblProjInfo.Period
ORDER BY tblDupe.Period DESC) AS PriorPCT
FROM tblProjInfo
GROUP BY tblProjInfo.Period, Format([Period_Dte],"mmm-yy"),
tblProjInfo.PlanPct, tblProjInfo.CurPCT
HAVING (((tblProjInfo.Period)>=0));


You can't have a record where tblDupe.Period is equal to two different
values at the same time. Which is what the WHERE clause says. Either
use OR in place of the AND in the subquery or just use <=

(SELECT TOP 1 tblDupe.PlanPct FROM tblProjInfo AS tblDupe
WHERE tblDupe.Period <= tblProjInfo.Period
ORDER BY tblDupe.Period DESC) AS PriorPCT

Although I would expect if you want prior period you don't want the
Equal record at all. So the query becomes more like

(SELECT TOP 1 tblDupe.PlanPct FROM tblProjInfo AS tblDupe
WHERE tblDupe.Period <tblProjInfo.Period
ORDER BY tblDupe.Period DESC) AS PriorPCT

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Noëlla Gabriël]

Hi John,

as I see it you have added the subquery as a calculated field, which will
always show up. Try to make a seperate query containing the project ID field
+ the desired value, and then link it to tblProjInfo to achieve the final
query.

 

[John]

As usual John, you are spot on! Thanks for the help.
--
Thanks in advance!
**John**

SELECT tblProjInfo.Period AS ConstPrd, Format([Period_Dte],"mmm-yy") AS
RptDte, tblProjInfo.PlanPct AS OriginalPlan, tblProjInfo.CurPCT AS
CurrentPlan,
(SELECT TOP 1 tblDupe.PlanPct FROM tblProjInfo AS tblDupe
WHERE tblDupe.Period = tblProjInfo.Period
AND tblDupe.Period < tblProjInfo.Period
ORDER BY tblDupe.Period DESC) AS PriorPCT
FROM tblProjInfo
GROUP BY tblProjInfo.Period, Format([Period_Dte],"mmm-yy"),
tblProjInfo.PlanPct, tblProjInfo.CurPCT
HAVING (((tblProjInfo.Period)>=0));


You can't have a record where tblDupe.Period is equal to two different
values at the same time. Which is what the WHERE clause says. Either
use OR in place of the AND in the subquery or just use <=

(SELECT TOP 1 tblDupe.PlanPct FROM tblProjInfo AS tblDupe
WHERE tblDupe.Period <= tblProjInfo.Period
ORDER BY tblDupe.Period DESC) AS PriorPCT

Although I would expect if you want prior period you don't want the
Equal record at all. So the query becomes more like

(SELECT TOP 1 tblDupe.PlanPct FROM tblProjInfo AS tblDupe
WHERE tblDupe.Period <tblProjInfo.Period
ORDER BY tblDupe.Period DESC) AS PriorPCT

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I have been fighting with this most of the day and can't seem to spot why
this will not work:

SELECT tblProjInfo.Period AS ConstPrd, Format([Period_Dte],"mmm-yy") AS
RptDte, tblProjInfo.PlanPct AS OriginalPlan, tblProjInfo.CurPCT AS
CurrentPlan,
(SELECT TOP 1 tblDupe.PlanPct FROM tblProjInfo AS tblDupe
WHERE tblDupe.Period = tblProjInfo.Period
AND tblDupe.Period < tblProjInfo.Period
ORDER BY tblDupe.Period DESC) AS PriorPCT
FROM tblProjInfo
GROUP BY tblProjInfo.Period, Format([Period_Dte],"mmm-yy"),
tblProjInfo.PlanPct, tblProjInfo.CurPCT
HAVING (((tblProjInfo.Period)>=0));

The Period field is an integer 0 - 52. The PlanPct field is a number,
formated as Percent. When this runs, all the fields show up, but there is no
data in the "PriorPCT" field (subquery), just blank.

If anyone can spot what is causing this I would be greatful...