How to calculate the numeric total for a table column and then use that total to perform further calculations within that same table or in a report from that table?

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered
Can someone provide screen shots of how to accomplish this?  I've read most of the replies and tried what I can but I'm not able to do this.
Photo of Connie

Connie

  • 0 Points

Posted 3 years ago

  • 0
  • 1
You will need to provide more specifics on your tables and their relationships, if any.  To get a total, you will need to have a relationship to a master table which will have a summary field in a relationship to get the total.  Then that total can be looked up down to the child records.  So if you can describe your use case, I can offer suggestions on what that Master table would look like.
Photo of Connie

Connie

  • 0 Points
I've created a summary (master) table with a relationship to the (child) table in question but I don't know if I've configured the two relationships correctly.  

Details Table [IT Allocations] has the data I need the total for. This is the child table.
Master Table [Summary Table] has one record. This is the master table.

The relationship is Summary Table one to many IT Allocations (related summary total)

I cannot paste the screen shot I'm seeing, so here is what QB is showing:

Master Table - Summary Table
    Field                                    Field Type
record id#                               record id# (key field)
allocs                                       report link
add alloc                                 URL (formula)
sum IT allocs Budget field      numeric (summary)

Details Table - IT Allocations
    Field                                     Field Type
related summary total              Numeric (reference)
summary total - budget 1        Numeric (reference proxy)
sum IT allocs Budget field        Numeric (lookup)

Please let me know your thoughts.
Just FYI, you are able to edit your original post to post screen shots.

So, can you explain the problem now that I understand the relationships?  If you have a summary total up at the master table and can look that up back down to the detail records, what is your current roadblock?
Photo of Connie

Connie

  • 0 Points
I don't know if I've configured the relationship correctly.  When I do a report on the IT Allocations table, there is no data for field "sum IT allocs Budget field".  Is that the field in the IT Allocations table I should be looking at to contain/display the total number?
Is there the value you were expecting on that field on the Parent table?  If the summary field on the Master table is not correct, go back and see how it is configured.
Photo of Connie

Connie

  • 0 Points
Since I'm trying to work with a total for a column in the details/child table, will that total populate a field in the master/parent table?  I only see the total when I do a report, and it is on the line/row TOT.
I think that likely problem is that you have Created a summary table but have not linked any of the child records to it.  If you like, contact me via the information in my profile and I will have a quick look at your app with you chandler get that total working.
Photo of Connie

Connie

  • 0 Points
left my contact info for you
Connie,
Did you leave me voicemail or an email?
Photo of Connie

Connie

  • 0 Points
I called and left a voice message