# How To Subtract Values In A Query

Sep 10, 2004

I have a table that has two fields; odometer reading and reading date. I want to able input the reading dates along with the odometer readings. Then I want my query to calculate the milage difference between each date.

Example:
9/1/04 35000
10/1/04 40000 5000
11/1/04 42000 2000

And so on.............

## Subtract Query?

Jul 30, 2007

If there is such an animal...

My employer uses a third party employee tracking system and I do back end work to massage the raw data for ad hoc reports using tables created by this third party software.

Is there a simple means to have a query compare Table A and Table B where Table A is a list of all jobs in a company - both filled and vacant - and Table B is a list of all employees in the company? The table containing jobs data has no field designating whether a job is filled or not. The only way to determine vacancies is to compare the two tables and select only those job numbers where there is no matching employee.

So, using examples from an earlier question - one that KeithG answered perfectly - let me see if I can demonstrate what I'm trying to do...

The Jobs table has two fields; JobNum and JobDesc.
The Employee table has three fields; EmpNum, EmpName, JobNum.

Here's Table A, Jobs;

100, Boss
105, Aide
200, Manager
201, Worker
202, Worker

Here's Table B, Employees;

111, Montgomery Burns, 100
112, Waylon Smithers, 105
121, Homer Simpson, 201
122, Lenny Leonard, 202

Jobnum is the link between the two tables in the query and the query's fields are;

JobNum and JobDesc

The result I need is;

200, Manager

The only vacant job.

So, how do I do a subtract query, if that's even a proper term? How do I have a result that is all of Table A that does not have a match in Table B?

Thanx!

## Subtract 7 Hours From The Query

Apr 19, 2007

4/11/2007 1:01:28 AM
4/12/2007 2:02:52 AM
4/13/2007 4:21:30 AM
4/14/2007 2:22:21 AM
4/15/2007 3:34:53 AM
4/16/2007 4:02:37 AM
4/17/2007 5:03:15 AM
4/18/2007 7:17:34 AM

I need helps again
How can I subtract 7 hour from the query

Thanks

## Queries :: Subtract Two Columns Within A Query

Jul 22, 2014

I have two calculated columns in my query for example... table.num1 - table.num2 as "Col1" and table.num1 - table.num3 as "Col2"

I then want another column equal to:
Col1 - Col2
I know that I could say:
(table.num1 - table.num2) - (table.num1 - table.num3)

but I am wondering if there is an easy way to just take two calculated columns of the query to use in the calculation of another column.

doing something like:
Col3: Col1 - Col2
doesnt work because it doesnt see Col1 and Col2 within the tables.

## Query Aggregate Value Won't Subtract From Form Total

Nov 7, 2006

Here's a sticky one? (maybe...) I have a number of aggregate totals from several queries all linked to the Input Query and all Nz'd as 0's

From the image enclosed the totaling text box (top right left) is calculating the value in the properties box. The value should be 3.52 + 5 - 8 = 0.52 the value showing is -4.475 (which is 3.52 - 8) so I see it's ignoring the 5 value but I can't see why.

The ACDays and ALDays values are both generated by the same module, but with different Leave types. and as I mentioned previously all Nz 0'd

Any thoughts appreciated, I'm baffled:confused:

## Queries :: If 0 From Subtract In Query Then Goto To Previous Record

Oct 5, 2014

I want substract from 9/20/2014 totreg40 - reg = ovt but when reg=0 the remainder subtract from 9/19/2014 (previous record) totreg40 - reg = ovt, etc. The sum of reg = 40.

IDFECHA REGOVTTOTREG40
2309/15/2014 5.5 0 46.75
2319/16/2014 8 0 46.75
2329/17/2014 8 0 46.75
2349/18/2014 7.75 0 46.75
2979/19/2014 8 0 46.75
2359/19/2014 8 0 46.75
2339/20/2014 1.5 0 46.75

I want the result like this:

IDFECHA REG OVT TOTREG40
2309/15/2014 5.5 0 46.75
2319/16/2014 8 0 46.75
2329/17/2014 8 0 46.75
2349/18/2014 7.75 0 46.75
2979/19/2014 8 0 46.75
2359/19/2014 2.75 5.25 46.75
2339/20/2014 0 1.5 46.75

Aug 16, 2007

Hi

I want to make a button which when clicked will bring up a box which the user will enter a number which will then be subtracted from a number in a field.

I have tried an update query however not been able to getting it working.

Any ideas?

## Subtract

May 20, 2006

Hi,
I have a query with two fields. One has the "MEASUREMENT" and the second one is the month containing the values. In the MEASUREMENTs I have gross revenues and discounts, how can I create a new MEASUREMENT (net revenue) that is calculated by subtracting the discounts from the gross revenue.

Regards
Giannis

## Subtract One Table From Another?

Aug 11, 2006

