Problems with a Circular Reference

T

Tara

Hello,

How can I get a value for [c?] without creating a circular reference? In the
below example, I have just but it down as coming from Table1, but in fact it
should be [Table1].[c]/tb.[Total%] from Table1 LEFT JOIN Table1 AS tb ON
[Table1].[cn]=tb.[cn] AND [Table1].[a]=tb.([a]+1)

When I tried to build it up slowly, it worked, but after I shut it down, I
kept getting a circular reference. Is there a way around this as the
information needed is based on the preceding line?

SELECT [Table1].[a], [Table1].[cn], [Table1]., [Table1].[c] AS [c?],
[Table1]!+(SELECT Sum( [c?]) AS [TotalX] FROM [Table1] AS Dupe WHERE
Dupe.[cn] = [Table1].[cn] And [Dupe].[a] <=[Table1].[a]) AS [TotalX],
[Query1].[TotalY]/[TotalX] AS [Total%], [Query1].[TotalY]
FROM [Table1] INNER JOIN [Query1] ON ([Table1].[cn] = [Query1].[cn]) AND
([Table1].[a] = [Query1].[a])
WITH OWNERACCESS OPTION;

Thank you for any help.
Tara
 
D

Duane Hookom

I try to avoid using a derived value i another calculation in a query. Try
something like:
SELECT [Table1].[a],
[Table1].[cn],
[Table1].,
[Table1].[c] AS [c?],
[Table1]!+(SELECT Sum([c?])
FROM [Table1] AS Dupe
WHERE Dupe.[cn] = [Table1].[cn] And [Dupe].[a] <=[Table1].[a]) AS
[TotalX],
[Query1].[TotalY]/([Table1]!+(SELECT Sum( [c?]) AS [TotalX]
FROM [Table1] AS Dupe
WHERE Dupe.[cn] = [Table1].[cn] And [Dupe].[a] <=[Table1].[a])) AS
[Total%],
[Query1].[TotalY]
FROM [Table1] INNER JOIN [Query1] ON ([Table1].[cn] = [Query1].[cn]) AND
([Table1].[a] = [Query1].[a])
WITH OWNERACCESS OPTION;
 
T

Tara

Hi Duane,

Thank you and I will definitely keep this in mind.
But, I am still having trouble getting a value for [c?] which is what is
causing the circular reference.
(sorry, but I am still very new to left join and realized that I wrote down
the wrong table in my question)

My formula needs to look something like this (i've seperated the parts that
are causing the problems):


SELECT [Table1].[a], [Table1].[cn], [Table1].,

([Table1].[c]/tb.[Total%]) AS [c?],

[Table1].+(SELECT Sum([c?]) FROM [Table1] AS Dupe WHERE Dupe.[cn] =
[Table1].[cn] And [Dupe].[a] <=[Table1].[a]) AS [TotalX],
[Query1].[TotalY]/([Table1]!+(SELECT Sum( [c?]) AS [TotalX] FROM [Table1]
AS Dupe WHERE Dupe.[cn] = [Table1].[cn] And [Dupe].[a] <=[Table1].[a])) AS
[Total%],
[Query1].[TotalY]
FROM ([Table1] INNER JOIN [Query1] ON ([Table1].[cn] = [Query1].[cn]) AND
([Table1].[a] = [Query1].[a]))

LEFT JOIN [ThisQuery] AS tb ON [ThisQuery].[cn]=tb.[cn] AND
[ThisQuery].[a]=tb.([a]+1)

WITH OWNERACCESS OPTION;


Is this making sense? [c?] is based on [c] divided by the percentage from
the previous row.
Can you help me out with this??

Thank you,
Tara

