Circular Reference error message on a query

  • Thread starter NoviceAccessUser-Melanie
  • Start date
N

NoviceAccessUser-Melanie

I had these queries working before but made a change by adding a parameter to
a Crosstab query which is a series of queries I've created to generate a
report.

I have a Union query: qry6B
SELECT Fund, SponsorName, DealExpr1,DealID, DealNumber, Ind, Indsort2,
YearID, TCAmount, SAPCo, LEID, LeaseNo, OrigProformaDte, LIHTCYr1, HTCYr1,
StCredYR1, LossesYr1, DiscountYr1, DiscountedProformaDte, DLIHTCYr1, DHTCYr1,
DStCredYR1, DDiscountYr1, ActualsDte, ALIHTCYr1, AHTCYr1, AStCredYR1,
ALossesYr1, ADiscountYr1, [Original Commitment]
FROM qry2B 1
ORDER BY SponsorName

UNION SELECT Fund, SponsorName, DealExpr1, DealID, DealNumber, Ind,
Indsort2, YearID, TCAmount, SAPCo, LEID, LeaseNo, OrigProformaDte, LIHTCYr1,
HTCYr1, StCredYR1, LossesYr1, DiscountYr1, DiscountedProformaDte, DLIHTCYr1,
DHTCYr1, DStCredYR1, DDiscountYr1, ActualsDte, ALIHTCYr1, AHTCYr1,
AStCredYR1, ALossesYr1, ADiscountYr1, [Original Commitment]
FROM Qry5B 1
ORDER BY SponsorName;

Then from this Union query I run a Crosstab query to flip the data: qry7
PARAMETERS [Forms]![frmSelectSponsor]![txtSponsor] Text ( 255 );
TRANSFORM First(IIf(IsNull([qry6B].[TCAmount]),"",[qry6B].[TCAmount])) AS
TCAmount1
SELECT qry6B.SponsorName, qry6B.Fund, qry6B.DealID, qry6B.DealExpr1,
qryDealTaxIndicators2.Ind, qryDealTaxIndicators2.Indsort2, qry6B.SAPCo,
qry6B.LEID, qry6B.LeaseNo, qry6B.OrigProformaDte, qry6B.LIHTCYr1,
qry6B.HTCYr1, qry6B.StCredYR1, qry6B.LossesYr1, qry6B.[Original Commitment],
Sum(IIf(IsNull([TCAmount1]),"",[TCAmount1])) AS TCAmountSum
FROM qryDealTaxIndicators2 LEFT JOIN qry6B ON qryDealTaxIndicators2.Ind =
qry6B.Ind
GROUP BY qry6B.SponsorName, qry6B.Fund, qry6B.DealID, qry6B.DealExpr1,
qryDealTaxIndicators2.Ind, qryDealTaxIndicators2.Indsort2, qry6B.SAPCo,
qry6B.LEID, qry6B.LeaseNo, qry6B.OrigProformaDte, qry6B.LIHTCYr1,
qry6B.HTCYr1, qry6B.StCredYR1, qry6B.LossesYr1, qry6B.DiscountYr1,
qry6B.DiscountedProformaDte, qry6B.DLIHTCYr1, qry6B.DHTCYr1,
qry6B.DStCredYR1, qry6B.DDiscountYr1, qry6B.ActualsDte, qry6B.ALIHTCYr1,
qry6B.AHTCYr1, qry6B.AStCredYR1, qry6B.ALossesYr1, qry6B.ADiscountYr1,
qry6B.[Original Commitment]
ORDER BY qry6B.SponsorName, qry6B.DealExpr1, qryDealTaxIndicators2.Ind,
qryDealTaxIndicators2.Indsort2
PIVOT qry6B.YearID;

Then to make it simpler to bring in the field to the report, I do another
simple Select query from the results : qry8

SELECT qry7.SponsorName, qry7.Fund, qry7.DealID, qry7.DealExpr1, qry7.Ind,
qry7.Indsort2, qry7.SAPCo, qry7.LEID, qry7.LeaseNo, qry7.OrigProformaDte,
qry7.[Original Commitment], qry7.LIHTCYr1, qry7.HTCYr1, qry7.StCredYR1,
qry7.LossesYr1, qry7.[1], qry7.[2], qry7.[3], qry7.[4], qry7.[5], qry7.[6],
qry7.[7], qry7.[8], qry7.[9], qry7.[10], qry7.[11], qry7.[12], qry7.[13],
qry7.TCAmountSum
FROM qry7;


Now when I run qry8, I get the error message : "Circular reference caused by
qry7"

None of my field names or query names have changed, so I don't understand
what is causing this error message to pop up now. Please help or direct me on
what else to check. Thank you.
 
J

Jerry Whittle

Crosstab queries don't like parameters very much. Try creating another query
based on the union query. Put the parameter there. Then base the crosstab on
that query.
--
Jerry Whittle - MS Access MVP 2007 - 2009
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder


NoviceAccessUser-Melanie said:
I had these queries working before but made a change by adding a parameter to
a Crosstab query which is a series of queries I've created to generate a
report.

I have a Union query: qry6B
SELECT Fund, SponsorName, DealExpr1,DealID, DealNumber, Ind, Indsort2,
YearID, TCAmount, SAPCo, LEID, LeaseNo, OrigProformaDte, LIHTCYr1, HTCYr1,
StCredYR1, LossesYr1, DiscountYr1, DiscountedProformaDte, DLIHTCYr1, DHTCYr1,
DStCredYR1, DDiscountYr1, ActualsDte, ALIHTCYr1, AHTCYr1, AStCredYR1,
ALossesYr1, ADiscountYr1, [Original Commitment]
FROM qry2B 1
ORDER BY SponsorName

UNION SELECT Fund, SponsorName, DealExpr1, DealID, DealNumber, Ind,
Indsort2, YearID, TCAmount, SAPCo, LEID, LeaseNo, OrigProformaDte, LIHTCYr1,
HTCYr1, StCredYR1, LossesYr1, DiscountYr1, DiscountedProformaDte, DLIHTCYr1,
DHTCYr1, DStCredYR1, DDiscountYr1, ActualsDte, ALIHTCYr1, AHTCYr1,
AStCredYR1, ALossesYr1, ADiscountYr1, [Original Commitment]
FROM Qry5B 1
ORDER BY SponsorName;

Then from this Union query I run a Crosstab query to flip the data: qry7
PARAMETERS [Forms]![frmSelectSponsor]![txtSponsor] Text ( 255 );
TRANSFORM First(IIf(IsNull([qry6B].[TCAmount]),"",[qry6B].[TCAmount])) AS
TCAmount1
SELECT qry6B.SponsorName, qry6B.Fund, qry6B.DealID, qry6B.DealExpr1,
qryDealTaxIndicators2.Ind, qryDealTaxIndicators2.Indsort2, qry6B.SAPCo,
qry6B.LEID, qry6B.LeaseNo, qry6B.OrigProformaDte, qry6B.LIHTCYr1,
qry6B.HTCYr1, qry6B.StCredYR1, qry6B.LossesYr1, qry6B.[Original Commitment],
Sum(IIf(IsNull([TCAmount1]),"",[TCAmount1])) AS TCAmountSum
FROM qryDealTaxIndicators2 LEFT JOIN qry6B ON qryDealTaxIndicators2.Ind =
qry6B.Ind
GROUP BY qry6B.SponsorName, qry6B.Fund, qry6B.DealID, qry6B.DealExpr1,
qryDealTaxIndicators2.Ind, qryDealTaxIndicators2.Indsort2, qry6B.SAPCo,
qry6B.LEID, qry6B.LeaseNo, qry6B.OrigProformaDte, qry6B.LIHTCYr1,
qry6B.HTCYr1, qry6B.StCredYR1, qry6B.LossesYr1, qry6B.DiscountYr1,
qry6B.DiscountedProformaDte, qry6B.DLIHTCYr1, qry6B.DHTCYr1,
qry6B.DStCredYR1, qry6B.DDiscountYr1, qry6B.ActualsDte, qry6B.ALIHTCYr1,
qry6B.AHTCYr1, qry6B.AStCredYR1, qry6B.ALossesYr1, qry6B.ADiscountYr1,
qry6B.[Original Commitment]
ORDER BY qry6B.SponsorName, qry6B.DealExpr1, qryDealTaxIndicators2.Ind,
qryDealTaxIndicators2.Indsort2
PIVOT qry6B.YearID;

Then to make it simpler to bring in the field to the report, I do another
simple Select query from the results : qry8

SELECT qry7.SponsorName, qry7.Fund, qry7.DealID, qry7.DealExpr1, qry7.Ind,
qry7.Indsort2, qry7.SAPCo, qry7.LEID, qry7.LeaseNo, qry7.OrigProformaDte,
qry7.[Original Commitment], qry7.LIHTCYr1, qry7.HTCYr1, qry7.StCredYR1,
qry7.LossesYr1, qry7.[1], qry7.[2], qry7.[3], qry7.[4], qry7.[5], qry7.[6],
qry7.[7], qry7.[8], qry7.[9], qry7.[10], qry7.[11], qry7.[12], qry7.[13],
qry7.TCAmountSum
FROM qry7;


Now when I run qry8, I get the error message : "Circular reference caused by
qry7"

None of my field names or query names have changed, so I don't understand
what is causing this error message to pop up now. Please help or direct me on
what else to check. Thank you.
Click to expand...
 
Top