By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,863 Members | 853 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,863 IT Pros & Developers. It's quick & easy.

subtract record value?

P: 7
hi, i have a query from 5 tables, and i want to make one of the field to subtract the value from another field, sample:

field1: field2:
5 2
4 3
3 1
1 1

above is the what iam in at the moment and i wanna do is field1 = field1 - field2

field1: field2:
3 2
1 3
2 1
0 1

so how am i able to do this? using access 2007, and i know you have to input something in the criteria field but i dont know how to use it thanks
Jan 6 '08 #1
Share this Question
Share on Google+
1 Reply

Expert 100+
P: 378
It's not as difficult as you think, in fact you only need 1 line in your Query, unless you have some criteria you havent posted (No negative numbers as results, etc).

In your Query, in the 'Field' row of a new column, put this (change values to match your table & field names where needed):

Result: (([Tablename].[Field1])-([Tablename].[Field2]))

"Result:" is the name of the expression. You can name that whatever you want, but it should be unique from any other field name or expression values being returned in the query.

Replace the 'Tablename' and 'Field1/Field2' values, and you should be good to go.

This can also be dumbed down a bit by removing parenthesis, since we're only using one mathematical operation, like so:
Result: [Tablename].[Field1]-[Tablename].[Field2]

Now if the field names you're using, are unique across all the fields in all 5 tables in your query, you could even get away with just this:
Result: Field1-Field2

Now, if you want to add criteria, for example to return only records where the Result is greater than 0, then simply add in the Criteria: >0
Jan 6 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.