Dynamic Calculation, Reporting & Data Entry System

I have a database that has four tables that will be used for calculations based on the selected analysis groups in the sample table, one is InternalResults, another is Esdat_Results (query), Another is RawData and another is ScanData.

Basically i want to be able to have a table of analytes, for example this table contains chemicals, and anything that is used to come up with a calculation, and against each entry it asks is it an InternalResult, and ExternalResult, or RawData. and if the result is Reportable

There would be another table which lets the user pick which Analytes are part of the calculation, and enter information about that calculation,, as well as the calculation name and description and the actual calculation, so for example if in the calculations table i had TVOC (Internal), StartDate&Time (RawData), EndDate&Time (RawData) and Flow1 (RawData) and Flow2 (RawData) selected from the Analytes table, and the calculation was = (((TVOC/((StartTime-FinishTime)*1440)*Ave(Flow1,Flow2)))*1000))) it would grab the values from all of the different tables and calculate them and give the final result.

There is another part to this, which is the data entry. Because the RawData table is structured differently, that is instead of having a field for Flow1, Flow 2 and so on, because over time the rawdata requirements will change i wanted to set it up like ID, SampleID (fk), RawDataName, RawDataValue, RawDataEntered. I was hoping someone might be able to come up with a crosstab like form that allows for data entry, and data updating based on what is in the raw data table for the samples that relate to that project, and what analytes (rawdata) are required for the samples that are part of that project (all samples have ProjectID already set).

So for example it goes through and sees that some samples need Flow1,Flow2, StartDate&Time, FinishDate&Time, SurfaceArea and it makes a crosstab like form for that project with the samples down the lefthand column, and at the top row it would have the RawDataName, and if it did not exist in the RawDataTable, when it is entered into the crosstab like form, it would insert a new entry into the RawDataTable for that SampleID with the HeaderName of the column that was being edited or entered. If possible, it would be great if it could read the Analyte table and find out what sort of data it is, and change the format of the text box, so dates & times are properly entered, as are decimal numbers and so on.

The final bit is a dynamic report, that can be generated for the project, which pulls up all of the samples and goes through all of the analytes, and if any analyte is marked as Reportable, it reports the raw data on the report, but then it also shows the calculation result against the sample ID for that analysis.

For example, with the TVOC value in the above example, if StartDate&Time, FinishDate&Time, and TVOC are all reportable, in the results table it would put those values, and at the end the result. It would group by Analysis type, so if there was another different calculation, it would make a new table for that result.

Budget $100 - $150

Last thing i forgot to mention, there is actually three groups, so there would be Analytes, then AnalyteCalcuations and then a Group of AnalyteCalcuations, so i might have 17 Different Analytes that are calculated in a group.

There are a few cases where the calculations reference other calculations, So for example a VOC (g/L) calculation requires Density and VOC to be calculated, and then grabs Water from External Results, and ExemptCompounds from RawData.

Microsoft Access

Projektin tunnus: #906246

Tietoa projektista