Creating calculations within a database table

[pease]

Hi all,

The heading probably doesnt make it clear at all what Im after,

I want to setup a database which will allow the user to enter a calculation within a field.

For instance in its simplest form, the table could contain 3 fields

Data1
Data2
Calculation

You could enter

2
3
Data1 + Data2


or
2
3
(Data1 * Data2) + Data 1

Then set up queries to format output giving me

2 + 3 = 5
(2 * 3) + 2 = 8

The database Im looking at will be a fair bit more complicated in the way of calculations but the logic is the same.

Im guessing that what I need to do is replace the likes of Data1 and Data2 with the information pulled from fields Data1 and Data2 and then run the field as a calculation.

Is it as straightforward as that? or do I need to do a lot more in the way of VBA coding to convert what is a text field into a calculation and run it?

One final thing, the overall calculations will involve different data types for instance one calculation could be adding 5 days to date held in data1 while another could be checking that the amount in data1 is less 500.

In an ideal world I would want one table with the data fields and calculation field in rather than one for date calculations, one for money ones etc.

Any help on the general logic behind it would be appreciated.

 

[SkipVought]

Hi,

Strange requirement. What is the business case for this requirement?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[pease]

Basic business case is the production of a large number of test data documents. Currently each year we revise these documents with updated information. The calcuations are generally the same but certain figures need to change such as starting dates for the test, bandwidth levels etc.

One of the documents can be found here as an example >

http://www.hmrc.gov.uk/ebu/testdata-part1-0910.pdf

If you look at page 10 of the document you'll see 6 tests, each of those currently involve us manually calculating both the figures used for Pay and the Expected Results.

eg test 1

there is a bandwidth limit for that particular tax code which this year works out at £39.25, if you earn less than that then you pay nothing, meet it and its £0.20 tax. in that one test there are two calculations, the first to establish the £39.24 (ie 1p below the band limit) and the second calculates the £0.00 expected results.

Id ideally like the test on the database to contain a calculation for the Pay field that (in its simplest form said "Bandwidth([Tax Code]) - 0.01" where Bandwidth() was a Function looking at the Tax Code field and the Expected Results of "Tax([Pay],[Bandwidth])".

I have the functions for these developed, its just getting the database in to allow me to put it all together.

 

[SkipVought]

Why not code various functions, each of which will return the desired result, given the arguments supplied?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[pease]

Im not sure what you mean?

Ive created functions for the likes of calculating tax, bandwidths, National Insurance etc however I now need to store the links to these in a database so that I can run a report which will produce something along the lines of the link I've given you.

 

[SkipVought]

What database?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[pease]

emmm the one this thread is related to.

 

[SkipVought]

Database???

MS Access
SQL Server
Oracle
DB2

What are you referring to, PLEASE?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[pease]

Apologies,

Access

 

[SkipVought]

You can write functions in MS Access VBA that can be used in your queries.

Forum705

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[pease]

Sorry but I mustnt be putting across the query very well.

I have written many a VBA function for this, all of these functions can and are used in Queries.

What I want to do is have a table which stores 500 or so different records, each record will have at least one data value (but usually three or 4) and a calculation attached to that value. In some cases the calculation may reference a function, in fact the best way to describe the calculation would be to imagine each one as a calculation from a Query.

eg

Record 1 may have data values of 95.00, 3 and 20% while the calculation field holds:

([data1] * [data2]) * [data3]

While record 2 could be 15/04/2010 and 5 with the calculation being:

Add_weeks_function([data1],[data2]

The document then would merely display the correct answer in the relevant place. I cant do it as a normal report with subqueries because you would literally have hundreds of queries and updating those would be as much work as we need to do now for the Word document.

Rather than updating, as we do now each and every field where we change the first value, then calculate the 2nd and apply it to the 3rd. We'd simply need to change the 1st value in the database and the rest would be updated.

There are also a number of constant values which can be brought into play eg for all tax calculations, the basic rate is 20%, if that changed next year to 22 then we would just update that once in the db and the whole document would be updated (apologies, Im giving the non-database person explanation there when I dont need to).

The purpose of these sitting in the table will be to produce a report of the type I included. The report would consist of the data values and the relevant results.

 

[SkipVought]

So you're just entering TEXT into this field to represent what the calculation would do. The calculation is NEVER actually performed?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[pease]

yes the calculation would need to be performed.