How to create a calculated field that calls the same calculated field in MS ACCESS

Question

I am creating a Access database query from an excel spreedsheet where the row "Total Capital Calls" is calculated by itself and "Current Capital Call", which is static.

The data from the spreedsheet:

                                 A          B           C           D

1 Current Capital Call 1,000,000 240,000 2,000,000 1,960,000

2 Total Capital Calls 1,000,000 1,240,000 3,240,000 5,200,000

The calculation is as follows:

                                 A          B           C           D

1 Current Capital Call 1,000,000 240,000 2,000,000 1,960,000

2 Total Capital Calls 1,000,000 =+B1+A2 =+C1+B2 =+D1+C2

So the formula is repeated as data moves to the next Total Capital Call field, summing the total to the next 'Total Capital Call' field.

I am trying to create a calculated field in a query using the same calculated field name in the calculation. I need the calculated field "Total Capital Calls" to sum itself with the field [Current Capital Call].

For example:

SELECT LlamadoDeCapital.[Total Capital Committed], 
    LlamadoDeCapital.[Capital Call Request Date], 
    LlamadoDeCapital.[Capital Call Date], 
    LlamadoDeCapital.[Current Capital Call], 
    [Current Capital Call]+[Total Capital Calls] 
AS [Total Capital Calls]
FROM LlamadoDeCapital;

After running the query, I get this message:

Circular reference caused by alias 'Total Capital Calls' in query definition's SELECT list

Is it possible to call the calculated field in it's own expression??

or

Is there better solution to summing the calculated field with itself and the other field?

Is your table structured like it's shown? you have multiple fields accross instead of records down? if the latter.. You're basically looking for a running sum? Is this for a report or for a form? Take a look at this link: support.microsoft.com/en-us/kb/290136 — Gene
Thank you both of your recommendations. I may have to use both the DLookup and DSum functions together somehow, but I am not quite sure how. At the moment, I am try to get the DSum function working only with the "Current Capital Calls" field to prove that works, however, I am getting a syntax error. — LetsDoThis
Here is my Select Expression: SELECT LlamadoDeCapital.ID, LlamadoDeCapital.[TotalCapitalCommitted], LlamadoDeCapital.[CapitalCallRequestDate], LlamadoDeCapital.[CapitalCallDate], LlamadoDeCapital.[CurrentCapitalCall] DSum("CurrentCapitalCall","LlamadoDeCapital","CapitalCallDate <=" & [CapitalCallDate]) AS TotalCapitalCalls FROM LlamadoDeCapital; — LetsDoThis
Here is the error I'm getting: Syntax error (missing operator) in query expression 'LlamadoDeCapital.[CurrentCapitalCall] DSum("CurrentCapitalCall","LlamadoDeCapital","CapitalCallDate" <=" & [CapitalCallDate])'. — LetsDoThis

LetsDoThis LetsDoThis · Accepted Answer · 2015-09-02T20:16:21

Ok, so adding the clng function was the resolution:

DSum("CurrentCapitalCall","LlamadoDeCapital","CapitalCallDate <=" & clng([CapitalCallDate])) AS TotalCapitalCalls

Running Sum total with both fields.

How to create a calculated field that calls the same calculated field in MS ACCESS

1 Answers