Hi, I hope someone can help me out with this problem:

I have 2 tables with identical stucture, and no unique ID number.
Both had identical data, and the databse was then copied onto 2 laptops. Now further data was added onto each.

I want to now merge the three databses (ie original one, and 2 laptops) into 1, without creating duplicates- so I need to identify which are the new fields added to the databases. I figured that by somehow subtracting the original table from the new ones, i would be left with the new data, but dont know how to do this. Or is their another way?

## Subtract Previous Value From Next Value

Feb 16, 2007

:confused: Need help building a query that will subtract the previous value from the next value at each change in date

Date Tier 1
1/6/2006 4.22
1/13/2006 4.27
1/20/2006 4.27
1/27/2006 4.314
2/3/2006 4.314

for example, 4.22-4.27 = -0.05

## Different Add/subtract Value Each Month

Jan 14, 2008

I have a report based on a query where I get results sorted by month after i have chosen 'from date' and 'to date' in the beginning. I also want a set number for each month to be subtracted from the total sum, and then added to the next month (even if it is a negative number). Ex. Total in january is 100, then it is supposed to subtract 50 from that number, report the result of 50 in one own field at the end of january, then send this to february where the total sum is ex. 200, then the total sum for february is supposed to be 250, and then again a different number, like 60 is automaticly subtracted from the total sum and displayed in one field as the result 190. And so on.. Does anyone know if this is possible, and how? I am a noob, both in access and in the english language :P

## Queries :: Query Records With Both Duplicate Values And Different Values?

Jun 18, 2013

I'm trying to determine the SQL to return only those records in a table which have duplicate values in each of two fields, but different values in a third field. Here's an example:

Code:

AcctNum FoodType FoodClass
------- -------- ---------
A123 Apple Fruit
A123 Apple Fruit
A123 Grape Fruit
A456 Potato Vegetable
A456 Potato Perishable
A789 Carrot Vegetable
A001 Banana Fruit

For the above table, I'm trying to return records which have multiple entries for AcctNum + FoodType, but DIFFERENT values for FoodClass. So for the above table, the query would return:

Code:

AcctNum FoodType FoodClass
------- -------- ---------
A456 Potato Vegetable
A456 Potato Perishable

It returns these two records because there is more than one record with for the AcctNum + FoodType (i.e. 'A456' + 'Potato'), but DIFFERENT values for FoodClass (i.e. one record has 'Vegetable' while the other has 'Perishable').

## IIF Number Exists, Then Subtract

Mar 12, 2007

(hard being a rookie)

On my Form, I want to have a query look at one specific field for a number (1). If the number is found then I would like to have the query do basic subtraction on two other fields. (one from each table)

What I have so far is one unbound text box with the following,
=IIf(tblDaStrategy!DaNumber=1,(tblDaStrategy!DaTar get-tblDaReport!DaActual1))

Simply put, if one field has the number one in it, then take the other two fields and subtract (ex. 100-75) to display 25.

It's not working. I keep getting the #Name?

Update: I think I got it. I went back to square one and made a master query that contains the data from both tables. Then I bound one of the forms to the query. I updated my IIF statement and poof. It seems to be working. Now, where's the Asprin and my pillow?

Update 2: Bummer, now my magical 25 is in place but now my form is locked tight. Guess I need to consider abandoning this concept and simply producing a report with the 25 on it. Unless someone out there has a better idea.

## Subtract Day From User Input

Oct 31, 2007

Hi,
Working on a query, what I have is a user will select a date on a form and hit submit.

I need the query that it runs, to pull all data -1 day from what they select.

So if they select 10/31 on the form, it needs to pull everything for 10/30 only.

I have this in my query now,
[Forms]![frm_change_report]![txt_Date]

Which pulls the data for the date they selected, but not sure how to subtract a day.

## Subtract TIME From DATETIME

Mar 7, 2008

I'm sure this is simple but my brain is mushy this morning!

I have two datetime fields ADMISSION and DISCHARGE with values such as 03/02/2008 15:00. How do I convert these to dates only? I don't want to just *format* them as dates, which I can do. In this example I would want to convert that date to 03/02/2008 (or 03/02/2008 00:00)

It's an idiosyncrasy of the way the NHS calculates hospital length of stay. Our previous IT system had dates and times in separate fields so I could just ignore the time field and work out length of stay based on dates only. However, our new system returns a combined datetime.

Patient A 03/02/2008 00:15 to 03/02/2008 23:45 = 0 days
but
Patient B 03/02/2008 23:45 to 04/02/2008 00:15 = 1 day

Basically, you count a day if there's a date change, even if that was only half an hour (or even a few minutes) either side of midnight, but you DON'T count a day if there was no date change, even if that was almost 24 hours.

So formatting them as dd/mm/yyyy wont work ,as I will still have partial days when I subtract one from the other and INT([DISCHARGE]-[ADMISSION]) wont always work either as it would calculate 0 for patient B.

