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

 

[Anne Troy]

I don't get it.

"Accumulated" means running balance? Or totals? Are you already grouping in a totals query?

 

[dov]

accumulated mean running totals. for example, for acccount "sales" the balance for the month 10 mean sales of the company for January to October and for month 9 - sales of the company from January to September. I need do have in a new column the sales of the October alone - balance of 10 less balance of 9.
i am not grouping the totals. i recieve it like this from the accounting systems of the companies.
Thanks

 

[Anne Troy]

"i recieve it like this from the accounting systems of the companies."

You receive the QUERY like this???

What does your table look like? In order to give you a total for each month, we need to have individual record values, but you state you only have a running balance.

 

[dov]

i am sorry
i recieve like this the table (my table is imported from an external system and it includes the fields as i described for many companies in our group)
i am using a query in order to join fields from other tables which are not relevant to my question.
in this query i want to calculate the monthly balances in a new field.

 

[Anne Troy]

Do you have individual record values too? Or just running sums?

I need to know what fields we can use. Do you understand? You tell me only that you have a *balance* column. Do you ALSO have a column that contains a single VALUE for that record?

If so:

=iif(left([myfield],1)="3",sum[myfield]) should work, I think.

 

[WillR]

If I understand you correctly, you are importing the general ledger balances for a number of charts of accounts at the end of each month (??) i.e., a bit like a closing trial balance.

If you have only this, you won't be able to calculate the month's movement in balances as there is no reference to what the balance was in previous months. If you have previous months saved in other tables, you could create a query that calculates the balance of account x at period 10 minus the same at period 9.

If all you have is the current period end balances however, you will need to import more data in order to get what you want.

HTH

 

[dov]

Yes, i am importing closing trial balances.
i have the data for the previous month also in the same table.
The question is how can i create a query that calculates the balance of account x at period 10 minus the same at period 9.
i used to do this calculation in Excel (after sorting the table by company,account, year, month) by using function with IF's and calculating difference between a current cel to the cell above it if few conditions are met (same company, same account,same year)
i need to do the same in access since the database is too large for excel now

Thanks

 

[kredmond]

cant you just add a field to your query, and in the field type in
Month: [Fieldwithcurrentbalance] - [Fieldwithlastmonthbalance]
this should then give you the movement.

Maybe way of base here but worth a go

 

[Corticus]

Hi,

I've had to do something like this with some financial statements. The trick is converting data in rows(by date) to data in columns(by date).

In other words, you might have

month/balance
Aug/100
Sep/200
Oct/300

You need it like:
Aug/Sep/Oct
100/200/300

Which will require you to run a create table query to rearrange the data in this format. This table will have to be created on the fly, as it will change everytime a new month of data is added. I coded this in using SQL directly, but that will be a bit much to explain here.

Once you have data in this form, running calculations such as determining year-to-date balances can be handled through query caclulations.

If this makes sense to you, try it. If your totally confused, I could send you a db utilizing this technique.

HTH,