Add calculated field

[dov]

i have a query with columns:comapny, account, year, month, balance.
The "balance" field shows the accumulated balance for the accounts for the year/month. I want to add an other column in which i will have the balance of the month (not the accumulated balance) for some of the accounts. I am looking for a way to add another field that will calculate that balance - the difference between the balance of the monthe and the previous month (for the same company,account and year), only for the accounts that starts with the digit 3 .

Thanks

 

[Russell Hauf]

You can actually do this in a query, although it's rather complicated.

Ok, so I assume that your data looks something like this (nevermind that my "account"s are numeric - that part is easy.

Company	Account	Month	Year	Bal
ABC	1	1	2002	9020.09
ABC	1	2	2002	6347.08
ABC	1	3	2002	10378.05
ABC	1	4	2002	6271.28
ABC	1	5	2002	6888.93
ABC	1	6	2002	6342.96
ABC	1	7	2002	5425.61

Make sure that your table is sorted by Company, then by Account, then by year, then by month. If it's not, you can open the table, click on Records-Filter-Advanced Filter/Sort, then choose those 4 fields, sort them ascending, and click on "Apply Filter" (it looks like a funnel). Close the table, and save it when it asks if you want to save (click yes).

Ok, now open a new query, add your table, and select Company, Account, Month, Year, and Balance. Put the name of the account that you want (if you want all that start with "c", then put

Like "C*"

in the criteria box).

Ok, now right-click on the table that is in your query - on the actual "box" that says the name of your table and has the fields listed. Select "Properties", and under "Alias", type "X".

Ok, now in the column to the right of Balance, type the following (my original table is called "C" - so replace all occurances of this with your table name) - in the "Field" box:

CurrBal: [Bal]-(SELECT Bal FROM C WHERE [Company]=X.[Company] AND [Account]=X.[Account] AND ([Month]=Month(DateAdd("m",-1,DateSerial(X.[Year],X.[Month],1)))) AND ([Year]=Year(DateAdd("m",-1,DateSerial(X.[Year],X.[Month],1)))))

If you have data from before January, then January's CurrBal will be negative - you can ignore it or put in an IIF statement which repeats the query above (not recommended).

I hope this helps, let me know if you need further clarification. I will be leaving for the weekend in about 90 minutes though, so if I don't see anything from you before then, you'll have to wait until Monday.

-Russell
This message was edited by Russell Hauf on 2002-11-27 13:31

 

[Corticus]

Nice solution Russel!

Better and easier than mine.

Have a great weekend!