Best practices for behind-the-scenes calculations of values with easily-changeable variables.

[Enioch]

Greetings, accessfolk!

First post here, and I am an Access newbie (beyond, you know, the basic 'lol make table, lol make form, clicky clicky relationships'), so please tailor your responses accordingly and please don't feel offended if I say something that violates your sensitivities on how a database should be designed

My situation is as follows: I am building a database that will be used in the context of a strategy game, to generate 'costs' for units that the players will be able to design themselves. In a nutshell, players input some values into specific stats for their units, some behind-the-scenes crunchiness takes place and then Access spits out a unit 'cost' that is derived from these calculations.

I have been working with Excel so far, but while I have had no problems with writing the functions and algorithms for doing the calculations, I am not happy with the massive / multiple workbooks that I start getting once more and more units are generated, and I would like to switch to Access, if possible.

The main problem is that I want to translate the basic Excel functionality I currently have into Access without falling into the trap of 'spreadsheet thinking', and I want to make sure that I'm designing the DB in a way that makes sense in the long term. As my experience with Access is relatively lacking, I would like to consult you fine people for as much advice as I can.

Allow me to present you with a scenario:

I give each of my units a basic 'speed', in an integer from 1 to 8. This speed needs to be run through a calculation that involves at least 4 adjustable parameters applied to all units equally (something like cost = A*speed^B / C^D) to get the final 'cost' the unit gets for its speed stat. So, if A = 2, B = 3, C = 4, D = 5, a unit with speed 6 would have a total speed cost of 0.421875.



Now, my questions are two-fold:

1. (most relevant to this sub-forum) - what is the best way to store the variables somewhere that makes them easily accessible (in case I want to change them later and allow for balance tweaking?) And how would I best structure my tables to allow for this type of calculations?

2. (less relevant to the overall theme of the subforum, but it's intrinsically linked to the overall question, so I'm posting it here rather than having to re-explain the entire problem somewhere else) - what are the best practices for generating the results that I want? Where should the calculations be handled and what would be the best option for outputting them in the form (to allow for quick overview) and in a printable report?

I realize that I might not be giving you necessary information, please feel free to query (heh) for any further clarifications you might need.

Many thanks in advance!

[ranman256]

Make a custom function. They can be used in queries or forms:

function calcMyValu ( pvHt , pvWd)
 CalcMyValu = 73 * pvHt * pvWd +1
end function

[CJ_London]

what is the best way to store the variables somewhere that makes them easily accessible (in case I want to change them later and allow for balance tweaking?

difficult to answer other than to say in a table without more detail since table design and their relationships need to reflect 'real life' and you haven't provided that.

You might have a table for each of speed, A, B, C and D or they might be be in one table or they might be in 2 or 3 tables. If your calculations are fixed in terms of how the variables are treated, use a function as suggested by ranman or perhaps store as a string swapping in variable values and use the eval function to evaluate


mystring="A*5/B"
mycalc=eval(replace(replace(mystr,"A",2),"B",1.5))

in the immediate window
?eval(replace(replace("A*5/B","A",2),"B",1.5))
6.6666666666666666666666666667

what are the best practices for generating the results that I want? Where should the calculations be handled and what would be the best option for outputting them in the form (to allow for quick overview) and in a printable report?

in a query - results can be pass to a form or report. Investigate creating UDF's (user defined functions) for use in a query.

[Enioch]

Lovely, definitely something to start working with!

I'll get on it and see if I can get what I need. Many thanks, kind accesspeople.