Guy_Smith

Engine Exhaust Emissions Database ERD is Circular!

8 posts in this topic

I am trying to set up a database to calculate and report exhaust emissions from diesel engines and am stumped at Step #1:  The infamous Entity Relationship Diagram!

I am looking at a few thousand engines and need to calculate how much of what kind of pollutants they kick out into the air.  I have spreadsheets that list the Year of Manufacture, the horsepower, the serial number, how long the engine ran during a given year and how much fuel it consumed during that year.  I also have some spreadsheets that list what the Emission Factors, usually expressed in pounds per hour operated or pounds per gallon of fuel consumed, for each of the two dozen chemical compounds I’m interested in tracking.

Those emissions are relatively simple to calculate:  Multiply the Emission Factor times the number of hours operated (or times the gallons of fuel consumed) and we’re done.  Except for the fact that the Emission Factors are based on the Year of Manufacture and, in some cases, the horsepower rating of the engine, so I have to circle back around to the Engine table:  I don’t know how to connect the emission factor for, say, Arsenic to a specific engine, say a 154 hp Chevy made in 2014,  based on that engine’s year of manufacture and hp rating.

So my ERD keeps looking something like this:  Engines --->Emission Factors--->Engines   or   Engines ---<  Emissions >---Emission Factors--->Engines

My computer keeps puking ones and zeroes all over my keyboard when I try either setup in my Relationship Graph!

The only other way I can think of accomplishing these seemingly simple calculations is to write a boatload of scripts with variables, but I’m not even sure I could come up with all of the combinations/permutations involved.  And, unfortunately, I can’ even spell SQL, let alone know how to execute one of ‘em!

I have attached a (simplified) copy of the spreadsheets I currently use to better explain my problem.  I hope one of you will find this to be mind-numbingly simple and explain how it should be done.  Thanks very much for your time and consideration.

Sincerely,

Guy

Engine Emissions Calc Sheet MOD for FMF.xlsx

Share this post


Link to post
Share on other sites

Posted (edited)

On 03/22/2017 at 1:13 AM, Guy_Smith said:

the Emission Factors are based on the Year of Manufacture and, in some cases, the horsepower rating of the engine

You have to start by splitting this into individual records in an EmissionFactors table that has fields for:

• Substance
• YearOfManufacture
• MinimumHorsePower
• EmissionFactor
• Units

Then for your report generate a record in a third Emissions table for each Engine/Substance combination, and let it calculate the total emission. So your structure would be:

Engines -< Emissions >- EmissionFactors

and the relationship between Emissions and EmissionFactors would be defined as:

Emissions::Substance = EmissionFactors::Substance
AND
Emissions::YearOfManufacture = EmissionFactors::YearOfManufacture
AND
Emissions::HorsePower ≤ EmissionFactors::MinimumHorsePower

with the records on the EmissionFactors side sorted by MinimumHorsePower, descending.

 

On 03/22/2017 at 1:13 AM, Guy_Smith said:

how long the engine ran during a given year

I don't see a given year in the example. If you have  year-by-year data, this may affect the structure.

 

 

 

 

 

Edited by comment
1 person likes this

Share this post


Link to post
Share on other sites

Comment:  Thank you so much for the help on this!  And, of course, you're a step ahead of me already - I am currently only collecting data for 2016, but I anticipate that it will turn into an annual event and I will need to modify my database to include multiple reporting years.  Any helpful hints on how to tweak the solution to handle that?

Thanks again,

Guy

 

Share this post


Link to post
Share on other sites
1 hour ago, Guy_Smith said:

I will need to modify my database to include multiple reporting years.

Again, you will need a table where the yearly engine consumption data is stored using an individual record for each engine/year combination. I don't know how you're getting your data, but ideally you would have:

Engines -< EngineYearlyConsumptions -< Emissions >- EmissionFactors

Note that a record in Emissions table must lookup the YearOfManufacture and HorsePower values from its ancestor in Engines, in order to link to the correct record in EmissionFactors.

Share this post


Link to post
Share on other sites

That actually makes sense to me - who'd have thunk it?!

Thanks again, Comment - your willingness to help us neophytes is a true blessing!

Share this post


Link to post
Share on other sites

