Help! Spot the subquery error?

[Jaazaniah]

Ok, I'm having to re-vamp a reporting system, and in the midst, was
altering this query to work with the new underlaying queries.However,
it appears that the subquery YTD is now bugging out and this query
won't run, saying [mpl].[Year] is not a valid fieldname (or
expression). I've scoured this query many times and can't figure out
why the subquery can't refer to mpl properly. As a further point, the
query runs perfectly as expected without the subquery field [YTD]. Any
advise would be appreciated.

SELECT mpl.HiddenLink, mpl.ChartGroup, mpl.Year, mpl.Month,
mpl.Procedure, qryMonthlyInPatients.*, qryMonthlyOutPatients.*,
mpr2.Totals, (select Nz(sum([Totals]),0) as Sums from
[qryMonthlyProductionReport2] as mpr1 where [mpr1].[Year]=[mpl].[Year]
AND [mpr1].[Procedure]=[mpl].[Procedure] AND [mpr1].[Month]<=[mpl].
[Month]) AS YTD
FROM qryMonthlyInPatients RIGHT JOIN (qryMonthlyOutPatients RIGHT JOIN
(qryMonthlyProductionList AS mpl LEFT JOIN qryMonthlyProductionReport2
AS mpr2 ON mpl.HiddenLink = mpr2.HiddenLink) ON
qryMonthlyOutPatients.HiddenLink = mpl.HiddenLink) ON
qryMonthlyInPatients.HiddenLink = mpl.HiddenLink
ORDER BY mpl.HiddenLink;

 

[ErezM via AccessMonster.com]

hi
i'm sorry i'm not bringing any good news (or solutions), but still:

dont use reserved words (or function names) as column names in your tables or
queries, it's a very bad habit that will only cause you head aches if you're
lucky, and much worse if that's your or others work
so "month" and "year" are going out right?

second: you say the query riuns well without the syb query? how does the sub
query run when placed on it's own (that is the way i construct sub queries,
first build them on their own and test them, and them copy and paste them a
sub queries...

now for the guessing part: try to use the actual qualifier (query or table
names) in the sub query and not the alias (which is declared only when the
sub query has returned it's results)

good luck

Ok, I'm having to re-vamp a reporting system, and in the midst, was
altering this query to work with the new underlaying queries.However,
it appears that the subquery YTD is now bugging out and this query
won't run, saying [mpl].[Year] is not a valid fieldname (or
expression). I've scoured this query many times and can't figure out
why the subquery can't refer to mpl properly. As a further point, the
query runs perfectly as expected without the subquery field [YTD]. Any
advise would be appreciated.

SELECT mpl.HiddenLink, mpl.ChartGroup, mpl.Year, mpl.Month,
mpl.Procedure, qryMonthlyInPatients.*, qryMonthlyOutPatients.*,
mpr2.Totals, (select Nz(sum([Totals]),0) as Sums from
[qryMonthlyProductionReport2] as mpr1 where [mpr1].[Year]=[mpl].[Year]
AND [mpr1].[Procedure]=[mpl].[Procedure] AND [mpr1].[Month]<=[mpl].
[Month]) AS YTD
FROM qryMonthlyInPatients RIGHT JOIN (qryMonthlyOutPatients RIGHT JOIN
(qryMonthlyProductionList AS mpl LEFT JOIN qryMonthlyProductionReport2
AS mpr2 ON mpl.HiddenLink = mpr2.HiddenLink) ON
qryMonthlyOutPatients.HiddenLink = mpl.HiddenLink) ON
qryMonthlyInPatients.HiddenLink = mpl.HiddenLink
ORDER BY mpl.HiddenLink;