Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    gatsby is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    114

    Calculations Per Row Effect

    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 * [Escalation] * [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.
    Attached Thumbnails Attached Thumbnails Row.jpg  
    Last edited by gatsby; 03-27-2013 at 12:28 AM. Reason: wrong calculation;

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    In your tables does each cost center (Design engineering, project management, procurement estimate, etc) have a year, escalation, rate basis and contingency associated with it?

    You're not showing your final numbers but you mention a calculation is not performing as it should.

    Are all of your formulas supposed to be AMOUNT * ESCALATION * CONTINGENCY except for design engineering which has an additional constant of 850 included?

    What is the formula for the field that is not showing the correct amount, what is it showing and what should it show?

  3. #3
    gatsby is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    114
    Quote Originally Posted by rpeare View Post
    In your tables does each cost center (Design engineering, project management, procurement estimate, etc) have a year, escalation, rate basis and contingency associated with it?

    You're not showing your final numbers but you mention a calculation is not performing as it should.

    Are all of your formulas supposed to be AMOUNT * ESCALATION * CONTINGENCY except for design engineering which has an additional constant of 850 included?

    What is the formula for the field that is not showing the correct amount, what is it showing and what should it show?

    Year, Escalation, Rate Basis, and Contingency have a constant value, it is applicable to all:

    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



    Design Engineering has a different formula, others have its own formula also. I just made Design Engineering as my sample because if I could get Design Engineering correctly, for sure I will know how to do it for the next part. Yes, it should be AMOUNT * ESCALATION * CONTINGENCY wherein AMOUNT got its value from ([Unit Weight] * 850) and thats for Design Engineering only, others have different calculation.

    Formula for Design Engineering is [Unit Weight] * 850 * [Escalation] * [Contingency], the value should be 7,331 if the user will not choose Year and Rate Basis but when users choose Year the amount will change according to the Year or Rate Basis.

    For example:

    if the user will click Year 2011 and the amount 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....

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    How are you storing your information on the table? is it a normalized structure? Can you provide a sample of your database? just make a copy of your db, put in some fake information and then zip it up and load it here and I can take a look at it.

  5. #5
    gatsby is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    114
    Quote Originally Posted by rpeare View Post
    How are you storing your information on the table? is it a normalized structure? Can you provide a sample of your database? just make a copy of your db, put in some fake information and then zip it up and load it here and I can take a look at it.

    Attached is the zip file of my database... yes, its a normal structure. all data is in the Table, all the calculations are in the Query...

    By the way, once youre in my database, go to "High Level Cost Estimate" tab then you can see what my problem is all about. the Procurement Estimate, Design Engineering, etc etc should change its value once users pick Year and Rate Basis wherein Year has a specific value in combobox, same as the Rate Basis.
    Attached Files Attached Files

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Can you convert the database to something prior to 2010, I don't have access to a version higher than 2007

  7. #7
    gatsby is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    114
    i cant save it to lower version because it says "The specified database sort order, General, is not supported for the target database format. i dont know what that means.

  8. #8
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    If you're using multi value fields or calculated value fields you may be getting this error, it would also indicate you're *not* using a normalized structure that I was talking about.

  9. #9
    gatsby is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    114
    yup i believe so. this dbs involves a lot of calculations.

  10. #10
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    That's likely why you can't convert it, having a TABLE with a calculated field or multi value field is something unique to Access 2010 and I really do not know why they put that in, it is a terrible, terrible practice to use either in a table.

  11. #11
    gatsby is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    114
    all of my calculations are in the Query. no calculations in the Table.

  12. #12
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    If you're using multi value fields or calculated value fields *IN A TABLE* you may be getting this error, it would also indicate you're *not* using a normalized structure that I was talking about.

    I thought it was clear I was talking about a table when I mentioned multi value or calculated fields. At any rate I can not tell you any more than I have without a sample to work with so we are at an impasse.

  13. #13
    gatsby is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    114
    i attached an Excel file on what i wanted to do. in Excel file it is working but i cant do it in Access...

    MI (it means Manual Input)
    AI (it means Automatic Input)


    If you click a specific Year, theres a dropdown beside it. Clicking specific Year will change the Escalation that is assigned to it. if the Year will change, the Escaltion will change as well as the Design Engineering (Amount) will change according to the percent increase. Same process with Rate Basis.
    Attached Files Attached Files

  14. #14
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I can not read office 2010 files. If you convert it to a prior version I can have a look.

  15. #15
    gatsby is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    114
    ops sorry. okay, i converted this one into Excel 97-2003...
    Attached Files Attached Files

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. calculations
    By DariusD in forum Access
    Replies: 3
    Last Post: 01-01-2013, 07:22 PM
  2. Calculations on reports
    By BigMac4 in forum Reports
    Replies: 3
    Last Post: 09-14-2012, 02:33 PM
  3. Replies: 1
    Last Post: 08-13-2012, 01:25 PM
  4. calculations??
    By richrit in forum Access
    Replies: 1
    Last Post: 04-11-2012, 05:06 PM
  5. Rainbow color effect on textbox highlighted text
    By DanielHofer in forum Forms
    Replies: 1
    Last Post: 08-31-2011, 08:14 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums