Circular Reference

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi! I am trying to get this quiry to work right. What I want is to add the
two queries together. So in the query I am having problems with I select the
two queries (no table) to add them together. I match Ret_Civ_Ser If there is
no matching values then I want it to not add the two queries together but to
just bring over that one value.

Query work fine
qryRetCivSerAP2
Ret_Civ_Ser CountOfLAST_NAME
N 79
Y 4

And Query works fine
qryRetCivSerCC2
Ret_Civ_Ser CountOfLAST_NAME
N 38

Note: There is no Y answer for Query==>qryRetCivSerCC2

So I have to check for it in the next query that addes them together

qryRetCivSerAP2CC2
SELECT DISTINCTROW qryRetCivSerAP2.Ret_Civ_Ser,
IIf(([qryRetCivSerAP2.Ret_Civ_Ser]=[qryRetCivSerCC2.Ret_Civ_Ser]),Sum([qryRetCivSerAP2.CountOfLast_Name]+[qryRetCivSerCC2.CountOfLast_Name]),IIf(IsNull([qryRetCivSerAP2.Ret_Civ_Ser]),([qryRetCivSerCC2.Ret_Civ_Ser]=[qryRetCivSerAP2.Ret_Civ_Ser])
And
([qryRetCivSerCC2.CountOfLAST_NAME]=[Expr1]),IIf(IsNull([qryRetCivSerCC2.Ret_Civ_Ser]),([qryRetCivSerAP2.Ret_Civ_Ser]=[qryRetCivSerCC2.Ret_Civ_Ser])
And ([qryRetCivSerAP2.CountOfLast_Name]=[Expr1])))) AS Expr1
FROM qryRetCivSerAP2, qryRetCivSerCC2
GROUP BY qryRetCivSerAP2.Ret_Civ_Ser;

I get a Circular Error 3103 when I try this because I am trying to take the
value and add it back to the Expr1 value

Maybe I am putting to much into this query? I don't know

Please help and Thank You very much!!!
 
Try changing --
And ([qryRetCivSerAP2.CountOfLast_Name]=[Expr1])))) AS Expr1
to read --
And ([qryRetCivSerAP2.CountOfLast_Name]=[Expr1])))) AS ExprX
 
Well I tried changing to what you indicated and I got another error:
You tried to execute a query that does not include the specified expression
<name> as part of an aggregate function. (Error 3122)

SELECT DISTINCTROW qryRetCivSerAP2.Ret_Civ_Ser,
Sum([qryRetCivSerAP2.CountOfLast_Name]+[qryRetCivSerCC2.CountOfLast_Name]) AS
Expr1,
IIf(IsNull([qryRetCivSerAP2.Ret_Civ_Ser]),([qryRetCivSerCC2.Ret_Civ_Ser]=[qryRetCivSerAP2.Ret_Civ_Ser])
And
([qryRetCivSerCC2.CountOfLAST_NAME]=[Expr1]),IIf(IsNull([qryRetCivSerCC2.Ret_Civ_Ser]),([qryRetCivSerAP2.Ret_Civ_Ser]=[qryRetCivSerCC2.Ret_Civ_Ser])
And ([qryRetCivSerAP2.CountOfLast_Name]=[Expr1]))) AS ExprX
FROM qryRetCivSerAP2, qryRetCivSerCC2
GROUP BY qryRetCivSerAP2.Ret_Civ_Ser;

Please help Thank You!!!

KARL DEWEY said:
Try changing --
And ([qryRetCivSerAP2.CountOfLast_Name]=[Expr1])))) AS Expr1
to read --
And ([qryRetCivSerAP2.CountOfLast_Name]=[Expr1])))) AS ExprX


Amour said:
Hi! I am trying to get this quiry to work right. What I want is to add the
two queries together. So in the query I am having problems with I select the
two queries (no table) to add them together. I match Ret_Civ_Ser If there is
no matching values then I want it to not add the two queries together but to
just bring over that one value.

Query work fine
qryRetCivSerAP2
Ret_Civ_Ser CountOfLAST_NAME
N 79
Y 4

And Query works fine
qryRetCivSerCC2
Ret_Civ_Ser CountOfLAST_NAME
N 38

Note: There is no Y answer for Query==>qryRetCivSerCC2

So I have to check for it in the next query that addes them together

qryRetCivSerAP2CC2
SELECT DISTINCTROW qryRetCivSerAP2.Ret_Civ_Ser,
IIf(([qryRetCivSerAP2.Ret_Civ_Ser]=[qryRetCivSerCC2.Ret_Civ_Ser]),Sum([qryRetCivSerAP2.CountOfLast_Name]+[qryRetCivSerCC2.CountOfLast_Name]),IIf(IsNull([qryRetCivSerAP2.Ret_Civ_Ser]),([qryRetCivSerCC2.Ret_Civ_Ser]=[qryRetCivSerAP2.Ret_Civ_Ser])
And
([qryRetCivSerCC2.CountOfLAST_NAME]=[Expr1]),IIf(IsNull([qryRetCivSerCC2.Ret_Civ_Ser]),([qryRetCivSerAP2.Ret_Civ_Ser]=[qryRetCivSerCC2.Ret_Civ_Ser])
And ([qryRetCivSerAP2.CountOfLast_Name]=[Expr1])))) AS Expr1
FROM qryRetCivSerAP2, qryRetCivSerCC2
GROUP BY qryRetCivSerAP2.Ret_Civ_Ser;

I get a Circular Error 3103 when I try this because I am trying to take the
value and add it back to the Expr1 value

Maybe I am putting to much into this query? I don't know

Please help and Thank You very much!!!
Click to expand...
Click to expand...
 
Sorry, I did not stop to analyze your SQL.

I think this will do what you want --
SELECT DISTINCTROW qryRetCivSerAP2.Ret_Civ_Ser,
NZ([qryRetCivSerCC2].[CountOfLAST_NAME])+NZ([qryRetCivSerAP2].[CountOfLAST_NAME]) AS Totals
FROM qryRetCivSerAP2 LEFT JOIN qryRetCivSerCC2 ON
qryRetCivSerAP2.Ret_Civ_Ser = qryRetCivSerCC2.Ret_Civ_Ser;


Amour said:
Well I tried changing to what you indicated and I got another error:
You tried to execute a query that does not include the specified expression
<name> as part of an aggregate function. (Error 3122)

SELECT DISTINCTROW qryRetCivSerAP2.Ret_Civ_Ser,
Sum([qryRetCivSerAP2.CountOfLast_Name]+[qryRetCivSerCC2.CountOfLast_Name]) AS
Expr1,
IIf(IsNull([qryRetCivSerAP2.Ret_Civ_Ser]),([qryRetCivSerCC2.Ret_Civ_Ser]=[qryRetCivSerAP2.Ret_Civ_Ser])
And
([qryRetCivSerCC2.CountOfLAST_NAME]=[Expr1]),IIf(IsNull([qryRetCivSerCC2.Ret_Civ_Ser]),([qryRetCivSerAP2.Ret_Civ_Ser]=[qryRetCivSerCC2.Ret_Civ_Ser])
And ([qryRetCivSerAP2.CountOfLast_Name]=[Expr1]))) AS ExprX
FROM qryRetCivSerAP2, qryRetCivSerCC2
GROUP BY qryRetCivSerAP2.Ret_Civ_Ser;

Please help Thank You!!!

KARL DEWEY said:
Try changing --
And ([qryRetCivSerAP2.CountOfLast_Name]=[Expr1])))) AS Expr1
to read --
And ([qryRetCivSerAP2.CountOfLast_Name]=[Expr1])))) AS ExprX


Amour said:
Hi! I am trying to get this quiry to work right. What I want is to add the
two queries together. So in the query I am having problems with I select the
two queries (no table) to add them together. I match Ret_Civ_Ser If there is
no matching values then I want it to not add the two queries together but to
just bring over that one value.

Query work fine
qryRetCivSerAP2
Ret_Civ_Ser CountOfLAST_NAME
N 79
Y 4

And Query works fine
qryRetCivSerCC2
Ret_Civ_Ser CountOfLAST_NAME
N 38

Note: There is no Y answer for Query==>qryRetCivSerCC2

So I have to check for it in the next query that addes them together

qryRetCivSerAP2CC2
SELECT DISTINCTROW qryRetCivSerAP2.Ret_Civ_Ser,
IIf(([qryRetCivSerAP2.Ret_Civ_Ser]=[qryRetCivSerCC2.Ret_Civ_Ser]),Sum([qryRetCivSerAP2.CountOfLast_Name]+[qryRetCivSerCC2.CountOfLast_Name]),IIf(IsNull([qryRetCivSerAP2.Ret_Civ_Ser]),([qryRetCivSerCC2.Ret_Civ_Ser]=[qryRetCivSerAP2.Ret_Civ_Ser])
And
([qryRetCivSerCC2.CountOfLAST_NAME]=[Expr1]),IIf(IsNull([qryRetCivSerCC2.Ret_Civ_Ser]),([qryRetCivSerAP2.Ret_Civ_Ser]=[qryRetCivSerCC2.Ret_Civ_Ser])
And ([qryRetCivSerAP2.CountOfLast_Name]=[Expr1])))) AS Expr1
FROM qryRetCivSerAP2, qryRetCivSerCC2
GROUP BY qryRetCivSerAP2.Ret_Civ_Ser;

