Problems correcting Circular Reference!

  • Thread starter Thread starter eric
  • Start date Start date
E

eric

Hi,

I have created two queries: A and B within an already
esisting database.

Unfortunately, now when I try to open query A, I get an
error message: 'Circular reference caused by A' and when I
try to open B, I get an error message 'Circular reference
caused by B'.

When I try to 'correct' these queries in Design View,
initially I am not allowed to do so - and I am
automatically presented with the SQL view. When I attempt
to make some corrects in the SQL view - then I can view
the datasheet view. However, if I try to save the
corrected query, ACCESS shuts down.

What's going wrong here? How can I correct the new
queriest and stop the 'circular reference' process?

Thank you!

Eric
 
You could probably start by posting the SQL of your queries. We have trouble
seeing them from our vantage point.
 
Hi,
Here is the Engagement_Level1_pre query SQL statement:

SELECT [PRENATAL CLIENT CONTACT].[ID], [PRENATAL CLIENT
CONTACT].[Dtvisit], 100*([Dtvisit]-[Enrolldt])/[Opportun]
AS [%opsused], IIf([Phone]=1,"",[EMomInvl]) AS EMmInvl2,
[PRENATAL CLIENT CONTACT].[EMomInvl], [PRENATAL CLIENT
CONTACT].[EMUndstd], IIf([Phone]=1,"",[EMUndstd]) AS
EMUnstd2, [EmomInvl]+[EMUndstd] AS Engagemt, IIf([Phone]
=1,"",[Engagemt]) AS Engagmt2, 1 AS VisitFrq, [PRENATAL
CLIENT CONTACT].[Longvis], IIf([SettingPR]="Clinic",1,0)
AS Clinic, IIf([SettingPR]="Hospital",1,0) AS Hospital, IIf
([SettingPR]<>"Hospital" And [SettingPR]<>"Clinic" And
[SettingPR]<>"Phone" And [SettingPR]<>"Home",1,0) AS
Other, IIf([SettingPR]="Phone",1,0) AS Phone, IIf
([SettingPR]="Home",1,0) AS Home, [TeensMom]+[BabsFath]+
[Part_noF]+[Friend]+[Relative]+[PRENATAL CLIENT CONTACT]!
[ClassGrp]+[PRENATAL CLIENT CONTACT]![OthDoula]+[PRENATAL
CLIENT CONTACT]![Doctor]+[PRENATAL CLIENT CONTACT]![Nurse]+
[PRENATAL CLIENT CONTACT]![Soc_Work]+[PRENATAL CLIENT
CONTACT]![Doul_Sup]+[PRENATAL CLIENT CONTACT]![OthrPres]
AS TlPresnt, IIf([TeensMom]=1,1,0) AS GmomPres, IIf
([BabsFath]=1 Or [Part_noF]=1,1,0) AS BoyfPres, [PRENATAL
CLIENT CONTACT].[HPreg%], [PRENATAL CLIENT CONTACT].
[Support%], [PRENATAL CLIENT CONTACT].[PrepLD%], [PRENATAL
CLIENT CONTACT].[PrepPCC%], [PRENATAL CLIENT CONTACT].
[Owndvlp%], [HPreg%]+[Support%]+[PrepLD%]+[PrepPCC%]+
[Owndvlp%] AS [Mytotck%], [HPreg%]+[PrepLD%] AS [Health%],
[Dtvisit]-[Enrolldt] AS Opsused, [Dtvisit]-[BBABYBD] AS
BabyAge, [DEMOGRAPHIC].[GROUP], [PRENATAL CLIENT CONTACT].
[EMCnflic], IIf([Longvis]<150,[Longvis],150) AS LongVisB
FROM (DEMOGRAPHIC INNER JOIN ([PRENATAL CLIENT CONTACT]
LEFT JOIN POSTPARTUM ON [PRENATAL CLIENT CONTACT].[ID]=
[POSTPARTUM].[ID]) ON [DEMOGRAPHIC].[ID]=[PRENATAL CLIENT
CONTACT].[ID]) LEFT JOIN qEngagement_Level2 ON [PRENATAL
CLIENT CONTACT].[ID]=[qEngagement_Level2].ID
WHERE ((([PRENATAL CLIENT CONTACT].[ID])<>6 And ([PRENATAL
CLIENT CONTACT].[ID])<>26 And ([PRENATAL CLIENT CONTACT].
[ID])<>90 And ([PRENATAL CLIENT CONTACT].[ID])<>170 And
([PRENATAL CLIENT CONTACT].[ID])<>193 And ([PRENATAL
CLIENT CONTACT].[ID])<900) And (([Dtvisit]-[BBABYBD])>-160
And ([Dtvisit]-[BBABYBD])<=0) And (([DEMOGRAPHIC].[GROUP])
=1))
ORDER BY [PRENATAL CLIENT CONTACT].[ID];

Here is the qEngagement_Level2 query SQL statement:

SELECT [DEMOGRAPHIC].[ID], [BBABYBD]-[DEMOGRAPHIC]!
[Enrolldt] AS Opportun, [DEMOGRAPHIC].[Agenroll],
([DEMOGRAPHIC]![Enrolldt]-[DEMOGRAPHIC]![DOB])/366 AS
Ageatenr, ([POSTPARTUM]![BBABYBD]-[DEMOGRAPHIC]![DOB])/366
AS Ageatdel, [BBABYBD]-[DEMOGRAPHIC]![Enrolldt] AS
EnBirth, [CAREGIVERS].[APCINHSE] AS LivWPCG, [PEABODY].
[StanScore], [DOULA WRAPUP].[Doula], [DEMOGRAPHIC].
[Recruitr], [POSTPARTUM].[B1DOULAPR] AS Doulpres,
[DEMOGRAPHIC].[GROUP], [PRENATAL-WRAP UP].[AW5], [PRENATAL-
WRAP UP].[AW6], [PRENATAL-WRAP UP].[AW7], [PRENATAL-WRAP
UP].[AW8], [PRENATAL-WRAP UP].[AW9], [AW5]+[AW6]+[AW7]+
[AW8] AS Motives, [qCONDUCT PROBLEMS].[ConProbs],
[qCONDUCT PROBLEMS].[StopPoli], [qCONDUCT PROBLEMS].
[JVJail], [qEngagment_level1_means].AvInvolve,
[qEngagment_level1_means].AvUnders,
[qEngagment_level1_means].AvEngage,
[qEngagment_level1_means].AvLonvis, [PRENATAL
SCORES_11_12_03].[ACESTL], [PRENATAL SCORES_11_12_03].
[ASIMPSON], [PRENATAL SCORES_11_12_03].[APEARLIN], DateDiff
("d",[BBABYBD],#2/26/2004#) AS DsAftBir, [PRENATAL
SCORES_11_12_03].[ARUBLE]
FROM (((((((((DEMOGRAPHIC LEFT JOIN CAREGIVERS ON
[DEMOGRAPHIC].[ID]=[CAREGIVERS].[ID]) LEFT JOIN PEABODY ON
[DEMOGRAPHIC].[ID]=[PEABODY].[ID]) LEFT JOIN [qCONDUCT
PROBLEMS] ON [DEMOGRAPHIC].[ID]=[qCONDUCT PROBLEMS].[ID])
LEFT JOIN Dario_Saliva_Query7_31_03 ON [DEMOGRAPHIC].[ID]=
[Dario_Saliva_Query7_31_03].[ID]) LEFT JOIN
qEngagment_level1_means ON [DEMOGRAPHIC].[ID]=
[qEngagment_level1_means].ID) LEFT JOIN [PRENATAL
SCORES_11_12_03] ON [DEMOGRAPHIC].[ID]=[PRENATAL
SCORES_11_12_03].[ID]) LEFT JOIN [DOULA WRAPUP] ON
[DEMOGRAPHIC].[ID]=[DOULA WRAPUP].[ID]) LEFT JOIN
POSTPARTUM ON [DEMOGRAPHIC].[ID]=[POSTPARTUM].[ID]) LEFT
JOIN [PRENATAL-M] ON [DEMOGRAPHIC].[ID]=[PRENATAL-M].[ID])
LEFT JOIN [PRENATAL-WRAP UP] ON [DEMOGRAPHIC].[ID]=
[PRENATAL-WRAP UP].[ID]
WHERE ((([DEMOGRAPHIC].[ID])<>6 And ([DEMOGRAPHIC].[ID])
<>26 And ([DEMOGRAPHIC].[ID])<>90 And ([DEMOGRAPHIC].[ID])
<>170 And ([DEMOGRAPHIC].[ID])<>193 And ([DEMOGRAPHIC].
[ID])<900) And (([DEMOGRAPHIC].[GROUP])=1) And ((DateDiff
("d",[BBABYBD],#2/26/2004#))>91))
ORDER BY [DEMOGRAPHIC].[ID];

The 'circularity' in qEngagement_Level1_pre is probably
due to:

100*([Dtvisit]-[Enrolldt])/[Opportun] AS [%opsused]
(Opportun is a qEngagement_Level2 variable)

&:

LEFT JOIN qEngagement_Level2 ON [PRENATAL CLIENT CONTACT].
[ID]=[qEngagement_Level2].ID

But I can't seem to correct it - it kicks me out!

Thanks!

E
 
I'm sorry I asked to see this ;-) I can't seem to read past the use of "%"
in field and column names. The use of mostly UPPERCASE and spaces also
knocks my train of thought off the tracks. I see where your first query
references your second query but not vice-versa.

--
Duane Hookom
MS Access MVP


eric said:
Hi,
Here is the Engagement_Level1_pre query SQL statement:

SELECT [PRENATAL CLIENT CONTACT].[ID], [PRENATAL CLIENT
CONTACT].[Dtvisit], 100*([Dtvisit]-[Enrolldt])/[Opportun]
AS [%opsused], IIf([Phone]=1,"",[EMomInvl]) AS EMmInvl2,
[PRENATAL CLIENT CONTACT].[EMomInvl], [PRENATAL CLIENT
CONTACT].[EMUndstd], IIf([Phone]=1,"",[EMUndstd]) AS
EMUnstd2, [EmomInvl]+[EMUndstd] AS Engagemt, IIf([Phone]
=1,"",[Engagemt]) AS Engagmt2, 1 AS VisitFrq, [PRENATAL
CLIENT CONTACT].[Longvis], IIf([SettingPR]="Clinic",1,0)
AS Clinic, IIf([SettingPR]="Hospital",1,0) AS Hospital, IIf
([SettingPR]<>"Hospital" And [SettingPR]<>"Clinic" And
[SettingPR]<>"Phone" And [SettingPR]<>"Home",1,0) AS
Other, IIf([SettingPR]="Phone",1,0) AS Phone, IIf
([SettingPR]="Home",1,0) AS Home, [TeensMom]+[BabsFath]+
[Part_noF]+[Friend]+[Relative]+[PRENATAL CLIENT CONTACT]!
[ClassGrp]+[PRENATAL CLIENT CONTACT]![OthDoula]+[PRENATAL
CLIENT CONTACT]![Doctor]+[PRENATAL CLIENT CONTACT]![Nurse]+
[PRENATAL CLIENT CONTACT]![Soc_Work]+[PRENATAL CLIENT
CONTACT]![Doul_Sup]+[PRENATAL CLIENT CONTACT]![OthrPres]
AS TlPresnt, IIf([TeensMom]=1,1,0) AS GmomPres, IIf
([BabsFath]=1 Or [Part_noF]=1,1,0) AS BoyfPres, [PRENATAL
CLIENT CONTACT].[HPreg%], [PRENATAL CLIENT CONTACT].
[Support%], [PRENATAL CLIENT CONTACT].[PrepLD%], [PRENATAL
CLIENT CONTACT].[PrepPCC%], [PRENATAL CLIENT CONTACT].
[Owndvlp%], [HPreg%]+[Support%]+[PrepLD%]+[PrepPCC%]+
[Owndvlp%] AS [Mytotck%], [HPreg%]+[PrepLD%] AS [Health%],
[Dtvisit]-[Enrolldt] AS Opsused, [Dtvisit]-[BBABYBD] AS
BabyAge, [DEMOGRAPHIC].[GROUP], [PRENATAL CLIENT CONTACT].
[EMCnflic], IIf([Longvis]<150,[Longvis],150) AS LongVisB
FROM (DEMOGRAPHIC INNER JOIN ([PRENATAL CLIENT CONTACT]
LEFT JOIN POSTPARTUM ON [PRENATAL CLIENT CONTACT].[ID]=
[POSTPARTUM].[ID]) ON [DEMOGRAPHIC].[ID]=[PRENATAL CLIENT
CONTACT].[ID]) LEFT JOIN qEngagement_Level2 ON [PRENATAL
CLIENT CONTACT].[ID]=[qEngagement_Level2].ID
WHERE ((([PRENATAL CLIENT CONTACT].[ID])<>6 And ([PRENATAL
CLIENT CONTACT].[ID])<>26 And ([PRENATAL CLIENT CONTACT].
[ID])<>90 And ([PRENATAL CLIENT CONTACT].[ID])<>170 And
([PRENATAL CLIENT CONTACT].[ID])<>193 And ([PRENATAL
CLIENT CONTACT].[ID])<900) And (([Dtvisit]-[BBABYBD])>-160
And ([Dtvisit]-[BBABYBD])<=0) And (([DEMOGRAPHIC].[GROUP])
=1))
ORDER BY [PRENATAL CLIENT CONTACT].[ID];

Here is the qEngagement_Level2 query SQL statement:

SELECT [DEMOGRAPHIC].[ID], [BBABYBD]-[DEMOGRAPHIC]!
[Enrolldt] AS Opportun, [DEMOGRAPHIC].[Agenroll],
([DEMOGRAPHIC]![Enrolldt]-[DEMOGRAPHIC]![DOB])/366 AS
Ageatenr, ([POSTPARTUM]![BBABYBD]-[DEMOGRAPHIC]![DOB])/366
AS Ageatdel, [BBABYBD]-[DEMOGRAPHIC]![Enrolldt] AS
EnBirth, [CAREGIVERS].[APCINHSE] AS LivWPCG, [PEABODY].
[StanScore], [DOULA WRAPUP].[Doula], [DEMOGRAPHIC].
[Recruitr], [POSTPARTUM].[B1DOULAPR] AS Doulpres,
[DEMOGRAPHIC].[GROUP], [PRENATAL-WRAP UP].[AW5], [PRENATAL-
WRAP UP].[AW6], [PRENATAL-WRAP UP].[AW7], [PRENATAL-WRAP
UP].[AW8], [PRENATAL-WRAP UP].[AW9], [AW5]+[AW6]+[AW7]+
[AW8] AS Motives, [qCONDUCT PROBLEMS].[ConProbs],
[qCONDUCT PROBLEMS].[StopPoli], [qCONDUCT PROBLEMS].
[JVJail], [qEngagment_level1_means].AvInvolve,
[qEngagment_level1_means].AvUnders,
[qEngagment_level1_means].AvEngage,
[qEngagment_level1_means].AvLonvis, [PRENATAL
SCORES_11_12_03].[ACESTL], [PRENATAL SCORES_11_12_03].
[ASIMPSON], [PRENATAL SCORES_11_12_03].[APEARLIN], DateDiff
("d",[BBABYBD],#2/26/2004#) AS DsAftBir, [PRENATAL
SCORES_11_12_03].[ARUBLE]
FROM (((((((((DEMOGRAPHIC LEFT JOIN CAREGIVERS ON
[DEMOGRAPHIC].[ID]=[CAREGIVERS].[ID]) LEFT JOIN PEABODY ON
[DEMOGRAPHIC].[ID]=[PEABODY].[ID]) LEFT JOIN [qCONDUCT
PROBLEMS] ON [DEMOGRAPHIC].[ID]=[qCONDUCT PROBLEMS].[ID])
LEFT JOIN Dario_Saliva_Query7_31_03 ON [DEMOGRAPHIC].[ID]=
[Dario_Saliva_Query7_31_03].[ID]) LEFT JOIN
qEngagment_level1_means ON [DEMOGRAPHIC].[ID]=
[qEngagment_level1_means].ID) LEFT JOIN [PRENATAL
SCORES_11_12_03] ON [DEMOGRAPHIC].[ID]=[PRENATAL
SCORES_11_12_03].[ID]) LEFT JOIN [DOULA WRAPUP] ON
[DEMOGRAPHIC].[ID]=[DOULA WRAPUP].[ID]) LEFT JOIN
POSTPARTUM ON [DEMOGRAPHIC].[ID]=[POSTPARTUM].[ID]) LEFT
JOIN [PRENATAL-M] ON [DEMOGRAPHIC].[ID]=[PRENATAL-M].[ID])
LEFT JOIN [PRENATAL-WRAP UP] ON [DEMOGRAPHIC].[ID]=
[PRENATAL-WRAP UP].[ID]
WHERE ((([DEMOGRAPHIC].[ID])<>6 And ([DEMOGRAPHIC].[ID])
<>26 And ([DEMOGRAPHIC].[ID])<>90 And ([DEMOGRAPHIC].[ID])
<>170 And ([DEMOGRAPHIC].[ID])<>193 And ([DEMOGRAPHIC].
[ID])<900) And (([DEMOGRAPHIC].[GROUP])=1) And ((DateDiff
("d",[BBABYBD],#2/26/2004#))>91))
ORDER BY [DEMOGRAPHIC].[ID];

The 'circularity' in qEngagement_Level1_pre is probably
due to:

100*([Dtvisit]-[Enrolldt])/[Opportun] AS [%opsused]
(Opportun is a qEngagement_Level2 variable)

&:

LEFT JOIN qEngagement_Level2 ON [PRENATAL CLIENT CONTACT].
[ID]=[qEngagement_Level2].ID

But I can't seem to correct it - it kicks me out!

Thanks!

E


-----Original Message-----
You could probably start by posting the SQL of your queries. We have trouble
seeing them from our vantage point.

--
Duane Hookom
MS Access MVP





.
Click to expand...
Click to expand...
 
Back
Top