subquery help

[Chad]

Hello everyone. Please excuse my ignorance as I am trying to get my feet wet
with SQL. I am trying to run a query that has an imbedded subquery (see
below):

************************************************************

SELECT tblYears.Year_ID, tblMonths.Month_ID, tblDestinations.Region_ID,
tblDestinations.[Experience Code Name], tblResidence.Destination,
tblResidence.[Residence ID], tblResidence.[GL Acct], tblResidence.[Residence
Internal Name], tblResidence.OwnershipType, tblResidence.[Acquisition Origin],

(SELECT tblResidence.Amount
FROM tblResidence
WHERE (((tblResidence.[Residence History Code ID])=462) AND
((tblResidence.[From Date])<=DateSerial([Year_ID],[Month_ID]+1,1)-1) AND
((tblResidence.[To Date])>=DateSerial([Year_ID],[Month_ID]+1,1)-1)) AND
[Residence ID]=[Residence ID]) AS LeaseAmt

FROM tblMonths, tblYears, tblResidence INNER JOIN tblDestinations ON
tblResidence.Destination = tblDestinations.Name

WHERE (((tblResidence.[Residence History Code ID])=461) AND
((tblResidence.[From Date])<=DateSerial([Year_ID],[Month_ID]+1,1)-1) AND
((tblResidence.[To Date])>=DateSerial([Year_ID],[Month_ID]+1,1)-1))

GROUP BY tblYears.Year_ID, tblMonths.Month_ID, tblDestinations.Region_ID,
tblDestinations.[Experience Code Name], tblResidence.Destination,
tblResidence.[Residence ID], tblResidence.[GL Acct], tblResidence.[Residence
Internal Name], tblResidence.OwnershipType, tblResidence.[Acquisition Origin];

************************************************************

In a nutshell I am trying to link the subquery to Month_ID, Year_ID, and
[Residence ID] in the output of the original query. Is this the proper way
to handle this type of task or should I use some kind of self join
relationship?

In particular the line item in the subquery that I think is giving my query
problems is:

[Residence ID]=[Residence ID]

Any help would be greatly appreciated.

Thanks!

Chad

 

[Tom van Stiphout]

On Sat, 13 Dec 2008 16:32:01 -0800, Chad

I would use a self-join, but if you want to use a subquery you should
give tblResidence an alias:
.... from tblResidence as Sub

Then you can say:
....where tblResidence.[Residence ID] = Sub.[Residence ID]

I would also drop the spaces from the field names. Too many [] for my
taste.

-Tom.
Microsoft Access MVP