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

 

[MGFoster]

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.

1. Show the SQL for this query.
2. Describe how you'd use the result of the sub-query as the criteria
for the rest of the query: Give examples.

 

[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

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You can't refer to aliased columns that are in the SELECT clause in the
same query's WHERE, GROUP BY, or HAVING clauses. You have to use the
aliased column's formula in those clauses because the SELECT clause is
processed after those clauses and the aliased formula results don't
exist when those clauses are run.

The Res_Per formula could be more efficient as a derived table that is
part of the FROM clause:

FROM (SELECT Max(Year_Value) As MaxYear, Max([Period])+1 As MaxPeriod
From tblFinancials) As ResPer, (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)

Sometimes Access won't allow this set up, even though it is a legitimate
syntax. The thing to do is disconnect all the joins and put in the
derived table, then rejoin the other tables, as above, from the Design
Grid view. If that doesn't work put the derived table in a separate
query and just use the name of the query in the FROM clause (starting
with un-joined tables/queries). Once that's working use the MaxYear and
MaxPeriod in a DateSerial() function in the SELECT clause:

SELECT ...
DateSerial(ResPer.MaxYear, ResPer.MaxPeriod, 0) As Res_Per
FROM ...

Using MAX(DateSerial(...)) causes the query to "look" at every row
(record) in order to compute the DateSerial() & then the MAX(). Using
my way, if your Year and Period columns are indexed, will be much faster
'cuz only the relevant (max) rows will be "looked" at.

The contents of the HAVING clause should be part of the WHERE clause.
The HAVING clause is run after the GROUP BY clause has marshaled all the
selected rows. It's faster to eliminate non-relevant rows during the
first pass thru the data (when the WHERE clause runs).

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSY54uIechKqOuFEgEQIIEwCgnmddESVsRlFt7mMnyOY737tzNTsAmwWW
5idwpmkIn6czgHlwXJdQgVvC
=N7x7
-----END PGP SIGNATURE-----

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

1. Show the SQL for this query.
2. Describe how you'd use the result of the sub-query as the criteria
for the rest of the query: Give examples.

 

[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.