Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2010
    Posts
    4

    Unanswered: How To Subtract The two Columns

    i am new to microsoft access

    i have made this simple file .

    anyone please help me substract the two columns and show the result in third column

    INCOME-EXPENDITURE=Savings

    I want to use the above formula on SAVINGS Column please help
    Last edited by greensonu; 03-07-10 at 04:58.

  2. #2
    Join Date
    Jun 2009
    Posts
    18
    Quote Originally Posted by greensonu View Post
    i am new to microsoft access

    i have made this simple file .

    anyone please help me substract the two columns and show the result in third column

    INCOME-EXPENDITURE=Savings

    I want to use the above formula on SAVINGS Column please help

    Hi, I'm really not so keen on downloading, but I hope I can help anyway...
    If you're new to Access I think the easiest way is to do a query that calculates for you. A standard (select) query, that will show this column each time you open the query...

    Create a New query in design view. Add your columns to the grid (click and drag, if you're really really new...) and in the field heading for Savings you write like this:

    Savings: [INCOME]-[EXPENDITURE]

    A general tip: The help section of MS Access is a great help (better than most other MS helps I think)... Search for calculate query and you'll find topics of interest!

    Good luck with Access!
    /Nenne

  3. #3
    Join Date
    Mar 2010
    Posts
    4
    Hi, I'm really not so keen on downloading, but I hope I can help anyway...
    If you're new to Access I think the easiest way is to do a query that calculates for you. A standard (select) query, that will show this column each time you open the query...

    Create a New query in design view. Add your columns to the grid (click and drag, if you're really really new...) and in the field heading for Savings you write like this:

    Savings: [INCOME]-[EXPENDITURE]

    A general tip: The help section of MS Access is a great help (better than most other MS helps I think)... Search for calculate query and you'll find topics of interest!

    Good luck with Access!
    /Nenne

    it worked but how to view the results in the table ...and in the form

    i dont want to enter the value manually in savings column so how can it be added automatically

  4. #4
    Join Date
    Jun 2009
    Posts
    18
    Quote Originally Posted by greensonu View Post
    it worked but how to view the results in the table ...and in the form

    i dont want to enter the value manually in savings column so how can it be added automatically
    First the form...
    You should base the form on your query instead of on your table - you should still be able to update the fields and the input will end up in the table - plus you will be able to see the calculated field in the form.
    Change data source in the form's properties to the query, and you will see your calculated field in the field list.

    Using Access you very rarely look at the table directly, but you use forms to present tables to you, and usually you have more than one table and then you use queries to link them together and you base your forms on the queries.

    If you really want to, you can change the query to an update query which will write back information to your table, but then you will have to run it every time you've made changes in the table - the select query is always up to date with the contents of the other fields...

    As you get more in to Access, you will get the idea of relational databases and the importance of not having redundant information etc...

    /Nenne

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Generally speaking, it's not recommended to store a computed value into a table. Create a query on that table and add the computed columns to the query instead. Otherwise, one day or another you'll end up with data inconsistencies in the table.
    Have a nice day!

  6. #6
    Join Date
    Mar 2010
    Posts
    4
    Generally speaking, it's not recommended to store a computed value into a table. Create a query on that table and add the computed columns to the query instead. Otherwise, one day or another you'll end up with data inconsistencies in the table.

    thank you very much i got it

  7. #7
    Join Date
    Mar 2010
    Posts
    4
    one more thing i have these columns total six in number

    Basicpay overtime allowance absence advance netpayable

    i want to calculate aS:- i have tried in different ways but no result ....

    NETPAYABLE: [BASICPAY/30]/8*[ALLOWANCE+OVERTIME]-[ADVANCE+ABSENCE]

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It should be:
    Code:
    NetPayable: (([basicpay]/30)/8)*(([allowance]+[overtime])-([advance]+[absence]))
    The brackets identify the columns, they are not the same as parentheses. Moreover, the expression could be simplified.
    Have a nice day!

  9. #9
    Join Date
    Jul 2012
    Posts
    1
    I have a company with three branches in different states; they produce four products either as final product or input materials, by the end of the year, we should sum what we deliver (D) and then find percentage of each product (e.g. (product (1))/sum (D)), then take these percentage and multiply it with whatever we receive in return (-R-), this will allow us to reward workers. I would like any help how to build a query in ACCESS to perform this calculation.
    YR Branch Product Deal Value
    2006 1 1 D 349.37
    2006 1 4 R 2,350.67
    2006 1 2 D 2,371.96
    2007 1 3 D 1,320.85
    2007 1 1 D 370.68
    2007 1 4 R 6,443.00
    2007 1 2 D 6,370.42
    2008 2 1 D 153.49
    2008 2 4 R 5,905.50
    2008 2 2 D 5,761.65
    2009 2 3 D 276.59
    2009 2 1 D 720.71
    2009 2 4 R 0.00
    2009 2 4 R 3,944.76
    2009 2 2 D 3,322.93
    2010 3 3 D 560.89
    2010 3 1 D 430.74
    2010 3 4 R 2,278.83
    2010 3 2 D 1,677.31
    2011 3 3 D 1,024.65
    2011 3 1 D 926.71
    2011 3 4 R 3,400.51
    2011 3 2 D 315.53

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •