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

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

Pivot with multiple data field in Access 0
Display Record based on value of combination of 2 fields 4
circular reference 3
Select Query Opposite 2
ERROR 3125 switching to "totals" aggregate function 3
Storing query criteria captured in a form 5
Union Query if Yex/No 13
Union query 5

Top