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

MGFoster

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

You can get rid of the sub-queries like this (use IIf() function in the
SELECT clause; use table Aliases [for readability]; use the IN ()
predicate in the WHERE clause; and get rid of those dang parentheses in
the WHERE clause):

SELECT Y.Year_ID, M.Month_ID, D.Region_ID, D.Experience_Code_Name,
R.Destination, R.Residence_ID, R.GL_Acct, R.Residence_Internal_Name,
R.OwnershipType, R.Acquisition_Origin,

SUM(IIf(R.Residence_History_Code_ID = 462, Amount, NULL)) AS LeaseAmt,

SUM(IIf(R.Residence_History_Code_ID = 558, Amount, NULL)) AS
AppraisalAmt,

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


FROM tblMonths As M, tblYears As Y, tblResidence As R INNER JOIN
tblDestinations As D ON R.Destination = D.Name

WHERE R.Residence_History_Code_ID IN (461, 462, 558)
AND R.From_Date<=DateSerial(Y.[Year_ID],M.[Month_ID]+1,1)-1)
AND R.To_Date>=DateSerial(Y.[Year_ID],M.[Month_ID]+1,1)-1))
GROUP BY Y.Year_ID, M.Month_ID, D.Region_ID, D.Experience_Code_Name,
R.Destination, R.Residence_ID, R.GL_Acct, R.Residence_Internal_Name,
R.OwnershipType, R.Acquisition_Origin,
Day(DateSerial(Y.[Year_ID],M.[Month_ID],0))

Since you were using the same criteria for your sub-queries as your main
query, and you were using the same table as the main query I saw that
you could use the IN () predicate for the three Residence History Codes
and use the IIf() function in the SELECT clause to separate the Lease
Amt from the Appraisal Amt. When the Residence History Code = 461 both
the Lease Amt and the Appraisal Amt will be NULL (or zero when SUMmed).
I had to SUM() the values so you'd get all the data in one row (Record)
providing all the other columns (Fields) are equal.

--
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/AwUBSUcDioechKqOuFEgEQKYjwCgqd3P0MOvWdjfjAYlpoIWnxdHPOcAn1bg
w+XVsv/fJSgFqDb0shkYbHFx
=iqar
-----END PGP SIGNATURE-----
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