List box based on SQL Query with calculated field

[Movian]

I have a list box that is currently displaying services, with cost, payments and adjustments.

I have a field that shows a current total owed. However i have been asked to alter the list box to be able to show the total for each line item. I know in access 2010 you can setup a field in a table that is a calculation (like a computed field in SQL) However we are currently using access 2010. Is there a way i can achieve this in 2007 or should i just push the use of 2010 and setup the field in there ?

Thanks in advance

[OBP]

Movian, sorry I am not familair with either Access 2007 or 2010, but didn't you answer your own question.
ie. use an SQL statement complete with computed field for the List box instaed of a table?
That would be totally independant of Access version.

[Movian]

So how would i create a computed Field in a SQL statement that is using access tables is the question then. As the computed field i was referring to as in actual physical field in the table that is itself a calculation.

[OBP]

When you say "actual physical field in the table that is itself a calculation" do you mean that the Table is Temporary and Created on the run? If so you can run a query after updating the table.
What sort of calculation do yuo require?

[Movian]

ok let me give you an example, i have a table called ledger
in the ledger table currently are the fields.
Service ID, PatiendID, CPU (cost per unit), Units and some other fields that arn't relevent to this question

There is a second Table called Payments.
PaymentID, ServiceID,Patient ID, Payment Amount and again some other fields that are not relevent.

At the moment i have a SQL statement in the list box that shows all the services (and the total payments for those services).

I have been asked to have an extra column in the list box for Balance.

Balance is not a field in either table and would be calculated with Service Total amount - Payments.

Is there a way to do this with a SQL statement?


In access 2010, i would add a new field to the service table called balance and instead of setting its type to text or currency. It would be set to the new calculation type. Which would allow me to just add the filed name to the select portion of the query. However this option does not exist in access 2007. So i was asking iif there is another way to do it :)

[OBP]

Assuming that the List box is currently coming from a Query based on both tables you can have that calculation in the query as a new column.
If however you need to get the balance from "Multiple" Costs and payments you might need to use Grouping "pre" queries to get the total for the calculation and join that to the Current query.

The other alternative is to have the field as you described but update the current records with an Update Query and then use VBA in the updating forms to "maintain" the balance.
I have that kind of VBA on a couple of databases.

[Movian]

Assuming that the List box is currently coming from a Query based on both tables you can have that calculation in the query as a new column.


Could you give an example of this, as from your description i think thats what i am trying to get at. :)

[OBP]

Try this.

[Imdabaum]

Another option is to create a function that does addition/subtraction and set up a column Balance: GetBalance([Service Total amount],[Payments])

It may not be optimum because it's not native SQL, but it's another option.