SemperFi
New Member
- Joined
- Aug 21, 2014
- Messages
- 22
Hello Team
I'm hoping someone can shed some light on this problem. I have an accounts payable table where the expenses are listed by incident numbers or PR, I'm using the incident number as the "primary key" since is unique to the expense (the PR is unique as well and either one can be used), this would be the equivalent of a many to many types of listing where I can have multiple incidents or PR (as one unit) with different invoices and expenses (invoice amount). Each PR or Incident has an approved amount once I near 30% of that amount I'll need to request more funds. The problem that I'm having is that when I run a pivot, the approved amount is listed as one unit, which is great, but the total available is listed as each individual expense, and I need the total available to "subtract all expenses (related to either the PR/Incident), from the approved amount.
In the image you can see some highlighted entries where the incident/PR are repeated units with different amounts reported, some are in zero amounts. The idea is to group all expenses from either the PR or incident an subtract the invoice amount from the approved amount so I can show one general balance. See example below:
Please note: the approved amount will never change, is a fixed number for the life of the expense, the invoice amount will change.
Thank you
I'm hoping someone can shed some light on this problem. I have an accounts payable table where the expenses are listed by incident numbers or PR, I'm using the incident number as the "primary key" since is unique to the expense (the PR is unique as well and either one can be used), this would be the equivalent of a many to many types of listing where I can have multiple incidents or PR (as one unit) with different invoices and expenses (invoice amount). Each PR or Incident has an approved amount once I near 30% of that amount I'll need to request more funds. The problem that I'm having is that when I run a pivot, the approved amount is listed as one unit, which is great, but the total available is listed as each individual expense, and I need the total available to "subtract all expenses (related to either the PR/Incident), from the approved amount.
In the image you can see some highlighted entries where the incident/PR are repeated units with different amounts reported, some are in zero amounts. The idea is to group all expenses from either the PR or incident an subtract the invoice amount from the approved amount so I can show one general balance. See example below:
Please note: the approved amount will never change, is a fixed number for the life of the expense, the invoice amount will change.
Thank you