Duane Hookom said:
I try to avoid using a derived value i another calculation in a query. Try
something like:
SELECT [Table1].[a],
[Table1].[cn],
[Table1].,
[Table1].[c] AS [c?],
[Table1]!+(SELECT Sum([c?])
FROM [Table1] AS Dupe
WHERE Dupe.[cn] = [Table1].[cn] And [Dupe].[a] <=[Table1].[a]) AS
[TotalX],
[Query1].[TotalY]/([Table1]!+(SELECT Sum( [c?]) AS [TotalX]
FROM [Table1] AS Dupe
WHERE Dupe.[cn] = [Table1].[cn] And [Dupe].[a] <=[Table1].[a])) AS
[Total%],
[Query1].[TotalY]
FROM [Table1] INNER JOIN [Query1] ON ([Table1].[cn] = [Query1].[cn]) AND
([Table1].[a] = [Query1].[a])
WITH OWNERACCESS OPTION;
--
Duane Hookom
Microsoft Access MVP


Tara said:
Hello,

How can I get a value for [c?] without creating a circular reference? In the
below example, I have just but it down as coming from Table1, but in fact it
should be [Table1].[c]/tb.[Total%] from Table1 LEFT JOIN Table1 AS tb ON
[Table1].[cn]=tb.[cn] AND [Table1].[a]=tb.([a]+1)

When I tried to build it up slowly, it worked, but after I shut it down, I
kept getting a circular reference. Is there a way around this as the
information needed is based on the preceding line?

SELECT [Table1].[a], [Table1].[cn], [Table1]., [Table1].[c] AS [c?],
[Table1]!+(SELECT Sum( [c?]) AS [TotalX] FROM [Table1] AS Dupe WHERE
Dupe.[cn] = [Table1].[cn] And [Dupe].[a] <=[Table1].[a]) AS [TotalX],
[Query1].[TotalY]/[TotalX] AS [Total%], [Query1].[TotalY]
FROM [Table1] INNER JOIN [Query1] ON ([Table1].[cn] = [Query1].[cn]) AND
([Table1].[a] = [Query1].[a])
WITH OWNERACCESS OPTION;

Thank you for any help.
Tara
Click to expand...
Click to expand...
 
D

Duane Hookom

[Total%] is a derived/calculated value in the query. I never use this derived
name in another calculation. I would repeat the entire expression or use one
query that calculates the [Total%] and then another query that uses [Total%]
in another calculation.

--
Duane Hookom
Microsoft Access MVP


Tara said:
Hi Duane,

Thank you and I will definitely keep this in mind.
But, I am still having trouble getting a value for [c?] which is what is
causing the circular reference.
(sorry, but I am still very new to left join and realized that I wrote down
the wrong table in my question)

My formula needs to look something like this (i've seperated the parts that
are causing the problems):


SELECT [Table1].[a], [Table1].[cn], [Table1].,

([Table1].[c]/tb.[Total%]) AS [c?],

[Table1].+(SELECT Sum([c?]) FROM [Table1] AS Dupe WHERE Dupe.[cn] =
[Table1].[cn] And [Dupe].[a] <=[Table1].[a]) AS [TotalX],
[Query1].[TotalY]/([Table1]!+(SELECT Sum( [c?]) AS [TotalX] FROM [Table1]
AS Dupe WHERE Dupe.[cn] = [Table1].[cn] And [Dupe].[a] <=[Table1].[a])) AS
[Total%],
[Query1].[TotalY]
FROM ([Table1] INNER JOIN [Query1] ON ([Table1].[cn] = [Query1].[cn]) AND
([Table1].[a] = [Query1].[a]))

LEFT JOIN [ThisQuery] AS tb ON [ThisQuery].[cn]=tb.[cn] AND
[ThisQuery].[a]=tb.([a]+1)

WITH OWNERACCESS OPTION;


Is this making sense? [c?] is based on [c] divided by the percentage from
the previous row.
Can you help me out with this??

Thank you,
Tara

