How to work with individual records in detail section of report

[jpstokes]

I'm trying to develop a report that is based on a 3 level hierarchal data modeling system. Each tier is a summation of the layer below it. When printing a report based on the top level the template for the report is pretty much the following:

project title, actual costs, earned value, planned value

My problem is that I've created a query that gets most of the data I need but I can't perform the calculations in the same query. In order to compute the values I need I believe I need to do a separate query and compute a value that I then set as the value of my computed fields.

How do I do this?

How do I create a query and execute that query on a per record basis?

[Rabbit]

We would need to know what calculation you're trying to do.

[jpstokes]

The calculation are as such:

SV = EV - PV
CV = EV - AC
CPI = EV / AC
SPI = EV / PV

I can't figure out how to do these calculations and display them with the other record details. I created a query that gets most of the info I need but I can't figure out how to do these calculation and show them in the rows under their respective headers. I've attached an image to show what this report should look like.
[IMGNOTHUMB]http://bytes.com/attachments/attachment/6076d1329109529/screen-shot-2012-02-13-12.04.03-am.jpg[/IMGNOTHUMB]

[Mihail]

The formula in the text boxes where you wish to show the SV value must be something like that:

=[EV_FieldName]-[PV_FieldName]

Of course you must replace my EV_FieldName and PV_FieldName with the real field names in your daatabase (report)

[Gail Williams]

What you might want to think about is a two step approach. I have similar project reporting which I've had to write, and I achieve this by doing a single line calculation for each expression per line at the lower level in our case, work order (see advise given by Mihail).

When reporting at the higher project level, because I need to add up multiple lower level work order lines, I use the original query with the calculations in and then do a summation query grouped by project, CAVEAT - you do need to look out for mathematical oddities when doing this, so often I will add up all the original figures then re-do the calculations again in this 'totals' query, but the necessity of this will depend on the calculations you're doing and what the end result needs to be.

Once you have both query levels to work with, it's down to clever report writing, which depends on how the reports are to be presented.

Hope that helps.

[jpstokes]

In the following code I've grap all the data and did as many calculations as possible in the original query however, I'm missing a few that I could not figure out how to get in this query.

    Me.RecordSource = "SELECT PROJECTS.TITLE, FUNDING_DOC.FUNDING_DOC_NO, FUNDING_DOC.FUNDING_TYPE, FUNDING_DOC.EXP_DATE, PROJECTS.ID, SUM(TASKS.PLANNED_AMT) AS BAC, " & _
    "SUM(EXPENSES.EXPENSE_AMOUNT) AS EV, " & _
    "SUM(TASKS.UPDATED_PV) AS PV " & _
    "FROM ((PROJECTS LEFT JOIN FUNDING_DOC ON PROJECTS.ID = FUNDING_DOC.PROJECT_ID_FK) " & _
    "LEFT JOIN TASKS ON TASKS.FUNDING_DOC_ID_FK = FUNDING_DOC.ID) " & _
    "LEFT JOIN EXPENSES ON EXPENSES.TASK_ID_FK = TASKS.ID " & _
    "WHERE PROJECTS.PUBLISHED = TRUE AND FUNDING_DOC.PUBLISHED = TRUE AND TASKS.PUBLISHED = TRUE " & _
    "GROUP BY PROJECTS.TITLE, FUNDING_DOC.FUNDING_DOC_NO, FUNDING_DOC.FUNDING_TYPE, FUNDING_DOC.EXP_DATE, PROJECTS.ID;"
    
    Me.tbFundingDocNo.ControlSource = "[FUNDING_DOC_NO]"
    Me.tbBac.ControlSource = "[BAC]"
    Me.tbEv.ControlSource = "[EV]"
    'Me.tbAc.ControlSource = ???
    Me.tbCv.ControlSource = "[EV] - [AC]" 'doesn't work
    Me.tbSv.ControlSource = "[EV] - [PV]" 'doesn't work
    Me.tbPv.ControlSource = "[PV]"

One of the calculation I'm missing for example is Actual Costs (AC) which is calculated using the expense table where expenses.status = "expended". I can't figure out how to get this number. I need something like:

AC = select tasks.id, sum(expenses.expense_amount) from expenses left join tasks on expenses.tasks_id_fk = tasks.id where expenses.status = 'expended' group by task.id

As you can see in the query above the EXPENSE table has a 1 to many relationship with my TASKS table. I'm just not sure how to get this number for each of the records when I'm doing project level report. As far as using the field names to do the formulas I don't think this will work because if I filter out the expenses in the current query then I won't be able to calculated EARNED VALUE (EV) which is the sum of all expenses regardless of status.

[jpstokes]

Knowing how my database setup may help you help me. So this is pretty much what it looks like:

PROJECTS
+ FUNDING_DOCS
++ TASKS
+++ EXPENSES

each of the tables has a 1 to many relationship with the one above and I'm reporting from the top level, PROJECTS and rolling up all summation to that level as well.

[reggieadkins]

Jason, Mihai answered your question.

[jpstokes]

OK..Mihai response sort of answer my question but it doesn't solve my issue with getting a value for Actual Cost where the solution is the result of doing a specific query in which you only sum expense.expense _amount where expense.status = ' expended'.

Earned value is also computed from expense.expense _amount but it sums all values regardless of expense.status.

Just seems like this should be two queries or a very complicated query that I haven't thought off. I was trying to create a function that I could call with no luck:

' function call
Me.tbAc.ControlSource = getActualCost("[FUNDING_DOC_NO]")

