Complex Calculations in Acess

[jpynn]

Hi,

First for the general...
I have created an acess database and was wondering if there was a place for all the different type of functions you can use in a calculated field?

Now the specifics

Here is a small sample of the table
<table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="100%" id="AutoNumber1">
<tr>
<td width="14%">Primary Element</td>
<td width="14%">Air</td>
<td width="14%">Earth</td>
<td width="14%">Water</td>
<td width="14%">Fire</td>
<td width="15%">Cost</td>
<td width="15%">Adjusted cost</td>
</tr>
<tr>
<td width="14%">Air</td>
<td width="14%">4</td>
<td width="14%">0</td>
<td width="14%">0</td>
<td width="14%">1</td>
<td width="15%">50</td>
<td width="15%">To be calculated</td>
</tr>
<tr>
<td width="14%">Earth</td>
<td width="14%">0</td>
<td width="14%">3</td>
<td width="14%">0</td>
<td width="14%">1</td>
<td width="15%">70</td>
<td width="15%"> </td>
</tr>
<tr>
<td width="14%">Water</td>
<td width="14%">0</td>
<td width="14%">1</td>
<td width="14%">5</td>
<td width="14%">0</td>
<td width="15%">30</td>
<td width="15%"> </td>
</tr>
<tr>
<td width="14%">Air</td>
<td width="14%">2</td>
<td width="14%">0</td>
<td width="14%">0</td>
<td width="14%">0</td>
<td width="15%">100</td>
<td width="15%"> </td>
</tr>
<tr>
<td width="14%">Fire</td>
<td width="14%">0</td>
<td width="14%">0</td>
<td width="14%">0</td>
<td width="14%">6</td>
<td width="15%">40</td>
<td width="15%"> </td>
</tr>
</table>

I have created a form to get the following query criteria from the user

<table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="100%" id="AutoNumber1">
<tr>
<td width="25%">Air</td>
<td width="25%">Earth</td>
<td width="25%">Water</td>
<td width="25%">Fire</td>
</tr>
<tr>
<td width="25%">9</td>
<td width="25%">7</td>
<td width="25%">8</td>
<td width="25%">7</td>
</tr>
</table>

The Calculation for Adjusted cost is Cost * 1/(1+"User Criteria Value of primary"-"Table value of primary")

So for Example the adjusted cost of the first line item would be 50* 1/(1+"9" - "4") = 50*1/5 = 10

Seems like it shouldn't be difficult to do except the "table value of primary" sadly however since I have no idea what functions I can use (the function list in excel has bailed me out a number of time) I am stuck .

 

[mdmilner]

Ignore Functions for right now.
The Math Operators in your pseudocode work.

Try this:
Go into QBE (query by example) - the Wizard interface you probably used to build the queries, and add your formula into the field. You'll have to specify the Fieldname you want to appear when executed, followed by a Colon and then the formula.

I'm not sure I understand which fieldnames you're really using in your formula, so this is probably innaccurate, but you do it like the below. Brackets [] help Access identify text as a Fieldname, but a format like this would work too:

tblname.fieldname

CalcValue: [Cost]* 1/(1+[fldname1] - [fldname2])

This will dynamically populate the CalcValue column with numbers based on the values in other columns for the same record only.

Now, this next part is a small step forward.
Use this new query as the RecordSource to build your Input/Edit form.
All of the fields, including the calculated one will now show up. You'll also find that changes to a field on the form will be reflected in the calculated field.

There *are* more complex ways to do this, which may still be necessary.
Here's hoping that gets you started.

Mike

 

[jpynn]

Thanks,

The solution you have outline works perfectly if the field remains the same for each record.

However each record needs to use different fields based on the record's value of [Primary]

So if the value of [primary] is Air I need to use the User entered value for [air] and the db value for [air].

I need dynamic field names or something

Hope that clarifies my problem a little

 

[mdmilner]

Let me get back to you tomorrow.
I was fiddling with this, and ended up with 3 tables and a form that uses VBA behind a button to calculate a value. Not sure how to simplify this one.

 

[jpynn]

Thanks for your time, I figured it out using two calculated values that containing IIF statements

Thanks for you time!

 

[mdmilner]

Great

I was starting to feel guilty that I didn't finish.
Doing this was very possible, but each idea I had was, in my opinion, more complex than it needed to be. Kept looking for that simple answer and for some reason I didn't get there.

Good Luck!

Mike