Problems correcting Circular Reference!

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
 
D

Duane Hookom

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

eric

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
 
D

Duane Hookom

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

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

circular reference 3
How to slove: Circular Reference Warning 1
Circular Reference 1
Problems with a Circular Reference 4
Circular reference error in query kicks me out of Access 1
CIRCULAR REFERENCE CAUSED 5
calculation based on previous year's percentage 6
Circular reference, Indirect, and Sum 3

Top