Calculations Per Row Effect

[rpeare]

Are the escalation and contingency based upon what you choose in your combo boxes?



So, for instance is the escalation a calculation of 5% per year removed from the current year (as your spreadsheet shows)?

If so you do not need to store your escalation or contingency at all, you can display them as values in an unbound field.

You don't actually need a field to store the year since it's a simple calculation but your rate basis is a text value that has a specific value (I'm assuming that does not change) from year to year, for instance if someone selects EST it's always going to be a 120% markup regardless of the year they enter. If that's the case then yes, I would have a table that had the rate basis and the contingency modification (1.2).

Are you doing your data entry on a bound or unbound form? I can't tell from the screen shot.

can you show a screen shot of the table DESIGN that's storing the information.

Does each estimate *always* have each of these five categories? or can it have a varying number of these line items and possibly other ones as well?

Let's say your amount field is fld_amount
your Year field is fld_year
your escalation is fld_escalation
your rate basis is fld_ratebasis
your contingency is fld_contingency

Further let's assume your contingency and escalation are UNBOUND fields

Now let's assume your design engineering is 7331
When you choose your year you want the escalation to automatically calculate an increase of 5% per year removed from the current year. Assuming you are not storing a date anywhere in your data structure that you can use you can, in the ON EXIT of fld_year have something like

fld_escalation = (datepart("yyyy", date) - datepart("yyyy", fld_year) * .05) + 1

if your fld_ratebasis is based on a table you can include the PK (autonumber field I'm assuming) the rate basis description (EST) and the percentage that description represents (1.2)

in the ON EXIT of fld_ratebasis you would have something like

fld_contingency = fld_ratebasis.column(2)

Keep in mind that the first column of any list box or combo box is column 0

so column 0 would be your PK, column 1 would be your rate basis description (EST) and column 2 would be the percentage that represents (1.2)

Then I'd just make sure the escalation and contingency are locked so they can not be changed and rely totally on the data entry of year and rate basis.

[gatsby]

yes, excalation and contingency are based on what the user choose from the comboboxes, and they are fixed, i mean per year it has a fixed percentage or value (2009 = 120% ; 2010 = 115% ; and so on)

screen shots provided (Table and Query)

most of the estimates (7 out of 10) have that 5 categories ; others doesnt need those percentage increase...
yes, data entry are unbound form.

my problem now is that would it have problems on the Amount (Design Engineering) because it get its values from another table, while these Estimates get their values from other table?
here's the code i placed in the unbound box (Design Engineering - Amount): =CLng([SearchResults].[column](24)) since it depends on what equipment or material a user choose also. every equipment or material has different values that could increase its price per year. like 7,331 (for example only; after a series of calculation with its weight, quantity, etc.) is for door knob only, nails have different price.



okay, let me try this first before i ask questions and let you know the outcome.
Thanks.

[rzw0wr]

I think I was able to convert this db to 2003.

try and see.

BTW, Nice looking database

iCoster DBS - Integration2333333.zip


Dale

[gatsby]

I think I was able to convert this db to 2003.

try and see.

BTW, Nice looking database

iCoster DBS - Integration2333333.zip


Dale

Thank you so much Dale (rzw0wr) for converting it.

[rzw0wr]

My pleasure.

Gave me something to do.

I did not understand it.

Dale

[gatsby]

My pleasure.

Gave me something to do.

I did not understand it.

Dale

Thanks Dale.
Okay, as you can see in the Tab Control, "Procurement" tab shows every information per material or equipment (some with calculations - multiplied with weight, quantity, etc etc). On "High Level Cost Estimate" tab its a different calculation. This is more advance. it deals with additional expenses or pricing per equipment.

In the High Level Cost Estimate, what i wanted to do is to change the Amount of Design Engineering (unbound box - which is blank here but in my latest database it has values already depending on the equipment choose by the user) every Year. Every Year has its corresponding percent of increase which is named as Escalation. If user will click:

2009 amount will be multiplied by 120% increase
2010 amount will be multiplied by 115% increase
and so on....

these multipliers or percentages are fixed:
2009 = 120%
2010 = 115%
2011 = 110%
2012 = 105%
2013 = 100%

the same goes to Rate Basis. If user will click:

ALL amount will be multiplied by 120% increase
CUR amount will be multiplies by 115% increase
... the number or amount multiplied here is the Design Engineering amount (with or without an increase from Escalation).

For example:
if the user will click Year 2011 and the amount (Design Engineering) is 7,331, the amount should change to 8,064.1 since it has a 110% increase... if the user will choose EST for Rate Basis also, the amount 8,064.1 (after an increase of 110% in Year 2011) will change to 9,676.92 because there is an increase of 120% after choosing EST for Rate Basis. and this all happens in this row only. Another calculation for the next facility which is Project Mngt O/H (Owner PMT) which has a calculation of [Unit Weight] * 1700 * [Escalation] * [Contingency].... and so on....


i attached also an Excel file to explain further. i got it on Excel (calculations and everything) but i cant do it in Access.

[rpeare]

I'm looking at your table

Table1

It has a list of years and a percent associated with that year and a list of rate basis field and a percentage relating to each of those.

The way you have this set up implies that there can only be one rate basis for any given year i.e. the CUR rate basis relates only to CY/FY 2009. Which I don't think is what you intend?

Secondly what happens with 2014 rolls around, are you going to have to modify all the year percentages? It was my understanding based on your posts that there is an increase of 5% per year from the year of the initial estimate.

so in 2014, 2013 would be 105%, 2012 would be 110% where in the current year 2012 would be 105% and 2011 would be 110%. If your estimate is based on 5% per year removed from the CURRENT year you do not need a table for the escalation.

Secondly, if your rate basis is NOT tied directly to the FY/CY of your estimate you want a separate table for the variables. Do not store them in the same table as your FY/CY list (especially if my previous assumption is correct you don't need a cy/fy list at all).

For instance you have a 'date added' field in your table tbl_description which seems to be what is driving everything on this form. If that were populated you could base the everything on the year of the date added.

The second part of this is that you are referencing *1* field in your tbl_description for both escalation and rate basis because your table is not normalized.

What typically happens in a po/estimating arrangement is that you have *1* record in a table (Tbl_PurchaseOrders for example) for the main information (customer, date, etc) and a second table (Tbl_PurchaseOrderDetail for example) for the line by line information, in this case it would be the possible estimate categories you have.

So you have two options,

1. Spend some time to properly normalize your table (just looking at it and having no clue as to your business model/quoting procedures I can't advise you on this part)
2. Put in field for every category that may have a escalation and rate basis percentage so instead of having one field labeled escalation you'd have escalation_PE (procurement estimate), escalation_DE (design engineering), etc and do the same thing for your rate basis information you do not need to store BOTH the year AND the percentage (for the escalation) you'd just need the year (again assuming it's 5% per year from the year of the estimate you don't need a table and can rely on calculations at run time of any query/report) and for the rate estimate you'd just need a separate table with a PK (autonumber) the rate basis (CUR, ALL, PO, etc) and the percentage relating to that code. Then in your ratebasis_PE you'd store the PK of rate basis for that item.

[gatsby]

i tried all of your suggestions but still i cant make it work. i "even" tried to make the Year into a value list, i tried the ON EXIT on the Event Property Sheet, and others.

i dont have an idea how this calculation will change the Design Engineering Amount by choosing Escalation and Contingency:

fld_escalation = (datepart("yyyy", date) - datepart("yyyy", fld_year) * .05) + 1

Design Engineering textbox (unbound) has this function in Control Source: =[SearchResults].[column](24) ; that the Design Engineering Amount will depend also from the searched item. So, whenever i do your suggestions, the Amount didnt change, it remains on its original value.

i know you have ideas but my mind cant catch up, sorry, because i am a newbie with Access... but still im working on this!

[rpeare]

Ok I am trying to work on your database but it's a bit of a mess so I have some questions and comments before we go any further.

1. your table tblDescriptions appears to be a list of unique parts. Some of the items have identical descriptions but the weight, bore or material may differ. So the biggest question is... why are you trying to perform estimates on a table that is a part list? And perhaps more importantly HOW does your estimating process work. Does a customer call in say I need (for instance) 5 90 degree elbow butterfly valves and your GOAL is to perform an estimate of how much it would cost for all five items at one time? Please be as clear as possible in your description of HOW your process works. Particularly if your estimates may include multiple part numbers for the same order. For instance, if you have an order for multiple parts does the SAME year apply to ALL parts within that order or do you selectively choose a year and rate basis for each part within the order? Remember I know nothing about your business so describe your process as though you are teaching someone how to estimate for your business, because you are!

It looks to me like EVERYTHING is based on the quantity, procurement estimate, year and rate basis. All other values are calculated with these as a root.

For instance you have these in your query Qry_SearchAll which is based on your tblDescriptions:

Procurement Estimate
Design Engineering: Design Engineering: [Unit Weight]*850*[Escalation Field]*[Contingency Field]
Owner PMT: [Unit Weight]*1700*[Escalation Field]*[Contingency Field]
Freight Charges: [Procurement Estimate]*0.05
Onshore Fab and Ins: [AVE USD/MT]*[Unit Weight]*[Escalation Field]*[Contingency Field]
Temporary Work: ([Unit Weight]*180)+([Unit Weight]*0.1)*1440*[Escalation Field]*[Contingency Field]
Inshore Fab and Trans: [Unit Weight]*1.1*2000*[Escalation Field]*[Contingency Field] (is this supposed to be offshore or does inshore mean something else because your query says inshore but your form says offshore)
PC Hook and Com: [Unit Weight]*1000*[Escalation Field]*[Contingency Field]

What is the [AVE USD/MT] field meant to represent (special character in this table field name should be removed)? It's a null value for most of your table.

2. You have a bunch of field names with spaces in them, I would remove all spaces and, where necessary replace any spaces with _ (underscore). Having spaces in object names (table names, field names, query names, etc) will really make things more difficult for you in the long run
3. You also have special characters (%) being one of them. Do not use any special characters other than _ (underscore) in any of your object names either for the same reason you should not use spaces.
4. Do not store your rate basis percentages as text, store them as number (double) with a format of percent and 0 decimal places otherwise you're going to have to constantly perform an operation on the text value to convert it to a number value whenever you do a calculation
5. What is your subform supposed to be doing? There is no link to the main table and it references your tblDescription table.
6. You should have a table that stores the constants (850, 1700, .05, .1, 1440, 1.1, 2000, 1000) in a table for any given year I assume these numbers apply to any estimate generated in CY 2013, but they may change over time so you'd want to be able to modify those numbers without having to modify all your queries, reports and forms along with that change.
7. What pieces of information in the table tblDescriptions apply ONLY TO THE PART and have absolutely NOTHING to do with the estimating process?
8. In your tblDescriptions your Aseembly Combined field (misspelled assembly?) should be storing the PK of the value from your table3 NOT the text value of the description. If your description changes for any reason you will orphan a lot of records.
9. Similarly you do not need to store anything but the EstimationClassID from in your tblDescriptions (assuming this does not affect your estimating process but is there for a visual queue?) and the EstimationClassID should be an autonumber field, not a text field.
10. I haven't tracked down what your table 'Item Name' is doing yet, but it just appears to be a list of descriptions that I can not correlate to anything in your tblDescriptions table, what is this doing and why do you have multiple descriptions that are identical in it?

[rpeare]

I just thought of one more thing.

Is your ITEM NAME table supposed to be a list of 'finished' (assembled) items and you want to correlate the parts that go into making that 'finished' item where the tblDescription is holding the component parts of the 'finished' product?

[gatsby]

1. most of the data here are historical. it was used in the previous projects. there are new projects that are still on going but the data arent ready yet. so most likely, this database is some sort of a reference not an order or inventory based database. its not a database that will order 35 pieces and will calculate the total amount for 35 pieces or will give receipt or add to the inventory of sales. the database is a reference only and will generate report per material or per assembly or whatever information you want as long as it is in the scope of the database. like for instance, an engineer wanted to know the price of a certain material then he/she will search and the database will give him/her information that include estimates. the quantity as you can see is always 1. If ever an engineer wants to know 5 different materials with different years so what he must do is to generate a report one by one or per year because prices differ and may increase as years go by.

the reason why i perform estimates on a table that is a part list because whenever i used tblDescription for the estimates it will give me lots of spaces in the combobox. like for example Year, it only has 6 values, if i will make tblDescription as the Record Source it will create lots of blank of spaces or List Rows (even if i change it to 6) whenever combobox is pressed because other fields have more than 6 values (mostly, it will reach more tha 5,000+). picture is attached to make it much clearer, im sorry because English is not my first language.

- yes, most likely weight, quantity, procurement estimate, year, and rate basis are involved in calculations.

- inshore and offshore were corrected already in my recent database. [AVE USD/MT] is CAPEX Cost Estimare (PDV)/Weight, its not in there because i attached part of the database only. i did not include everything. my point in posting this issue is to know the process on how this estimate calculations work, if i will know how to operate even 1 row i believe i can do it on the other rows.

2. okay do what you think is right, you are the expert and thanks for the advice.

3. okay noted.

4. okay noted.

5. just dont mind the subform. i found solution to that one already. its about adding or saving new record/material. main concern is the Tab Control where estimates lies.

6. okay noted. the problem with me is that im having a problem if i am putting too much Tables in the database. im sort of lost if i will have lots of Tables, Queries. thats the reason why everything is messed up because everything is compressed in 1-2 Tables only.

7. Estimating Process includes Unit Weight, Contingency Field, Year, Procurement, Rate Basis, the rest has nothing to do with the Estimates like Discipline, Generic, Project Reference, Currency, etc.....

8. it should be Assembly, okay noted on that one. how to do this storing PK value issue?

9. i dont have problems with estimation class id or the one in the lower part - Classification and Accuracy and 50/50 CAPEX Estimate (MOV).

10. disregard Item Name Table. i forgot to delete that one. it has nothing to do with the database.


rpeare, thank you for taking your time with me here. i really do appreciate your help. if only were near, i will invite you to my house and prepare snacks or dinner as we work together on this one, hehe. looking forward on your next reply.

[rpeare]

So if I understand your position, you don't care about saving an estimate, you don't care about being able to recall it, you don't even care who or when performed the estimate.

In other words you don't really care about saving data at all. So you don't need to store the information on a table. Period.

So on to the next point.

The root values (that are not calculated) for your formulas are:

Quantity
Unit Weight
Unit Rate
Escalation (based on the formula 5% per year removed from the current year)
Contingency (based on the choice of rate basis)
AVE USD/MT
Contingency Applied (%)


THESE are your calculated values (associated with the estimate tab you're talking about)

Please check the parentheticals on all the formulas:

Base Cost: [Quantity]+[Unit Weight]*[Unit Rate]*[Escalation] THIS IS HOW IT SHOWS
Base Cost: [Quantity]+([Unit Weight]*([Unit Rate]*[Escalation])) THIS IS HOW IT IS BEING CALCULATED

Contingency Amount: [Base Cost]*[Contingency Applied (%)]
Procurement Estimate: [Base Cost]+[Contingency Amount]

Design Engineering: [Unit Weight]*850*[Escalation Field]*[Contingency Field]
Owner PMT: [Unit Weight]*1700*[Escalation Field]*[Contingency Field]
Freight Charges: [Procurement Estimate]*0.05
Onshore Fab and Ins: [AVE USD/MT]*[Unit Weight]*[Escalation Field]*[Contingency Field]
Temporary Work: ([Unit Weight]*180)+([Unit Weight]*0.1)*1440*[Escalation Field]*[Contingency Field]
Inshore Fab and Trans: [Unit Weight]*1.1*2000*[Escalation Field]*[Contingency Field] (is this supposed to be offshore or does inshore mean something else because your query says inshore but your form says offshore)
PC Hook and Com: [Unit Weight]*1000*[Escalation Field]*[Contingency Field]

So...
Your data entry points are going to be the Escalation YEAR, and the rate basis for each one of the groups. IN other words you want to be able to enter a different escalation and rate basis for each one of these:

Procurement Estimate
Owner PMT
Freight Charges
Onshore Fab and Ins
Temporary Work
Offshore Fab and Trans
PC Hook and Com

The only other thing that you might need to enter is the quantity, but you said that's always 1.

There is a inconsistency in your formula though if you intend to enter an escalation (year which determines escalation) for each group because your BASE cost which drives your PROCUREMENT ESTIMATE also uses an escalation:

Base Cost: [Quantity]+[Unit Weight]*[Unit Rate]*[Escalation]

this is your current formula and when it's being calculated no escalation would have been entered yet.

So the question is do you also have an escalation/rate basis for the part itself and not for the other portions of the estimate (procurement estimate, owner pmt etc) because if you do that you're applying 2 escalations by the time you figure all of your procurement estimate calculations.

[gatsby]

So if I understand your position, you don't care about saving an estimate, you don't care about being able to recall it, you don't even care who or when performed the estimate.

In other words you don't really care about saving data at all. So you don't need to store the information on a table. Period. .

- most likely yes. it all depends on the calculation escpecially on the Year (for Escalation) and Rate Basis (for Contingency). different Year and Rate Basis give different result of estimates. i got your point in not storing information in the Table so most likely its a calculation/code that should be placed in either Control Source or Event on Property Sheet but i dont know what calculation or code to put. i tried your calculations/codes but it didnt work. maybe it will but the problem is, you disregard that Design Engineering Amount box (unbound) has its codes/function assigned in Control Source: =[SearchResults].[column](24) ; which will give result to whatever keyterm a user will enter.

So on to the next point.

The root values (that are not calculated) for your formulas are:

Quantity
Unit Weight
Unit Rate
Escalation (based on the formula 5% per year removed from the current year)
Contingency (based on the choice of rate basis)
AVE USD/MT
Contingency Applied (%)


THESE are your calculated values (associated with the estimate tab you're talking about)

Please check the parentheticals on all the formulas:

Base Cost: [Quantity]+[Unit Weight]*[Unit Rate]*[Escalation] THIS IS HOW IT SHOWS
Base Cost: [Quantity]+([Unit Weight]*([Unit Rate]*[Escalation])) THIS IS HOW IT IS BEING CALCULATED

Contingency Amount: [Base Cost]*[Contingency Applied (%)]
Procurement Estimate: [Base Cost]+[Contingency Amount]

Design Engineering: [Unit Weight]*850*[Escalation Field]*[Contingency Field]
Owner PMT: [Unit Weight]*1700*[Escalation Field]*[Contingency Field]
Freight Charges: [Procurement Estimate]*0.05
Onshore Fab and Ins: [AVE USD/MT]*[Unit Weight]*[Escalation Field]*[Contingency Field]
Temporary Work: ([Unit Weight]*180)+([Unit Weight]*0.1)*1440*[Escalation Field]*[Contingency Field]
Inshore Fab and Trans: [Unit Weight]*1.1*2000*[Escalation Field]*[Contingency Field] (is this supposed to be offshore or does inshore mean something else because your query says inshore but your form says offshore)
PC Hook and Com: [Unit Weight]*1000*[Escalation Field]*[Contingency Field]

So...
Your data entry points are going to be the Escalation YEAR, and the rate basis for each one of the groups. IN other words you want to be able to enter a different escalation and rate basis for each one of these:

Procurement Estimate
Owner PMT
Freight Charges
Onshore Fab and Ins
Temporary Work
Offshore Fab and Trans
PC Hook and Com

The only other thing that you might need to enter is the quantity, but you said that's always 1.

- that is correct, i want to enter different escalation and rate basis for all the Cost Estimate Facilities.

- yes, quantity is always 1.

There is a inconsistency in your formula though if you intend to enter an escalation (year which determines escalation) for each group because your BASE cost which drives your PROCUREMENT ESTIMATE also uses an escalation:

Base Cost: [Quantity]+[Unit Weight]*[Unit Rate]*[Escalation]

this is your current formula and when it's being calculated no escalation would have been entered yet.

So the question is do you also have an escalation/rate basis for the part itself and not for the other portions of the estimate (procurement estimate, owner pmt etc) because if you do that you're applying 2 escalations by the time you figure all of your procurement estimate calculations.

- in Procurement tab (not Estimate tab) the Year there is named Reference Year, and the Escalation is named Escalation only while in Estimates tab the Year is named Year Field and Escalation is Escalation Field. it has different names. in the Procurement tab, your calculation is being applied: Escalation: 2012-[Reference Year]*0.05+1 ; but in the Estimates it isn't working because of the Design Engineering Amount box (unbound) codes/function assigned in Control Source.

[rpeare]

iCoster DBS - Modified.zip

Here's a modified version of your database. I've removed all non-essential tables etc.

Your list box should only list your parts, you can take out any calculations in the query.

All calculations are now done on the form based on the BASE YEAR you enter on the first tab (also carried over to the second tab for a visual reference)

All calculations follow the formulas in your original query (though again you will likely want to consider making these variables available on a table instead of hard coded into your forms/reports)

rp

[gatsby]

rpeare, you are the best. thank you so much. God bless you for helping others.

will transfer these changes to my latest database. so far, what you did is okay and hoping no errors will occur when i transfer these. will let you know.

again, thank you !!!