I get a Circular Error 3103 when I try this because I am trying to take the
value and add it back to the Expr1 value

Maybe I am putting to much into this query? I don't know

Please help and Thank You very much!!!
Click to expand...
Click to expand...
Click to expand...
 
Thank You that works great!

KARL DEWEY said:
Sorry, I did not stop to analyze your SQL.

I think this will do what you want --
SELECT DISTINCTROW qryRetCivSerAP2.Ret_Civ_Ser,
NZ([qryRetCivSerCC2].[CountOfLAST_NAME])+NZ([qryRetCivSerAP2].[CountOfLAST_NAME]) AS Totals
FROM qryRetCivSerAP2 LEFT JOIN qryRetCivSerCC2 ON
qryRetCivSerAP2.Ret_Civ_Ser = qryRetCivSerCC2.Ret_Civ_Ser;


Amour said:
Well I tried changing to what you indicated and I got another error:
You tried to execute a query that does not include the specified expression
<name> as part of an aggregate function. (Error 3122)

SELECT DISTINCTROW qryRetCivSerAP2.Ret_Civ_Ser,
Sum([qryRetCivSerAP2.CountOfLast_Name]+[qryRetCivSerCC2.CountOfLast_Name]) AS
Expr1,
IIf(IsNull([qryRetCivSerAP2.Ret_Civ_Ser]),([qryRetCivSerCC2.Ret_Civ_Ser]=[qryRetCivSerAP2.Ret_Civ_Ser])
And
([qryRetCivSerCC2.CountOfLAST_NAME]=[Expr1]),IIf(IsNull([qryRetCivSerCC2.Ret_Civ_Ser]),([qryRetCivSerAP2.Ret_Civ_Ser]=[qryRetCivSerCC2.Ret_Civ_Ser])
And ([qryRetCivSerAP2.CountOfLast_Name]=[Expr1]))) AS ExprX
FROM qryRetCivSerAP2, qryRetCivSerCC2
GROUP BY qryRetCivSerAP2.Ret_Civ_Ser;

Please help Thank You!!!

KARL DEWEY said:
Try changing --
And ([qryRetCivSerAP2.CountOfLast_Name]=[Expr1])))) AS Expr1
to read --
And ([qryRetCivSerAP2.CountOfLast_Name]=[Expr1])))) AS ExprX


:

Hi! I am trying to get this quiry to work right. What I want is to add the
two queries together. So in the query I am having problems with I select the
two queries (no table) to add them together. I match Ret_Civ_Ser If there is
no matching values then I want it to not add the two queries together but to
just bring over that one value.

Query work fine
qryRetCivSerAP2
Ret_Civ_Ser CountOfLAST_NAME
N 79
Y 4

And Query works fine
qryRetCivSerCC2
Ret_Civ_Ser CountOfLAST_NAME
N 38

Note: There is no Y answer for Query==>qryRetCivSerCC2

So I have to check for it in the next query that addes them together

qryRetCivSerAP2CC2
SELECT DISTINCTROW qryRetCivSerAP2.Ret_Civ_Ser,
IIf(([qryRetCivSerAP2.Ret_Civ_Ser]=[qryRetCivSerCC2.Ret_Civ_Ser]),Sum([qryRetCivSerAP2.CountOfLast_Name]+[qryRetCivSerCC2.CountOfLast_Name]),IIf(IsNull([qryRetCivSerAP2.Ret_Civ_Ser]),([qryRetCivSerCC2.Ret_Civ_Ser]=[qryRetCivSerAP2.Ret_Civ_Ser])
And
([qryRetCivSerCC2.CountOfLAST_NAME]=[Expr1]),IIf(IsNull([qryRetCivSerCC2.Ret_Civ_Ser]),([qryRetCivSerAP2.Ret_Civ_Ser]=[qryRetCivSerCC2.Ret_Civ_Ser])
And ([qryRetCivSerAP2.CountOfLast_Name]=[Expr1])))) AS Expr1
FROM qryRetCivSerAP2, qryRetCivSerCC2
GROUP BY qryRetCivSerAP2.Ret_Civ_Ser;

I get a Circular Error 3103 when I try this because I am trying to take the
value and add it back to the Expr1 value

Maybe I am putting to much into this query? I don't know

Please help and Thank You very much!!!
Click to expand...
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
Iif/IsNull Question 6
select query row empty if value zero/null 1
joining two strings before query 5
ACCESS - how do I apply a IIF = statement without parameter value prompt. The two column comparison is count 1
Iff statement - current or expired contracts 3
Query asks for parameter with no connection to the query. 1
making two IIf's work together 1

Back
Top