How to insert computed value in listbox on a per item basis

[jpstokes]

I have a form that I'm trying to populate. This a summary form. I already have a details form which calculates and shows all the necessary data for a particular item. My problem is that in the summary form I want to show a particular value that I'm already showing in the details form. This value is computed by performing queries and then calculations using the query results. Below is an example of one of the queries.

'Sum all planned amounts of tasks associated with funding doc
  Dim objRS As ADODB.Recordset
  Dim colName As String
  colName = "PLANNED_AMT"
  Set objRS = New ADODB.Recordset
  objRS.Open "SELECT Sum(" & colName & ") FROM TASKS WHERE (((TASKS.[FUNDING_DOC_ID_FK])=" & _
  fPrevious.lbFundingDocs & ") AND ((TASKS.[" & colName & "]) Is Not Null));", CurrentProject.Connection
  If Not objRS.EOF Then
    bac = objRS.Fields(0).Value
  End If
  objRS.Close
  Set objRS = Nothing

Basically the formula that I'm using is:

spi = ev / pv
cpi = ev / ac

avgIndex = (spi + cpi) / 2

ev, pv and ac are derived from summing up their respective columns from another table.

So what I'm trying to do is show a avgIndex for each item on my summary form but I don't know how to perform all the calculations I need and insert the result into a query so that I can have the avgIndex show up in a listbox. The code I'm currently using to generate this listbox is below:

Me.lbFundingDocs.RowSource = "SELECT ID, FUNDING_DOC_NO AS [Funding Doc No], FUNDING_TYPE AS [Funding Type], EXP_DATE AS [Expiration Date] FROM FUNDING_DOC;"

What I want is:

Me.lbFundingDocs.RowSource = "SELECT ID, FUNDING_DOC_NO AS [Funding Doc No], FUNDING_TYPE AS [Funding Type], EXP_DATE AS [Expiration Date], AVGINDEX FROM FUNDING_DOC;"

P.S. - the Details doesn't exists until I double click on an item in the summary page. Once i do that it shows the detail page for that item. So I'm thinking I won't be able to reference the value that I need from my details page since it needs to show up before I even click to open the details page.

Update
Just to further explain my issue. I want my listbox on the summary page to look something like this:

Doc No. #1 | BAC | AC | PV | EV | AVG Index | Color_block
Doc No. #2 | BAC | AC | PV | EV | AVG Index | Color_block
Doc No. #3 | BAC | AC | PV | EV | AVG Index | Color_block

Each of the values above is a computed value. So is this possible to achieve? Please tell me it is and if so how do I achieve this?

P.S. the color_block is where I want to show a color based on the value of AVGIndex for that particular item.

[patjones]

You've given a lot of information in your post, but it's hard to tell what is relevant to your problem.

I would put the calculation right in the SQL statement that populates the list box. Sometimes it's easy to forget that you can do math in the SELECT clause of a SQL statement. So, perhaps something like this (I'm sort of making up column names here...you need to change them to what they are in your table):

SELECT docNo, BAC, AC, PV, EV, 0.5*EV*(1/PV + 1/AC) AS avgIndex
FROM tbl;

[NeoPa]

JPStokes:
ev, pv and ac are derived from summing up their respective columns from another table.

I may be confused here, but this sounds like your main requirement is about how to handle the data from some other table. This would be the table which you haven't included in the explanation?

ZeppHead has provided the fundamental answer to your question I suspect, but I'm guessing by reading between the lines, that you also need to include data from another table in your query. That would involve a JOIN statement in your FROM clause. More detailed help I'm unable to supply at this time as I simply haven't the requisite information available, but it should give you enough to progress with. If you need more then let us know, with the relevant information with which we can work.

[jpstokes]

@NeoPa, Yes I'm using information from another table to come up with my calculations for BAC, AC, PV, etc. Basically each of these values requires a query to be run which pulls information from another table and calculates the value (i.e. - BAC = F(x), AC = F(y), etc. with each function being a query + calculation. So then how do I mash all this info up into a select statement so that I can use it as the recordsource of my listbox?

select F(x), F(y), F(z) from....

[NeoPa]

JPStokes:
Basically each of these values requires a query to be run which pulls information from another table and calculates the value (i.e. - BAC = F(x), AC = F(y), etc. with each function being a query + calculation.

Where do you get that from. That sounds like you're pre-judging the answer to me (and leading yourself astray).

Why don't you reread my last post and respond to that directly. I need to understand what you're working with rather than your conclusions as to what the solution must be. I have my guesses as to what's required, but I don't jump so easily to conclusions without the supporting data.

[patjones]

One approach (there are certainly others) for using a single SQL statement is to utilize a subquery.

SELECT tA.BAC, tA.AC, tA.PV, ...
FROM (SELECT F(x) AS BAC, F(y) AS AC, ...
      FROM  ...) AS tA;

It's sort of like creating a table temporarily for the outer query to pull from. Then you can do as you please with BAC, AC, etc. Of course, if all you were doing was listing BAC, AC, PV and so on, then there would be no point to this.

[jpstokes]

zepphead80 that looks like the answer I'm looking for. I give it a try and let you know how it works out.