By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,149 Members | 1,016 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,149 IT Pros & Developers. It's quick & easy.

How to work with individual records in detail section of report

P: 18
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?
Feb 13 '12 #1

✓ answered by jpstokes

NeoPa, I just took the code out of context but that line is just a line in the rest of the code. Anyway, I did figure it out all finally last night. My solution was to write a huge query (just my opinion) that did all the computations. My sql mastery just went up a notch.

Thanks for all the help.

Expand|Select|Wrap|Line Numbers
  1.     Me.RecordSource = "SELECT T1.*, FORMAT(NZ(T2.AC, 0), '$###,###,##0') AS AC, T3.EV, FORMAT(NZ(T3.EV - T2.AC, 0), '$###,###,##0') AS CV, FORMAT(NZ(T3.EV - T1.PV, 0), '$###,###,##0') AS SV, " & _
  2.     "IIF ((NZ(T1.PV, 0) > 0 AND NZ(T2.AC, 0) > 0), FORMAT((T3.EV/T1.PV + T3.EV/T2.AC)/2, '0.00'), 0) AS INDEX FROM " & _
  3.     "((SELECT PROJECTS.ID, PROJECTS.TITLE, FUNDING_DOC.FUNDING_DOC_NO, FUNDING_DOC.FUNDING_TYPE, FUNDING_DOC.EXP_DATE, SUM(NZ(TASKS.PLANNED_AMT, 0)) AS BAC, " & _
  4.     "SUM(NZ(TASKS.UPDATED_PV, 0)) As PV " & _
  5.     "FROM ((PROJECTS LEFT JOIN FUNDING_DOC ON PROJECTS.ID = FUNDING_DOC.PROJECT_ID_FK) " & _
  6.     "LEFT JOIN TASKS ON TASKS.FUNDING_DOC_ID_FK = FUNDING_DOC.ID) " & _
  7.     "WHERE PROJECTS.PUBLISHED = True And FUNDING_DOC.PUBLISHED = True And TASKS.PUBLISHED = True " & _
  8.     "GROUP BY PROJECTS.TITLE, FUNDING_DOC.FUNDING_DOC_NO, FUNDING_DOC.FUNDING_TYPE, FUNDING_DOC.EXP_DATE, PROJECTS.ID " & _
  9.     ") AS T1 LEFT JOIN " & _
  10.     "(SELECT FUNDING_DOC.FUNDING_DOC_NO, SUM(EXPENSES.EXPENSE_AMOUNT)  AS AC " & _
  11.     "FROM (FUNDING_DOC LEFT JOIN TASKS ON FUNDING_DOC.ID = TASKS.FUNDING_DOC_ID_FK) " & _
  12.     "LEFT JOIN EXPENSES ON TASKS.ID = EXPENSES.TASK_ID_FK WHERE EXPENSES.STATUS = 'Expensed' " & _
  13.     "GROUP BY FUNDING_DOC.FUNDING_DOC_NO " & _
  14.     ") AS T2 ON T1.FUNDING_DOC_NO = T2.FUNDING_DOC_NO)" & _
  15.     "LEFT JOIN " & _
  16.     "(SELECT FUNDING_DOC.FUNDING_DOC_NO, SUM(EXPENSES.EXPENSE_AMOUNT) AS EV 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 " & _
  17.     "GROUP BY FUNDING_DOC.FUNDING_DOC_NO) AS T3 ON T2.FUNDING_DOC_NO = T3.FUNDING_DOC_NO;"
  18.  

Share this Question
Share on Google+
18 Replies


Rabbit
Expert Mod 10K+
P: 12,366
We would need to know what calculation you're trying to do.
Feb 13 '12 #2

P: 18
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.

Attached Images
File Type: jpg Screen Shot 2012-02-13 at 12.04.03 AM.jpg (15.9 KB, 345 views)
Feb 13 '12 #3

100+
P: 759
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)
Feb 13 '12 #4

P: 12
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.
Feb 13 '12 #5

