(odd man out) unknown number of variables

[MattThat]

I am creating A DB for a client who runs a large gardening company. I have a smooth set-up for the entities and atributes but am at a loss for one particular part.

A service they offer is removle of top soil/rubble etc and leveling of garden prior to work.

They measure the garden and get a set of figures that useing geomitry can be turned into a volume of soil. This is then applied to some standard formulas, a new soil volume (soil removed) is gained and this is used to calculate the skip space needed.

How can I add this function to the database? I understand that the math can be carried out, but how do I record such a figure that might only be used once in ten times.

This is something that just sticks out and refuses to fit in.

Additionally I do not know if the user will have ALL the variables required and must therefore asume that any one maybe missing.

I see no way of recording this type of equation as an entry, normalised to allow other such equations to be setup by future users....

Lost.

 

[JeremyNYC]

Matt,

Are you saying that the equations may change? If that's the case, then yeah, you're in a bit of a pinch. If you can guarantee that you can get a text version of it that is computable, you could just store it as text and when you need the value you could use eval. I just did this in the immediate window:
?eval("5 * 4&quot
20

You will have to be confident that the strings placed in there will be proper mathematical stuff recognized by Access, and that's hard to do. But with some clever error handling and msgbox text, you could probably prompt users to figure out what the error is, if one comes up.

Jeremy =============
Jeremy Wallace AlphaBet City Dataworks

http://www.AlphaBetCityDataworks.com

See the Developers' section for some helpful fundamentals.

See thread181-473997 for information on how to get the best responses possible here at Tek-Tips.

 

[Darrylles]

Hi Matt,

If this can be calculated manually, then a formula exists that can do it.

You may have 10 different formulas, but the user will understand which formula to use.

I don't understand your question when you say: 'how do I record such a figure that might only be used once in ten times?'.

Are you saying that the different result (from a different calculation) must have it's own field in a table - thereby increasing the number of fields and breaking normalization rules?

Regards,

Darrylle

Are you "Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[MattThat]

JeremyNYC, your idea has some merits.

Darrylles, yeah kind of. The user may or maynot add the result to the estimate (what the DB generates) but some result will need to be stored.

I could, in theory, create a form that is almost an indipendant program that allows the user to fill out the values he's got and then works the rest out.

But as it should be a once in ten thing - storing the result is a bit of a pig. (and then what if the user feels the need to have two sets of values from two seperate calculations?).

I was hoping to normalize the formula and create set of tables that can be used to build additional formulas for functions that have not been thought up yet.

---------------- Help any?

-Matt

 

[beetee]

A possible answer:

save the calculation data as an encoded memo field. This will require little overhead

provide a wizard-like form that the user can interact with to perform the calculations. Any variables and results can be encoded and stored in the memo field. The value of the estimate can be stored in whatever field you use to store values of goods and services.

 

[Darrylles]

Hi Matt,

Sorry, I'm still at a loss as to exactly what the problem really is.
What I THINK is the problem is either one of the following...

1) There are numerous different ways to work out the top-soil removed due to the shape of a garden. So how do you create a generic formula that applies to all cases?

If this is the question, then it really depends on the complexity. There may or may not be a generic solution.
The point that I was making earlier is that there WILL be a finite number of formula's, so you could have 10 different functions, the user understands that each function matches a specific garden type and chooses the relevant function. This displays a form (1 of 10) that contains all relevant fields which MUST be filled in for the formula to work.
If a user doesn't have a value for one of these fields, then they can't even work it out manually either - so what does it matter?

Remember that this can be designed in such a way so that you initially supply 10 solutions (or formula's) and if the user discovers an 11th is needed - you can add it at a later date easily.

Can you supply maybe 2 different formula's that you know about - to give a better idea of the problem?

2)You have a situation where the 'top-soil' problem never occurs - 9 times out of 10 you don't even want to store top-soil volume, and you are thinking that fields will be in a table but left unused (empty) - which is wasteful.

The way to get around this is to have a table which has a 1:many relationship to the main table (1:many really means 0,1 or many records). So:

tblMain                         tblVolume          
pk      autonumber  <--|        pk      (autonumber)
fielda  (whatever)     |        volume  (number)
fieldb  (whatever)     |-->>    main_fk (long)
fieldc  (whatever)

If no volume record is reqd. then none is created.

Can you give an idea of your current tables?

Kind regards,

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk