Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Apr 2014
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts

    How to subtract data values from same column in MS Access 2010

    I'm trying to create a new column that calculates the difference between two values in another column. So if I had a column like this:
    4
    5
    7
    the new column would read:
    1
    2

    I'm using the Expression Builder I just don't know how to set it up so it takes values from the same column and not two different ones.

    Thanks so much.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,514
    Thanks
    3
    Thanked 48 Times in 48 Posts
    Is the 4,5,7 sequence in the same record, or are they successive records? If they are in different successive records, then you have a real challenge, and you have to resort to some gymnastics using a self join and have a record that indicates what the exact sequence is. Access isn't like Excel where you can refer to a prior row in a formula.
    Wendell

  3. The Following User Says Thank You to WendellB For This Useful Post:

    Jim456 (2014-04-17)

  4. #3
    New Lounger
    Join Date
    Apr 2014
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Ah, I see. No, the 4,5,7 sequence are not in the same record so I guess I have some work to do.

    Thanks so much, Wendell.

  5. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,514
    Thanks
    3
    Thanked 48 Times in 48 Posts
    Yes, that does get complicated. You probably need to add a sequence field to your table so you can get a predictable order for queries by sorting on that. Then you need to create a query of the table which contains the column and a calculated expression that is one less than the sequence number. You then create a second query which joins the table and the first query using the sequence number from the table and the calculated expression from the query. Then you can calculate the difference between the two column values in successive records. Hope that helps - if you need further help post some sample data in a small database and someone here can give you a hand.
    Wendell

Tags for this Thread

Posting Permissions

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