U bevindt zich op: Home Activities Call for Data SMB Access

The SMB access software

The CCE created an Access database with dedicated software to apply the Simple Mass Balance (SMB) model.

You can download the Access database version here.
This SMB-Access database has tables that needs converting to the template database as distributed to the National Focal Centres for the call for data 2015/2017. In order to submit data to the CCE, the following steps should be executed after the SMB calculations.
In case the ecosystem records in the SMB-Access database no empirical critical loads:

- Copy the tables “CLacid”, “CLnut”, “ecords” and “SiteInfo” into a new database.
- Rename these tables in the newly made database to “SMBCLacid”, “SMBCLnut”, “SMBecords” and “SMBSiteInfo”
- Import or link these tables to your template database, and execute the queries:

INSERT INTO CLacid SELECT SMBCLacid.* FROM SMBCLacid;
INSERT INTO ecords SELECT SMBCLacid.* FROM SMBecords;
INSERT INTO CLeut ( SiteID, CLeutN, cNacc )
SELECT SMBCLnut.SiteID, SMBCLnut.CLnutN, SMBCLnut.cNacc
FROM SMBCLnut;
UPDATE ecords SET Nmethod = 2 WHERE SiteID IN (
SELECT SiteID FROM SMBCLnut) ;
INSERT INTO SiteInfo ( SiteID, thick, nANCcrit, Cadep, Mgdep, Kdep, Cldep, Cawe, Mgwe, Kwe, Caupt, Mgupt, Kupt, Qle, lgKAlox, cOrgacids, Nimacc, Nupt, fde, Nde, Prec, TempC, CNrat, Measured )
SELECT SMBSiteInfo.SiteID, SMBSiteInfo.thick, SMBSiteInfo.nANCcrit, SMBSiteInfo.Cadep, SMBSiteInfo.Mgdep, SMBSiteInfo.Kdep, SMBSiteInfo.Cldep, SMBSiteInfo.Cawe, SMBSiteInfo.Mgwe, SMBSiteInfo.Kwe, SMBSiteInfo.Caupt, SMBSiteInfo.Mgupt, SMBSiteInfo.Kupt, SMBSiteInfo.Qle, SMBSiteInfo.lgKAlox, SMBSiteInfo.cOrgacids, SMBSiteInfo.Nimacc, SMBSiteInfo.Nupt, SMBSiteInfo.fde, SMBSiteInfo.Nde, SMBSiteInfo.Prec, SMBSiteInfo.TempC, SMBSiteInfo.CNrat, SMBSiteInfo.Measured
FROM SMBSiteInfo;

- Remove the SMB* -tables and compact the database before submitting

In case you have empirical critical loads (partly) for the same ecosystems:

- Insert your empirical critical loads in the template-database, including all ‘ecords’
- Follow the same procedure as above, but before the query for CLeut do:

SELECT SMBCLnut.SiteID, SMBCLnut.CLnutN INTO nutSTemp
FROM CLeut INNER JOIN SMBCLnut ON CLeut.SiteID = SMBCLnut.SiteID;
DELETE FROM SMBCLnut WHERE SMBCLnut.SiteID NOT IN (
SELECT SiteID FROM nutSTemp)
DELETE FROM CLeut WHERE SMBCLnut.SiteID IN (
SELECT SiteID FROM nutSTemp)

Last update 24 October 2016

 

Search:

Service