P: 18
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.

Expand|Select|Wrap|Line Numbers
  1.     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, " & _
  2.     "SUM(EXPENSES.EXPENSE_AMOUNT) AS EV, " & _
  3.     "SUM(TASKS.UPDATED_PV) AS PV " & _
  4.     "FROM ((PROJECTS LEFT JOIN FUNDING_DOC ON PROJECTS.ID = FUNDING_DOC.PROJECT_ID_FK) " & _
  5.     "LEFT JOIN TASKS ON TASKS.FUNDING_DOC_ID_FK = FUNDING_DOC.ID) " & _
  6.     "LEFT JOIN EXPENSES ON EXPENSES.TASK_ID_FK = TASKS.ID " & _
  7.     "WHERE PROJECTS.PUBLISHED = TRUE AND FUNDING_DOC.PUBLISHED = TRUE AND TASKS.PUBLISHED = TRUE " & _
  8.     "GROUP BY PROJECTS.TITLE, FUNDING_DOC.FUNDING_DOC_NO, FUNDING_DOC.FUNDING_TYPE, FUNDING_DOC.EXP_DATE, PROJECTS.ID;"
  9.  
  10.     Me.tbFundingDocNo.ControlSource = "[FUNDING_DOC_NO]"
  11.     Me.tbBac.ControlSource = "[BAC]"
  12.     Me.tbEv.ControlSource = "[EV]"
  13.     'Me.tbAc.ControlSource = ???
  14.     Me.tbCv.ControlSource = "[EV] - [AC]" 'doesn't work
  15.     Me.tbSv.ControlSource = "[EV] - [PV]" 'doesn't work
  16.     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:

Expand|Select|Wrap|Line Numbers
  1. 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
  2.  
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.
Feb 13 '12 #6

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

Expand|Select|Wrap|Line Numbers
  1. PROJECTS
  2. + FUNDING_DOCS
  3. ++ TASKS
  4. +++ EXPENSES
  5.  
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.
Feb 13 '12 #7

P: 2
Jason, Mihai answered your question.
Feb 13 '12 #8

P: 18
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:

Expand|Select|Wrap|Line Numbers
  1. ' function call
  2. Me.tbAc.ControlSource = getActualCost("[FUNDING_DOC_NO]")
  3.  
  4. ' function to populate AC control
  5. Public Function getActualCost(fundingDocId As String) As Double
  6.  
  7.     Set objRS = New ADODB.Recordset
  8.  
  9.     objRS.Open "SELECT SUM(EXPENSES.EXPENSE_AMOUNT) FROM ((FUNDING_DOC LEFT JOIN TASKS ON FUNDING_DOC.ID = TASKS.FUNDING_DOC_ID_FK) " & _
  10.     "LEFT JOIN EXPENSES ON EXPENSES.TASK_ID_FK = TASKS.ID) " & _
  11.     "WHERE FUNDING_DOC.ID = " & fundingDocId & " AND  FUNDING_DOC.PUBLISHED = TRUE AND TASKS.PUBLISHED = TRUE AND EXPENSES.PUBLISHED = TRUE AND EXPENSES.STATUS = 'Expensed';", _
  12.     CurrentProject.Connection
  13.  
  14.     If Not objRS.EOF Then
  15.       getActualCost = Nz(objRS.Fields(0).Value, 0)
  16.     End If
  17.  
  18.     objRS.Close
  19.  
  20.     Set objRS = Nothing
  21.  
  22. End Function
  23.  
Feb 13 '12 #9

Rabbit
Expert Mod 10K+
P: 12,366
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.
Feb 13 '12 #10

P: 18
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.
Feb 13 '12 #11

P: 2
Have you tried using 'with rollup'?
Feb 13 '12 #12

Rabbit
Expert Mod 10K+
P: 12,366
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:
Expand|Select|Wrap|Line Numbers
  1. field1 field2
  2. a      5
  3. a      10
  4. b      8
