How to Subtract a Sum of several Child Records from an Initial Value in a Parent Record

[Guy_Smith]

I can't figure out how to subtract the total of several child records from a starting value in a parent record:  Sounds simple, but it's got me stumped!

My database is designed to issue Emissions Reduction Credits to companies for reducing or eliminating sources of air pollution.  Those companies then later use those credits to offset new construction, sell them to other companies for cold, hard cash, transfer them to other companies, etc.  The initial issuance is all on one Certificate and there can be between one and five pollutant credits on each individual Certificate.  A company can own many certificates, but a certificate can only belong to one company.  Once issued, a company can use the credits all at once or (more likely) a little at a time.  After each use/expenditure of credits, the certificate is reissued with the new balance of credits listed on it.  Thanks to a lot of great help in the Relationship subforum (Thanks again, Don and Bruce!), I've got the basic architecture down and issuing the initial credits works just like it should and the FIRST instance of credit usage calculates the new balance correctly, but when subsequent uses/expenditures are entered, the new balance ignores any previous expenditures.

I've tried dozens of different calculations, added TOs, lumped all transactions into one TO, filtered portals, and barked at the moon, and nothing works (though I did manage to scare the bejeezes out of my cat!)

I have attached my semi-sorta-solution to help clarify my problem.  It should open to the "Certificate" layout - the bottom portal has the usage data that isn't calculating the new balances correctly.  Any help in getting this up and running would be most appreciated - thanks in advance for sharing your time and talents.

 

Best Regards,

Guy

FMPA 15, Windows and Mac

ERC Registry v2 ModBFR Bad ERC Balance.fmp12

[bruceR]

You are standing at the pantry throwing ingredients at the blender.

You really have not described (and illustrated) exactly what you are trying to do.

We are watching and we can only guess. Are you trying to make scrambled eggs? A chocolate cake? It is impossible to know and therefore impossible to answer.

What exactly are you trying to do?

[Guy_Smith]

Sorry, Bruce - thought I summed it up okay.  Here's attempt #2:  The basic premise is that I need a database solution to track and report the issuance and usage of pollutant-specific Emission Reduction Credits, or ERCs, throughout a given region.  There are a total of five main different pollutants of concern:  CO, NOx, SOx, VOC, and PM10.  Credits for each of these pollutants of concern can be earned for reducing these pollutants by permanently shutting down equipment that emits any of these specific pollutants, or reducing the use of such equipment, or by upgrading the equipment, etc.  A certificate is issued to the company responsible for reducing these emissions and the Certificate lists the pollutant-specific credits they have earned.  The company may then use, sell, transfer, etc. these credits as they desire (assuming the use meets all governing regulations, of course).

Upon opening the Certificate layout, there is some info regarding a specific ERC Certificate and there are two portals.

The first thing I need to be able to do is to assign a Certificate to a specific company.  I can do that via the top portion of the layout.

Next, I need to be able to assign the initial amounts of pollutant-specific ERCs to that specific Certificate, such as 200 lbs of NOx ERCs or 87 lbs of VOC ERCs.  Or Both. Or any combination of anywhere from one to five different Pollutant specific amounts.  I can do that through the first portal.

Once I have the initial amounts of pollutant-specific ERC entered, I need to be able to expend/use varying amounts of varying ERCs assigned to that specific Certificate, such as using 44 lbs of VOC ERCs to allow the operation of a new engine, or selling 100 lbs of VOCs to another company.  Or both.  Or any combination of sales, transfers, internal consumption, etc., until all of the individual pollutant specific ERCs are used up and the Certificate is retired.  As part of this process, I need to know how many of the pollutant-specific ERCs are left on any given Certificate:  That is done by subtracting all of the pollutant-specific ERC usages from the initial pollutant-specific ERC issuance.

For example:  ABC Company is issued Certificate # 123 with 500 lbs of NOx ERCs and 1000 lbs of VOC ERCs.  They later use 100 lbs of NOx ERCs to allow the operation of an engine - their new Certificate Balances are now 400 lbs of NOx ERCs and 1000 lbs of VOC ERCs.  Then they sell 200 lbs of NOx ERCs to another company - their new Certificate Balances are now 200 lbs of NOx ERCs and 1000 lbs of VOC ERCs.  I need to be able to automatically calculate the new ERC balances and display them.  As the solution now exists, the incorrect display would show their new Certificate Balances of 300 lbs of NOx ERCs and 1000 lbs of VOC ERCs:  It is subtracting only the latest expenditure/use of pollutant-specific ERCs and NOT the total of all pollutant-specific expenditures.  The calculation formula I am using is incorrect, but I don't know why.

Ideally, I will be able to print the revised Certificates directly from the solution, but I obviously need the correct numbers to show up on them.  I also need to ensure I do not let a company use more ERCs than they have available - and I need the correct current balance to do that.

Lastly, I also need to produce reports for management that lists how many new pollutant-specific ERCs were generated in a given timeframe, a list of active Certificates (those with balances above zero for any pollutant-specific ERC), a list of total pollutant-specific ERCs available throughout the region (from ALL active Certificates) and from each company holding active Certificates, and a list of all Certificates by Company, preferably with their current pollutant-specific ERC Balances included.  I believe I can generate those reports once I get past this calc malfunction.

You're absolutely right about my approach - I'm throwing the fridge at the blender hoping for a five star meal, but logic and reason only got me as far as a very large brick wall.  I'm stuck and have no idea what the correct next steps are.

Thanks again for your time on this and I hope this second effort better explains the problem - and if you have any Chemistry or Nuclear Engineering questions, just let me know!

-Guy

[bruceR]

Oooh, meaningful detail! Very good progress!

[Guy_Smith]

Excellent news!  I hope I've provided enough detail to be useful without putting everyone to sleep.  I used to work for the Bureau of Repetitively Redundant Bureaucratic Repetition, so I'm usually told to shorten my explanations.

If I've left out any details anyone still wants, please let me know and I'll get it taken care of ASAP.

Thanks again,

-Guy