Calculations Per Row Effect

davies107

Registered User.
Local time
Yesterday, 18:32
Joined
Oct 2, 2012
Messages
143
Experts of Ms Access I need your help again...

I posted my concern way back in this forum but still I cant get it work. I need your help again.

The problem is that, I wanted to work on changes of calculation per row. I attached a photo to make it much clearer.

In Design Engineering:

If the amount is 7,331 then users pick a Year, lets say 2011, that 7,331 will change and have an increase of 110% based on the change of amount per year. Then again, if the user pick a Rate Basis, lets say EST (Estimated) the amount 8,064.1 (after an increased in picking a Year) will increase again according to the Rate Basis choosen, it will become 9,676.92.

Its like, amount on the left side (Cost Estimate Facilities) will always increase according to what the user pick whether Year or Rate Basis BUT, changes should have an effect only per row not the entire Form because on the next Cost Estimate Facilities - Project Mngt O/H (OWner PMT) should have the same function but it has a different amount than the others.

Design Engineering has its calculation getting its Source from a Query - [Unit Weight] * 850 * [Year] * [Contingency]

All of the data is in a Table, and all calculations are in the Query.

Should I create a new Table for this? Should I create a new Query for this?

What to use in the Form - Subreport? Combobox?

Please help....

Thanks.
 

Attachments

  • Row.jpg
    Row.jpg
    46.9 KB · Views: 105
Last edited:
From the looks of your attachment, I would suggest use combo boxes.

Code:
For your year combobox have the record source as say
 
"SELECT Yr, Escalation From YourTable ORDER By Yr"
and set the column count to 2 and bound column to 1
 
Similarly for contingency combobox have the record source as
 
"SELECT Rate, Contingency From YourRateTable ORDER By Rate"
and set the column count to 2 and bound column to 1
 
Then your calculation becomes
 
[Unit Weight] * 850 * [YrCombo].column(1) * [RateCombo].column(1)

If you are not using the first column in the 2 combo boxes for anything other than to select the right year and rate then you can set the bound column to 2 and your formula becomes

Code:
[Unit Weight] * 850 * [YrCombo] * [RateCombo]
 
From the looks of your attachment, I would suggest use combo boxes.

Code:
For your year combobox have the record source as say
 
"SELECT Yr, Escalation From YourTable ORDER By Yr"
and set the column count to 2 and bound column to 1
 
Similarly for contingency combobox have the record source as
 
"SELECT Rate, Contingency From YourRateTable ORDER By Rate"
and set the column count to 2 and bound column to 1
 
Then your calculation becomes
 
[Unit Weight] * 850 * [YrCombo].column(1) * [RateCombo].column(1)

If you are not using the first column in the 2 combo boxes for anything other than to select the right year and rate then you can set the bound column to 2 and your formula becomes

Code:
[Unit Weight] * 850 * [YrCombo] * [RateCombo]




CJ_London, thanks. sorry but i made a mistake about the calculation its not

[Unit Weight] * 850 * [Year] * [Contingency] BUT IT IS:

[Unit Weight] * 850 * [Escalation] * [Contingency] .....


what im getting right now is the Combobox, its WORKING that whenever I chose a Year the Escalation or Contingency changes to its assigned value like:

Year --- Escalation
2009 --- 1.20
2010 --- 1.15
2011 --- 1.10
2012 --- 1.05
2013 --- 1

Then;

Rate Basis ---- Contingency
CUR ---------- 1
PO ----------- 1.10
BQ ----------- 1.15
EST ---------- 1.20
ALL ---------- 1.25


BUT, i dont get the right calculation for each field like Design Engineering.

my follow up question is how to get values from other Table to be place on Query for calculation? Unit Weight is in a different Table while these data - Year, Escalation, Rate Basis, and Contingency are in a different Table also. I want them to see each other in the Query for the calculation, is that possible?

Like:

Unit Weight (from Table 1) * 850 * Escalation (from Table 2) * Contingency (from Table 2)
 
Assuming your bound column for the year and rate columns is 1 the query is (change names to suit your fields)

Code:
SELECT [Unit Weight] * 850 * form!yourform![YrCombo].column(1) * form!yourform![RateCombo].column(1) FROM Table1

Alternatively assuming your bound column for the year and rate columns is 2 the query is (change names to suit your fields)

Code:
SELECT [Unit Weight] * 850 * form!yourform![YrCombo] * form!yourform![RateCombo] FROM Table1
 
CJ_London, I tried your suggestion but it didnt work. sigh...
 
can you post the code you are using?
 
ops sorry, my bad...

the calculations are in the Query - QRY_SearchAll

Form is - FRM_SearchMulti

Then go to "High Level Cost Estimate" tab....
 
you just need to change the values in tbldescription from text to numeric (double) where required - you may need to restate percentages as 1.15 rather than 115 or dive by 100 in the calculation depending on how you set it up
 
okay i can do that... but how about the flow? i dont know if thats the right word but as you can see the dbs is quite messy when it comes to the calculations. theyre not seeing each other when i tried to pull out values from the table thats why calculations are not working, i know theres a big mistake in what im doing.
 
okay done with your suggestions, replaced percentage with numbers (double) but still i cant get it work.
 

Users who are viewing this thread

Back
Top Bottom