ACCESS 2003 - problem with calculated field

[wilow]

Hello everyone,
I have a problem with create a calculated field in Access query. I have a table:

||    Date   || Some_value || My_calculated_field || Formula for "My_calculated_field"
   2012-01-01     3000                  3000                      3000/1
   2012-01-02        0                  1500                      (3000+0)/2
   2012-01-03        0                  1000                      (3000+0+0)/3
   2012-01-04        0                   750                      (3000+0+0+0)/4
   2012-01-05     1000                   800                      (3000+0+0+0+1000)/5

I tried to calculate it in such a way that the query I have added a calculated field with the following formula:

My_calculated_field: Sum([Some_value])/Day([Date])

/*Formula Day ([Date]) returns me the value of exactly what I'm talking about, that is noumber day in the month). However, as a result of a query is counted only the value for the first row, and for the rest: 0.*/
Is my problem is resolvable at all?

Thank you in advance for your help,
Michael

 

[plog]

First, 'Date' is a bad name for a field because it is a reserved word. You should instead prefix it with something related to the data in the table--ContactDate, BalanceDate, AppointmentDate, etc.

Second, you essentially need to create a running sum for each record in your query, then divide that value by the Day([Date])


My_calculated_field: DSum("[Some_value]", "YourTableNameHere", "[Date]<=#" & [Date] & "#")/Day([Date])

Does your table contain more than one month's worth of data and if so, do you want My_calculated_field to have the sum of just data for the month the record is in?

 

[wilow]

I chenged name of column("Date") to "Date_test".

Does your table contain more than one month's worth of data and if so, do you want My_calculated_field to have the sum of just data for the month the record is in?

Yes, my table contain more than one month, but I need only the data for each month. If I apply your solution on the table as shown by me everything is working properly. But I made one mistake in the translation my problem. Everything is as written, except that the value of "some_value" is also calculated field. And then it does not work. Do you have any idea how to solve it ??

 

[plog]

I don't understand. You say everything is working fine, but you end with "then it does not work". Is it working or not? Is field "some_value" a calculated field in a table or a query? What is it calculated on?

 

[wilow]

Sorry for my mistake. I think the attached file will explain the problem, which I wrote.

 

[plog]

The simple answer is to remove the calculated value I gave you from the query you have and then use the query as the data source for another query in which you can then put my calculated value.

 

[wilow]

Now it's OK Thank you very much for your help
Michael