Duane Hookom said:
I try to avoid using a derived value i another calculation in a query. Try
something like:
SELECT [Table1].[a],
[Table1].[cn],
[Table1].,
[Table1].[c] AS [c?],
[Table1]!+(SELECT Sum([c?])
FROM [Table1] AS Dupe
WHERE Dupe.[cn] = [Table1].[cn] And [Dupe].[a] <=[Table1].[a]) AS
[TotalX],
[Query1].[TotalY]/([Table1]!+(SELECT Sum( [c?]) AS [TotalX]
FROM [Table1] AS Dupe
WHERE Dupe.[cn] = [Table1].[cn] And [Dupe].[a] <=[Table1].[a])) AS
[Total%],
[Query1].[TotalY]
FROM [Table1] INNER JOIN [Query1] ON ([Table1].[cn] = [Query1].[cn]) AND
([Table1].[a] = [Query1].[a])
WITH OWNERACCESS OPTION;
--
Duane Hookom
Microsoft Access MVP


Tara said:
Hello,

How can I get a value for [c?] without creating a circular reference? In the
below example, I have just but it down as coming from Table1, but in fact it
should be [Table1].[c]/tb.[Total%] from Table1 LEFT JOIN Table1 AS tb ON
[Table1].[cn]=tb.[cn] AND [Table1].[a]=tb.([a]+1)

When I tried to build it up slowly, it worked, but after I shut it down, I
kept getting a circular reference. Is there a way around this as the
information needed is based on the preceding line?

SELECT [Table1].[a], [Table1].[cn], [Table1]., [Table1].[c] AS [c?],
[Table1]!+(SELECT Sum( [c?]) AS [TotalX] FROM [Table1] AS Dupe WHERE
Dupe.[cn] = [Table1].[cn] And [Dupe].[a] <=[Table1].[a]) AS [TotalX],
[Query1].[TotalY]/[TotalX] AS [Total%], [Query1].[TotalY]
FROM [Table1] INNER JOIN [Query1] ON ([Table1].[cn] = [Query1].[cn]) AND
([Table1].[a] = [Query1].[a])
WITH OWNERACCESS OPTION;

Thank you for any help.
Tara
Click to expand...
Click to expand...
Click to expand...
 
T

Tara

Hi Duane,

I’m trying to do as you say and (I think) I have taken out all the
derived/calculated fields, but I still don’t know what to do about [c?]. My
problem seems to be moving and follows me with every step I take!

[c?] appears in almost all the fields… how can I change that?
[TotalX] is dependent on how much [c?] is used in that same line
[c?] is dependent on the % of [TotalX] and [TotalY] from the previous line

If I reinsert the calculation for [c?] in [TotalX], then [c?] will still
appear in the new calculation!
I have tried to build this up using 2 queries, but that isn’t working either.

Below is what I have ended up with this time…


SELECT [Table1].[a], [Table1].[cn], [Table1].,

