Weird cross-tab question

C

Chad

I saw a similar question as this posted back in July but was not able to get
an answer. I am trying to create a crosstab query off of an existing query.
In the existing query (qryResidence) there is a subquery that is used to
populate one of the fields (Lease_Amt).

On its own qryResidence works fine. However, when I try to create a
crosstab based on this query, I get the error message stating:

'The Microsoft Access Database engine does not recognize '[Year_ID]' as a
valid field name or expression.'


The wierd part is...
After multiple attempts of including brackets and then excluding brackets, I
decided to try to run the crosstab based on qryResidence without the subquery
included and it worked fine. Is there something in subqueries that keeps
Access from being able to create crosstabs from the result?

************************************************************
Here is qryResidence with the subquery:
************************************************************

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 Temp1.Amount FROM tblResidence As Temp1 WHERE
Temp1.Residence_History_Code_ID=462 AND
Temp1.From_Date<=DateSerial([Year_ID],[Month_ID]+1,1)-1 AND
Temp1.To_Date>=DateSerial([Year_ID],[Month_ID]+1,1)-1 AND
tblResidence.[Residence_ID] = Temp1.[Residence_ID]) AS LeaseAmt,

(SELECT Temp2.Amount FROM tblResidence As Temp2 WHERE
Temp2.Residence_History_Code_ID=558 AND
Temp2.From_Date<=DateSerial([Year_ID],[Month_ID]+1,1)-1 AND
Temp2.To_Date>=DateSerial([Year_ID],[Month_ID]+1,1)-1 AND
tblResidence.[Residence_ID] = Temp2.[Residence_ID]) AS AppraisalAmt,

Day(DateSerial([Year_ID],[Month_ID],0)) AS DIM

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;

************************************************************
Here is it without the subqueries:
************************************************************

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, Day(DateSerial([Year_ID],[Month_ID],0)) AS
DIM

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;

************************************************************
And here is my crosstab query:
************************************************************

TRANSFORM Avg(qryResidence.[DIM]) AS AvgOfDIM
SELECT qryResidence.[Year_ID], qryResidence.[Residence_ID],
qryResidence.[Residence_Internal_Name]
FROM qryResidences
GROUP BY qryResidence.[Year_ID], qryResidence.[Residence_ID],
qryResidence.[Residence_Internal_Name]
PIVOT qryResidence.[Month_ID];


Thanks in advance for everyone's help.

Chad
 
D

Duane Hookom

Crosstabs don't like subqueries. You can generally replace the subqueries
with very slow and in-efficient domain aggregate functions. For instance,
replace your first subquery with (assuming Residence_ID is numeric):
DLookup("Amount","tblResidence", "Residence_History_Code_ID=462 AND
From_Date<=#" & DateSerial([Year_ID],[Month_ID]+1,1)-1 & #" AND
To_Date>=#" & DateSerial([Year_ID],[Month_ID]+1,1)-1 & "# AND
[Residence_ID] = " & [Residence_ID]) AS LeaseAmt,

--
Duane Hookom
Microsoft Access MVP


Chad said:
I saw a similar question as this posted back in July but was not able to get
an answer. I am trying to create a crosstab query off of an existing query.
In the existing query (qryResidence) there is a subquery that is used to
populate one of the fields (Lease_Amt).

On its own qryResidence works fine. However, when I try to create a
crosstab based on this query, I get the error message stating:

'The Microsoft Access Database engine does not recognize '[Year_ID]' as a
valid field name or expression.'


The wierd part is...
After multiple attempts of including brackets and then excluding brackets, I
decided to try to run the crosstab based on qryResidence without the subquery
included and it worked fine. Is there something in subqueries that keeps
Access from being able to create crosstabs from the result?

************************************************************
Here is qryResidence with the subquery:
************************************************************

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 Temp1.Amount FROM tblResidence As Temp1 WHERE
Temp1.Residence_History_Code_ID=462 AND
Temp1.From_Date<=DateSerial([Year_ID],[Month_ID]+1,1)-1 AND
Temp1.To_Date>=DateSerial([Year_ID],[Month_ID]+1,1)-1 AND
tblResidence.[Residence_ID] = Temp1.[Residence_ID]) AS LeaseAmt,

(SELECT Temp2.Amount FROM tblResidence As Temp2 WHERE
Temp2.Residence_History_Code_ID=558 AND
Temp2.From_Date<=DateSerial([Year_ID],[Month_ID]+1,1)-1 AND
Temp2.To_Date>=DateSerial([Year_ID],[Month_ID]+1,1)-1 AND
tblResidence.[Residence_ID] = Temp2.[Residence_ID]) AS AppraisalAmt,

Day(DateSerial([Year_ID],[Month_ID],0)) AS DIM

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;

************************************************************
Here is it without the subqueries:
************************************************************

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, Day(DateSerial([Year_ID],[Month_ID],0)) AS
DIM

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;

************************************************************
And here is my crosstab query:
************************************************************

TRANSFORM Avg(qryResidence.[DIM]) AS AvgOfDIM
SELECT qryResidence.[Year_ID], qryResidence.[Residence_ID],
qryResidence.[Residence_Internal_Name]
FROM qryResidences
GROUP BY qryResidence.[Year_ID], qryResidence.[Residence_ID],
qryResidence.[Residence_Internal_Name]
PIVOT qryResidence.[Month_ID];


Thanks in advance for everyone's help.

Chad
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

subquery help 1
At most one record can be returned... 7

Top