' function to populate AC control
Public Function getActualCost(fundingDocId As String) As Double

    Set objRS = New ADODB.Recordset
    
    objRS.Open "SELECT SUM(EXPENSES.EXPENSE_AMOUNT) FROM ((FUNDING_DOC LEFT JOIN TASKS ON FUNDING_DOC.ID = TASKS.FUNDING_DOC_ID_FK) " & _
    "LEFT JOIN EXPENSES ON EXPENSES.TASK_ID_FK = TASKS.ID) " & _
    "WHERE FUNDING_DOC.ID = " & fundingDocId & " AND  FUNDING_DOC.PUBLISHED = TRUE AND TASKS.PUBLISHED = TRUE AND EXPENSES.PUBLISHED = TRUE AND EXPENSES.STATUS = 'Expensed';", _
    CurrentProject.Connection
    
    If Not objRS.EOF Then
      getActualCost = Nz(objRS.Fields(0).Value, 0)
    End If
    
    objRS.Close
    
    Set objRS = Nothing
    
End Function

[Rabbit]

Your requirements have changed from post to post to the point where I'm confused as to what the actual requirements are. Some sample data and their results would go a long way towards understanding what you're looking for.

[jpstokes]

Rabbit, sorry if it sounds that way it's actually the same problem just trying to narrow down to what my exact problem is so that you guys can help. I've attached a screen shot of the report in my second post. Basically my report will look like this:

Project 1

Funding 1 BAC AC EV CV SV INDEX EXP DATE
Funding 2 BAC AC EV CV SV INDEX EXP DATE

Project 2

Funding 3 BAC AC EV CV SV INDEX EXP DATE
Funding 4 BAC AC EV CV SV INDEX EXP DATE

Each field other than project title and funding title is computed from a formula. The example I've been using so far is Actual Cost (AC). To me this field needs to execute a separate query in order to come up with the right value. AC can only be calculated by using a conditional which is why I can't just sum the fields in my report to come up with a value for AC like Mihail suggested.

So long story short my requirement right now is get the correct value for AC while keeping the report structure I've illustrated above...If I could do that I think I could figure out how to do it for the other Items.

[reggieadkins]

Have you tried using 'with rollup'?

[Rabbit]

You keep telling me how you want to accomplish your goals when what I'm asking for is what is the goal. And to know the goal, I need to know what the start is. What I'm asking for is this:

My raw data looks like this:

field1 field2
a      5
a      10
b      8

My goal is to end up with this:

field1 sum
a      15
b      8

[jpstokes]

Rabbit, I'm trying to do what you have sorta...I would add to your raw data another column for status

field1 field2 field3 (status)
a        5        obligated
a        10      expended
b        8        committed

My goal is to get:

field1   sum(field2)   sum(field2 where status = 'expended')
a          15            10
b          8              0

[jpstokes]

OK..Just about got it now. I had to just build a more complex query. I just have one line that's causing problems now.

    Me.RecordSource = "SELECT T1.*, T2.AC, (T1.EV - T2.AC) AS CV, (T1.EV - T1.PV) AS SV, " & _
    "(IIF (NZ(T1.PV, 0) = 0 OR NZ(T2.AC, 0) = 0), 0, (T1.EV / T1.PV + T1.EV/T2.AC)/2) AS INDEX FROM " & _
    "(SELECT PROJECTS.ID, PROJECTS.TITLE, FUNDING_DOC.FUNDING_DOC_NO, FUNDING_DOC.FUNDING_TYPE, FUNDING_DOC.EXP_DATE, SUM(TASKS.PLANNED_AMT) AS BAC, " & _
    "SUM(EXPENSES.EXPENSE_AMOUNT) AS EV, " & _
    "SUM(TASKS.UPDATED_PV) As PV " & _
    "FROM ((PROJECTS LEFT JOIN FUNDING_DOC ON PROJECTS.ID = FUNDING_DOC.PROJECT_ID_FK) " & _
    "LEFT JOIN TASKS ON TASKS.FUNDING_DOC_ID_FK = FUNDING_DOC.ID) " & _
    "LEFT JOIN EXPENSES ON EXPENSES.TASK_ID_FK = TASKS.ID " & _
    "WHERE PROJECTS.PUBLISHED = True And FUNDING_DOC.PUBLISHED = True And TASKS.PUBLISHED = True " & _
    "GROUP BY PROJECTS.TITLE, FUNDING_DOC.FUNDING_DOC_NO, FUNDING_DOC.FUNDING_TYPE, FUNDING_DOC.EXP_DATE, PROJECTS.ID " & _
    ") AS T1 LEFT JOIN " & _
    "(SELECT FUNDING_DOC.FUNDING_DOC_NO, SUM(EXPENSES.EXPENSE_AMOUNT)  AS AC " & _
    "FROM (FUNDING_DOC LEFT JOIN TASKS ON FUNDING_DOC.ID = TASKS.FUNDING_DOC_ID_FK) " & _
    "LEFT JOIN EXPENSES ON TASKS.ID = EXPENSES.TASK_ID_FK WHERE EXPENSES.STATUS = 'EXPENDED' " & _
    "GROUP BY FUNDING_DOC.FUNDING_DOC_NO " & _
    ") AS T2 ON T1.FUNDING_DOC_NO = T2.FUNDING_DOC_NO;"

ACCESS is complaining that there is a syntax error in this line

"(IIF (NZ(T1.PV, 0) = 0 OR NZ(T2.AC, 0) = 0), 0, (T1.EV / T1.PV + T1.EV/T2.AC)/2) AS INDEX FROM "