([Table1].[c]/([tb].[TotalY]/([Table1]!+(SELECT Sum([c?]) FROM
[ThisQuery] AS Dupe WHERE Dupe.[cn] = [ThisQuery].[cn] And
[Dupe].[a]<[ThisQuery].[a]))) AS [c?],

[Table1].+(SELECT Sum([c?]) FROM [ThisQuery] AS Dupe WHERE Dupe.[cn] =
[Table1].[cn] And [Dupe].[a]<=[ThisQuery].[a]) AS [TotalX],

[Query1].[TotalY]/([Table1]!+(SELECT Sum([c?]) FROM [ThisQuery]
AS Dupe WHERE Dupe.[cn] = [ThisQuery].[cn] And [Dupe].[a]
<=[ThisQuery].[a])) AS
[Total%],

[Query1].[TotalY]
FROM ([Table1] INNER JOIN [Query1] ON ([Table1].[cn] = [Query1].[cn]) AND
([Table1].[a] = [Query1].[a])) LEFT JOIN [Query1] AS tb ON
[Query1].[cn]=tb.[cn] AND
[Query1].[a]=tb.([a]+1)
WITH OWNERACCESS OPTION;

Tara.

Duane Hookom said:
[Total%] is a derived/calculated value in the query. I never use this derived
name in another calculation. I would repeat the entire expression or use one
query that calculates the [Total%] and then another query that uses [Total%]
in another calculation.

--
Duane Hookom
Microsoft Access MVP


Tara said:
Hi Duane,

Thank you and I will definitely keep this in mind.
But, I am still having trouble getting a value for [c?] which is what is
causing the circular reference.
(sorry, but I am still very new to left join and realized that I wrote down
the wrong table in my question)

My formula needs to look something like this (i've seperated the parts that
are causing the problems):


SELECT [Table1].[a], [Table1].[cn], [Table1].,

([Table1].[c]/tb.[Total%]) AS [c?],

[Table1].+(SELECT Sum([c?]) FROM [Table1] AS Dupe WHERE Dupe.[cn] =
[Table1].[cn] And [Dupe].[a] <=[Table1].[a]) AS [TotalX],
[Query1].[TotalY]/([Table1]!+(SELECT Sum( [c?]) AS [TotalX] FROM [Table1]
AS Dupe WHERE Dupe.[cn] = [Table1].[cn] And [Dupe].[a] <=[Table1].[a])) AS
[Total%],
[Query1].[TotalY]
FROM ([Table1] INNER JOIN [Query1] ON ([Table1].[cn] = [Query1].[cn]) AND
([Table1].[a] = [Query1].[a]))

LEFT JOIN [ThisQuery] AS tb ON [ThisQuery].[cn]=tb.[cn] AND
[ThisQuery].[a]=tb.([a]+1)

WITH OWNERACCESS OPTION;


Is this making sense? [c?] is based on [c] divided by the percentage from
the previous row.
Can you help me out with this??

Thank you,
Tara

Duane Hookom said:
I try to avoid using a derived value i another calculation in a query. Try
something like:
SELECT [Table1].[a],
[Table1].[cn],
[Table1].,
[Table1].[c] AS [c?],
[Table1]!+(SELECT Sum([c?])
FROM [Table1] AS Dupe
WHERE Dupe.[cn] = [Table1].[cn] And [Dupe].[a] <=[Table1].[a]) AS
[TotalX],
[Query1].[TotalY]/([Table1]!+(SELECT Sum( [c?]) AS [TotalX]
FROM [Table1] AS Dupe
WHERE Dupe.[cn] = [Table1].[cn] And [Dupe].[a] <=[Table1].[a])) AS
[Total%],
[Query1].[TotalY]
FROM [Table1] INNER JOIN [Query1] ON ([Table1].[cn] = [Query1].[cn]) AND
([Table1].[a] = [Query1].[a])
WITH OWNERACCESS OPTION;
--
Duane Hookom
Microsoft Access MVP


:

Hello,

How can I get a value for [c?] without creating a circular reference? In the
below example, I have just but it down as coming from Table1, but in fact it
should be [Table1].[c]/tb.[Total%] from Table1 LEFT JOIN Table1 AS tb ON
[Table1].[cn]=tb.[cn] AND [Table1].[a]=tb.([a]+1)

When I tried to build it up slowly, it worked, but after I shut it down, I
kept getting a circular reference. Is there a way around this as the
information needed is based on the preceding line?

SELECT [Table1].[a], [Table1].[cn], [Table1]., [Table1].[c] AS [c?],
[Table1]!+(SELECT Sum( [c?]) AS [TotalX] FROM [Table1] AS Dupe WHERE
Dupe.[cn] = [Table1].[cn] And [Dupe].[a] <=[Table1].[a]) AS [TotalX],
[Query1].[TotalY]/[TotalX] AS [Total%], [Query1].[TotalY]
FROM [Table1] INNER JOIN [Query1] ON ([Table1].[cn] = [Query1].[cn]) AND
([Table1].[a] = [Query1].[a])
WITH OWNERACCESS OPTION;

Thank you for any help.
Tara
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

calculation based on previous year's percentage 6
confused about LEFT JOIN and RIGHT JOIN 3
calculating a total amount 7
Crosstab query with unique count 6
getting fewer records from a union query than in individual table 5
Query is not pulling correct information... Help Please 1
Query on two tables with matching null fields 4
jet correlated subquery fails 1

Top