Help!

## Queries :: How To Subtract Data In One Row

Mar 29, 2013

I having a problem with Access

I have one row in table, for example:

km_counter
18000
18100
18300
18800

and when I run a query, I want to get:

km_counter km_diff
18000
18100_____ 100
18300_____ 200
18800_____ 500

is it possible to do that?

## Queries :: Subtract Two Dates

Mar 6, 2014

I have two date fields. I want to subtract them to determine the number of days elapsed. The dates are in the following format: Dateserial (Year, Month, Day). When I subtract them I get 7670 as the result when the number should be 365 if they are a year apart. How can I subtract the dates in a query?

## Subtract From One Record And Add To Another In Access

Jul 11, 2012

In my database I have a list of organizations with a certain goal. If that organization can not hit that goal we have to "reallocate" the left over goal to another organization. As it stands now I have the following fields, Goal (which should stay stagnant), change (to either subtract from one organization and add to another) and New. What I would like to do is have a way to have a way to calculate if there is a change to maybe have a drop drown that when the change is subtracted I can add that to "new" of another organization.

## Forms :: Subtract Value From Combobox Depend On Value In Other?

Feb 22, 2014

i want to substract value in combobox, but the source of the other combobox is in other table then Form query shows. How can i do that?

## Queries :: Subtract 1 From Entered Parameter

Jul 23, 2014

ACCESS 2007

I've got a table in which some records are associated with a number (numeric field): 691, 692, 693 etc. My parameter query asks for the number I'm looking for (parameter) and should return all records associated with that number or with that number minus one.

Something like this:
enter parameter
find records

in which number = parameter
OR
in which number = (parameter - 1)

How can I do that?

## Queries :: Add Or Subtract One Day If The Date Is Sunday

Jul 14, 2014

I have a table contains
1. full name
2. email
3. date

I want to make a query that will contain
1. full name
2. email
3. date1
4. date2

The date2 will be date1 + 90 days

So if the result is a sunday i want to add one day or subtract one day automatically...

## Queries :: Subtract 1 Month From A Date

Mar 25, 2015

My [datefield1] is formatted as dd/mm/yyyy

I wish to create a query that produces a return of the previous month and year. i.e.

25/03/2015 (though a query formula) becomes Feb-15
01/01/2000 would return DEC-99
etc

This way I can then link all of my February data (formatted "mmm-yy" to my [datefield1]

I have tried subtracting day and formatting to months and subtracting then the value of a month but it all becomes too complicated because of the different days in the months and Jan to Dec and year as well.

## Command Button To Subtract 1 From A Field Value?

Apr 21, 2012

I got a form with a field called [copies on hand] so i made a command button and when i click the button, i want the value in the field [copies on hand] to be subtracted by one.....

The code i have thats not working is

Private Sub Command 25_Click()
If [copies_on_hand] >0 Then [copies_on_hand] = ([copies_on_hand]-1)
End Sub

## I'm Very New To Access & Need To Figure Out How To Subtract One Field Form Another Wi

Apr 11, 2005

Hi,
I am very new to access. I have designed a db for my employer, but I can't figure out how to subtract one field from another without having it subtract fom all others.
My employer is a counselor and wants to know how many units are left per client. There are several diffent services available per client. (88K, 86H, 81H, ect) For instance I tried to use a query, but when it was subtracting
(number of units)-(number of used) It subtracted from all the different service types.
Then I tried to do it on the form and all I get is #Name?
I can email you a copy of the database with a mock client if i'm not explaing myself enough. I know it's probably something very simple and I would appreciate any help. Also, if you know of any good books or websites where I can learn more I'd appreciate the info. I'm learning from Access 2002 Bible.

## Forms :: Subtract Value In Table Using Textfield And Button

May 22, 2013

I have a table in wich I have stored numbers and i want to subtract them using vba.

example

Table1
ID number
1 20
2 40

textbox(ID):2 texbox(value):5 button(subtract)

So when i click button i want to subtract record with id 2 (40-5=35) and i want the new value to rewrite the previous one in the table.

result:
Table1
ID number
1 20
2 35

I want to know if it's possible to do this in vba.

## Queries :: Subtract Two Fields From Multiple Records

Sep 5, 2014

I have the following source data:

Code:
Item Revision Start End
332 B 9/2/11 9/15/11
332 C 9/21/11 9/22/11
332 E 11/2/12 11/29/12
2A9 A 1/13/13 1/14/13
2A9 C 1/16/13 1/18/13

I'm trying to make a select query to provide the following output (RevisionStart-PreviousRevisionEnd):

Code:
Item Revision Span
332 C 6
332 E 407
2A9 C 2

I've struck out with union queries and aggregating/sorting letters and don't know where else to look. Is there a specific name for this type of operation anyway?