0

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?

LetsDoThis
  • 29
  • 3
  • Read [here](https://support.microsoft.com/en-us/kb/210504) – cha Sep 02 '15 at 04:29
  • 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: https://support.microsoft.com/en-us/kb/290136 – Gene Sep 02 '15 at 10:51
  • 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 Sep 02 '15 at 17:58
  • 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 Sep 02 '15 at 18:01
  • Here is the error I'm getting: Syntax error (missing operator) in query expression 'LlamadoDeCapital.[CurrentCapitalCall] DSum("CurrentCapitalCall","LlamadoDeCapital","CapitalCallDate" <=" & [CapitalCallDate])'. – LetsDoThis Sep 02 '15 at 18:01
  • Ok, was able to resolve the syntax error. I replaced the commas with semi colons. But now when the query is run, nothing is summed in the "TotalCapitalCalls" column. – LetsDoThis Sep 02 '15 at 18:35
  • My updated expression. Only semi-colons were changed: SELECT LlamadoDeCapital.ID, LlamadoDeCapital.TotalCapitalCommitted, LlamadoDeCapital.CapitalCallRequestDate, LlamadoDeCapital.CapitalCallDate, LlamadoDeCapital.CurrentCapitalCall, DSum("CurrentCapitalCall","LlamadoDeCapital","CapitalCallDate <=" & [CapitalCallDate]) AS TotalCapitalCalls FROM LlamadoDeCapital; – LetsDoThis Sep 02 '15 at 18:36

1 Answers1

0

Ok, so adding the clng function was the resolution:

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

Running Sum total with both fields.

LetsDoThis
  • 29
  • 3