Rats! Phooey!  I'm still stuck - I can't get the emissions join table to populate.  I split the emission factor data into individual records, I've verified that I have the box checked to allow creation of records in the Emissions table and I've tried using a portal from the Engine table to display the emissions, but still nothing.  I maybe shouldn't be pulling the data for the Engine bhp and Tier from the Engine table or the CAS and Pollutant Names from the Emissions Factor table (for the Emissions table), but I don't know how else to do that (I also tried to do automatic lookups for those fields, but it didn't work, either). Any chance of looking at my file and seeing where I went wrong?

And, by the way, you will see the word "Tier" used in place of Year of Manufacture:  Tiers are basically groups of years used by the EPA. Using them in this instance saves me some time as I only have to enter emission factor records for one Tier instead of three or four records, one for each year in the Tier.

 

Thanks again for all of your assistance,

Guy

HARP DICE EmsInv Calc Sheet.fmp12

Share this post


Link to post
Share on other sites

Posted (edited)

I am afraid I cannot look at your file. In case it wasn't clear, you need to run a script that will create a record in the Emissions table for each combination of  EngineYearlyConsumptions and Substance (i.e. every row in your report). There is no other way to populate the table (unless you want to do it manually, or you have an outside source you can import)).

 

Edited by comment
1 person likes this

Share this post


Link to post
Share on other sites

Yeah, I sorta missed the "generate a record" part of your first response :B

I got the script to work and my database is up and running thanks to your guidance - thanks so much and have a great afternoon!

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now

  • Similar Content

    • By Rikki
      Hi guys,
      Sorry I do not know the terminology of what I need.
      I am trying to setup a database to record some info on postcodes. What I need to do is, each supplier can have 4 types of lead (back data, profile, survey, 2nd use). I have two tables for suppliers and postcodes already. I also have 2 join tables for many to many relationships. I need each field in the postcodes database to be able to store a count for each supplier's lead type.
      Eg:
      Supplier    - Lead Type - Postcode - Count
      Supplier A - Back Data - AB            - 20
                                           - BB            - 155
                                           - CB            - 30
       
                           Profile     - AB             - 55
                                          - BB             - 27
      My problem is with the way I have set the relationships up currently. When I add a count for a postcode it adds that count for all suppliers. I need the postcode count to be specific to each supplier and lead type.
      Can anyone tell me what I need to do to get this working correctly? I have attached a screenshot of my relationships.
      Thanks

    • By avo388
      Hello!
      I have re configuring the Research Notes Starter Solution - it works quite well for what I need. But I want to create a couple of extra tabs with portals, but I'm having trouble understanding the how the current IDs are being created (using a calculation) for the TYPE MATCH FIELD in the Data table for Notes, Documents and Media?
      Could anyone help me understand this? Should I just build it from scratch? I'm using Version 14.
      Thanks for your time.
    • By fireandlight
      I've driven myself more or less crazy trying to figure this out on my own. The relationships themselves are rather simple: Each project has a list of questions. Each question has a person who created the question, and someone the question is assigned to. A question also has a number of responses associated with it which each have their own "creator".
      There are 3 main tables to cover: Questions, People, and Responses
      People consists of 3 fields: The UID, the person's name, and the person's email address Questions has 2 fields which relate to People: creatorID and assigneeID Responses has 1 field which relates to People: creatorID. The way the relationship graph is set up now, I have 3 instances of the People table that relate the UID field to the appropriate ID field in Questions or Responses as needed.
      What I want to happen is when I set an Assignee or Creator for a question or response, I want to use the corresponding record details from the People table - if one does not exist, then create a new one. Instead what happens is a get a new record in the people table every time. Even if I set the Layout field to give me a drop down of the existing values (and I use it to select one when, say, setting the assignee on a question) it still creates a new record. So confused... any help is much appreciated.
    • By JoP
      I have a solution with 4 Databases. There is the main-data-database on a Filemaker Server (only data, no relationships in the Table-Occurences (TOs)) and a GUI Database on the Desktop (only Layouts, TO's are included from the Server-Database and on this TO's the relationships are defined, so I can use them in the Layouts. Than I have a 3rd database, which is a subset of the server database and lies on an iPad. This 2 data-databases will be synchronised by MirrorSync. And the 4th Database is a special GUI Database on the iPad which uses the 3rd database for Data. The iPad GUI Database also holds TO's with relationships.
      Now, I need a custom privilege set to forbid changes and deletions of records when they are marked as Locked. To see all tables in the privilege dialogs, I can only do this in my data-databases, because in the GUI databases I can't see the tables in the dialogs. But in the data-databases I have no relationships defined, so I can't use related tables in the formulas. Now I have to add these relationships also to the data-databases. Is there a simple way to "copy" them from one database to an other?
      Thanks, Hans
    • By Ultradistance2005
      I have a database with each record representing one account. Within each account record, there are ten different companies the account may have been sent to (one individual field for each company within the same table), along with the results of that effort. Both of these make up the company data set. Also within that single record there is an identifying ID Code, which represents the type of account it is.
       
      What I want to do now is create a table with a portal where I can select or enter an ID Code, and get each instance of that ID Code with each individual company data set. So if with the individual account record I noted that I sent it to four of the ten companies, I want it to return four responses, one for each of the companies I sent it to, with the unique responses (also in a field in the main account database) with the details of the individual record.
       
      I feel like the answer is right there, but I just can't get it to work. Any suggestions would be much appreciated. Thank you.