My goal is to end up with this:
Expand|Select|Wrap|Line Numbers
  1. field1 sum
  2. a      15
  3. b      8
Feb 13 '12 #13

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

Expand|Select|Wrap|Line Numbers
  1. field1 field2 field3 (status)
  2. a        5        obligated
  3. a        10      expended
  4. b        8        committed
  5.  
My goal is to get:

Expand|Select|Wrap|Line Numbers
  1. field1   sum(field2)   sum(field2 where status = 'expended')
  2. a          15            10
  3. b          8              0
  4.  
Feb 13 '12 #14

P: 18
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.

Expand|Select|Wrap|Line Numbers
  1.     Me.RecordSource = "SELECT T1.*, T2.AC, (T1.EV - T2.AC) AS CV, (T1.EV - T1.PV) AS SV, " & _
  2.     "(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 " & _
  3.     "(SELECT PROJECTS.ID, PROJECTS.TITLE, FUNDING_DOC.FUNDING_DOC_NO, FUNDING_DOC.FUNDING_TYPE, FUNDING_DOC.EXP_DATE, SUM(TASKS.PLANNED_AMT) AS BAC, " & _
  4.     "SUM(EXPENSES.EXPENSE_AMOUNT) AS EV, " & _
  5.     "SUM(TASKS.UPDATED_PV) As PV " & _
  6.     "FROM ((PROJECTS LEFT JOIN FUNDING_DOC ON PROJECTS.ID = FUNDING_DOC.PROJECT_ID_FK) " & _
  7.     "LEFT JOIN TASKS ON TASKS.FUNDING_DOC_ID_FK = FUNDING_DOC.ID) " & _
  8.     "LEFT JOIN EXPENSES ON EXPENSES.TASK_ID_FK = TASKS.ID " & _
  9.     "WHERE PROJECTS.PUBLISHED = True And FUNDING_DOC.PUBLISHED = True And TASKS.PUBLISHED = True " & _
  10.     "GROUP BY PROJECTS.TITLE, FUNDING_DOC.FUNDING_DOC_NO, FUNDING_DOC.FUNDING_TYPE, FUNDING_DOC.EXP_DATE, PROJECTS.ID " & _
  11.     ") AS T1 LEFT JOIN " & _
  12.     "(SELECT FUNDING_DOC.FUNDING_DOC_NO, SUM(EXPENSES.EXPENSE_AMOUNT)  AS AC " & _
  13.     "FROM (FUNDING_DOC LEFT JOIN TASKS ON FUNDING_DOC.ID = TASKS.FUNDING_DOC_ID_FK) " & _
  14.     "LEFT JOIN EXPENSES ON TASKS.ID = EXPENSES.TASK_ID_FK WHERE EXPENSES.STATUS = 'EXPENDED' " & _
  15.     "GROUP BY FUNDING_DOC.FUNDING_DOC_NO " & _
  16.     ") AS T2 ON T1.FUNDING_DOC_NO = T2.FUNDING_DOC_NO;"
  17.  
ACCESS is complaining that there is a syntax error in this line

Expand|Select|Wrap|Line Numbers
  1. "(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 "
  2.  
Feb 14 '12 #15

NeoPa
Expert Mod 15k+
P: 31,494
JPStokes:
ACCESS is complaining that there is a syntax error in this line
How? That is a separate line in neither SQL nor VBA.

Anyway, try :
Expand|Select|Wrap|Line Numbers
  1.         "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 " & _
Feb 14 '12 #16

P: 18
NeoPa, I just took the code out of context but that line is just a line in the rest of the code. Anyway, I did figure it out all finally last night. My solution was to write a huge query (just my opinion) that did all the computations. My sql mastery just went up a notch.

Thanks for all the help.

