Balance of two columns?

[lszuma]

I have a table of three columns (although there are other non-currency columns in this table) but I would like the balance column to show the balance of buget column less total expenditures:

Budget Total Expenditures Balance


Thanks.

 

[lszuma]

A bit more description showing columns (from a single table):

Grant No. End Date Sponsor Budget Total Expenditures Balance

The Budget column will always be a static dollar amount. The "Total" Row for each column is currenly set to "Group By".

I would like the balance column to calculate the Budget less the total expenditrues.

 

[mresann]

How about...

Balance: [Budget] - [Total Expenditures]

Make sure this field occurs AFTER the Budget and Total Expenitures fields in your query.

 

[lszuma]

Did that.

Unfortunately, it only returns those that have a zero balance.

Obviously too simple of a solution!

Any other suggestions to return all rows with balances?

 

[mresann]

Actually, you shouldn't have a field called 'Balance' in the table, as it is a calculated field between two other columns. When I first read your problem, I thought you meant query.

If you want to have a Balance field in the table, then you have to use an Update query to conduct the calculation and enter the data.

However, I would look more into removing the Balance field from the table altogether, and then basing your form or report on a query which contains the calcualtion for Balance.

 

[lszuma]

I thought I could perform this calculation at the query level which is why I started this tread in queries.

Although I understand your logic of a separate "Balance" table, it perplexes me as to why the calculation will return rows that that equal a zero balance but not those that have a remaining balance. A peculiar querk in Access?

I would really like to avoid another table as this table is part of a complex 5-table join.

What is the disadvantage of an update query? The report I will have to generate is quarterly. Would I have to perform the query each time? If so, then a separate, permanent calculation table would probably be the better option. But, oh the painful thought of adding yet another table.

Thank you for your input and advice. Lisa

 

[lszuma]

Awesome.......!

An additoinal table is not necessary.

At the report design view all I needed to do is type the following expression in the Control Source data properties of the Balance field:

=[budget]-[total expenditures]

Wallah..............!

 

[mresann]

Glad you got your problem fixed.

I apologize if I didnt make myself clearer earlier. Sometimes I have to read two or three times to get things right. I see that in your original post you did indeed leave out "Balance", therefore there is no need of an update query.

I still fail to see, though, how the query expresion I presented earlier failed. Unless there are complex 5-table join issues that come into play, the calculation query is actually pretty basic.

Anyway, good luck with your project!

 

[Pat Hartman]

Iszuma, the query didn't work because you put the calculation in the criteria line for the balance field. When [budget]-[total expenditures] equalled zero, the record was selected. The calculation needed to go in the field cell. Also, be sure to delete the Balance field from the table definition since it is not needed.

 

[lszuma]

Thanks! I'll ditch the uncessary use of storing dead space.

However, I used that same "Balance" field to write the calc; it was already queried and brought to the report design in that fashion. It was only after that that I used the field to write the statement.

So long as the report doesn't freak out if I delete the Balance field from the table, I'm kosher with it........so long as the calc is stored somewhere.

p.s. That student thing has gone away, at least for now. I queried the student name and degree info from the unjoined table and used it as a combined, one-column look up query in the other student table required for project assignments. Since students are a sub-subform on the projects subform, on the table form, referential integrity applies nicely here. I have managed to avoid those nasty ambiguous joins, junction tables, and bungy-chord relationships.

Although I am not quite sure of the effect of updating that query to capture new enrollments and eliminate those that have graduated. I have never had the need to do an update query, and perhaps it will not do what I am hoping and assuming it will do, but I will certainly find out! At least I have eliminated the repitious entry of student names.

Lisa

 

[Pat Hartman]

You seem to be referring to some othe post that I answered. I can't comment because I don't know what you are talking about. I help a lot of people every day. You have only 1 or 2 problems to remember. If you really have a question, post it in the correct thread.

BTW, update queries update values that are stored in tables. Recordsets created by queries are not persistant. They are distroyed when the query is closed. Only the data actually stored in the underlying tables remains.