Reference Calculated Field

[Chad]

I have a query that calculates a field 'Rep_Per', using a subquery. I need
to use this value in the criteria for other fields. Is there any way besides
continuously using the subquery in the WHERE statement? Doing it this way
results in the query taking about 40 sec. to run.

I am not able to use the subquery to create a temp table because I am using
joins in the main query.

Thanks for yoru help,
Chad

 

[Chad]

My query can be found below. It is pretty cumbersome, however, this is what
I would LIKE my query to look like. Currently I have to replace the
'Res_Per' field in the criteria with the subquery that is used to calculate
'Res_Per'

Thanks for your help.

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

SELECT
tblOccupancy.Club_Name,
tblOccupancy.Residence_ID,
tblOccupancy.Dest_Type,
tblOccupancy.Destination,
tblOccupancy.Acquisition_Origin,
IIf(Sum([tblOccupancy_1].[Amount])*1<=0,0,IIf(Sum([tblOccupancy].[Amount])*1>Sum([tblOccupancy_1].[Amount])*1,1,Sum([tblOccupancy].[Amount])/Sum([tblOccupancy_1].[Amount]))) AS Occupancy,
Sum(tblOccupancy.Amount) AS Reserved,
Sum(tblOccupancy_1.Amount) AS Available,
tblOccupancy.Acquisition_Origin,
(SELECT Max(DateSerial([Year_Value],[Period]+1,0)) From tblFinancials) As
Res_Per

FROM
tblResidence RIGHT JOIN (tblOccupancy INNER JOIN tblOccupancy AS
tblOccupancy_1 ON (tblOccupancy.Residence_ID = tblOccupancy_1.Residence_ID)
AND (tblOccupancy.Year_ID = tblOccupancy_1.Year_ID) AND
(tblOccupancy.Month_ID = tblOccupancy_1.Month_ID)) ON
tblResidence.Residence_ID = tblOccupancy.Residence_ID

WHERE
(((tblOccupancy.Year_ID)=Year(Res_Per)) AND ((tblOccupancy.Type)="Days
Reserved") AND ((tblOccupancy_1.Type)="Days Available") AND
((tblOccupancy.Acquisition_Origin)<>"PE")) OR
(((tblOccupancy.Year_ID)=Year(Res_Per)-1) AND ((tblOccupancy.Type)="Days
Reserved") AND ((tblOccupancy_1.Type)="Days Available") AND
((tblOccupancy.Acquisition_Origin)<>"PE") AND
((tblOccupancy.Month_ID)>Month(Res_Per)))

GROUP BY
tblOccupancy.Club_Name,
tblOccupancy.Residence_ID,
tblOccupancy.Dest_Type,
tblOccupancy.Destination,
tblOccupancy.Acquisition_Origin,
tblResidence.Residence_History_Code_ID,
tblResidence.From_Date,
tblResidence.To_Date

HAVING (((tblResidence.Residence_History_Code_ID)=461) AND
((tblResidence.From_Date)<=DateAdd("yyyy",-1,Res_Per)) AND
((tblResidence.To_Date)>=Res_Per)) OR
(((tblResidence.Residence_History_Code_ID)=461) AND
((tblResidence.From_Date)<=DateAdd("yyyy",-1,Res_Per)) AND
((tblResidence.To_Date)>=Res_Per));

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

Chad

 

[dhod]

Chad,

This may be too low level to help at all. I have a query I am using for YTD
financial values. To to this I'm using the DSum formula to generate a
running total for the year. The formuala, in design view of Access, is:

YTD_Revenue: Val(DSum("Revenue","Revenue_Current","DatePart('m', [Date])<="
& [Month] & " And DatePart('yyyy', [Date])=" & [Year] & ""))

The [Month] and [Year] fields are calculated from Date within the same
query. The " & [FIELD] & " appears to be the format to get it to look at the
calculated value within the query.

Good Luck.