Expand|Select|Wrap|Line Numbers
  1.     Me.RecordSource = "SELECT T1.*, FORMAT(NZ(T2.AC, 0), '$###,###,##0') AS AC, T3.EV, FORMAT(NZ(T3.EV - T2.AC, 0), '$###,###,##0') AS CV, FORMAT(NZ(T3.EV - T1.PV, 0), '$###,###,##0') AS SV, " & _
  2.     "IIF ((NZ(T1.PV, 0) > 0 AND NZ(T2.AC, 0) > 0), FORMAT((T3.EV/T1.PV + T3.EV/T2.AC)/2, '0.00'), 0) AS INDEX FROM " & _
  3.     "((SELECT PROJECTS.ID, PROJECTS.TITLE, FUNDING_DOC.FUNDING_DOC_NO, FUNDING_DOC.FUNDING_TYPE, FUNDING_DOC.EXP_DATE, SUM(NZ(TASKS.PLANNED_AMT, 0)) AS BAC, " & _
  4.     "SUM(NZ(TASKS.UPDATED_PV, 0)) As PV " & _
  5.     "FROM ((PROJECTS LEFT JOIN FUNDING_DOC ON PROJECTS.ID = FUNDING_DOC.PROJECT_ID_FK) " & _
  6.     "LEFT JOIN TASKS ON TASKS.FUNDING_DOC_ID_FK = FUNDING_DOC.ID) " & _
  7.     "WHERE PROJECTS.PUBLISHED = True And FUNDING_DOC.PUBLISHED = True And TASKS.PUBLISHED = True " & _
  8.     "GROUP BY PROJECTS.TITLE, FUNDING_DOC.FUNDING_DOC_NO, FUNDING_DOC.FUNDING_TYPE, FUNDING_DOC.EXP_DATE, PROJECTS.ID " & _
  9.     ") AS T1 LEFT JOIN " & _
  10.     "(SELECT FUNDING_DOC.FUNDING_DOC_NO, SUM(EXPENSES.EXPENSE_AMOUNT)  AS AC " & _
  11.     "FROM (FUNDING_DOC LEFT JOIN TASKS ON FUNDING_DOC.ID = TASKS.FUNDING_DOC_ID_FK) " & _
  12.     "LEFT JOIN EXPENSES ON TASKS.ID = EXPENSES.TASK_ID_FK WHERE EXPENSES.STATUS = 'Expensed' " & _
  13.     "GROUP BY FUNDING_DOC.FUNDING_DOC_NO " & _
  14.     ") AS T2 ON T1.FUNDING_DOC_NO = T2.FUNDING_DOC_NO)" & _
  15.     "LEFT JOIN " & _
  16.     "(SELECT FUNDING_DOC.FUNDING_DOC_NO, SUM(EXPENSES.EXPENSE_AMOUNT) AS EV 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 " & _
  17.     "GROUP BY FUNDING_DOC.FUNDING_DOC_NO) AS T3 ON T2.FUNDING_DOC_NO = T3.FUNDING_DOC_NO;"
  18.  
Feb 14 '12 #17

NeoPa
Expert Mod 15k+
P: 31,494
It appears to me that the original problem is still there in line #2. Notice where all the parentheses are and consider what they're trying to achieve. I don't believe your latest code is correct here because the first IIF() function call finishes before the FORMAT() function call.
Feb 14 '12 #18

Rabbit
Expert Mod 10K+
P: 12,366
Your sample data makes things a lot clearer. Do something like this:
Expand|Select|Wrap|Line Numbers
  1. SELECT T1.field1, T1.SumField2, T2.SumExpended
  2. FROM (
  3.    SELECT field1, SUM(field2) AS SumField2
  4.    FROM tableName
  5.    GROUP BY field1
  6. ) T1 LEFT JOIN (
  7.    SELECT field1, SUM(field2) AS SumExpended
  8.    FROM tableName
  9.    WHERE Status = 'Expended'
  10.    GROUP BY field1
  11. ) T2 ON T1.field1 = T2.field1
Alternatively, you could do it without subqueries by using a SUM on IIF.
Feb 14 '12 #19

Post your reply

Sign in to post your reply or Sign up for a free account.