Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS ACCESS & have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for

How To Subtract Values In A Query

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.

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

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

Thanks in Advance - John

View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Subtract Query?
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?


View Replies !   View Related
Subtract 7 Hours From The Query
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


View Replies !   View Related
Query Aggregate Value Won't Subtract From Form Total
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:

View Replies !   View Related
Add And Subtract

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?

View Replies !   View Related
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.


View Replies !   View Related
Subtract One Table From Another?
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?

View Replies !   View Related
Subtract Previous Value From Next Value
: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

View Replies !   View Related
Different Add/subtract Value Each Month
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

View Replies !   View Related
IIF Number Exists, Then Subtract
(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.

View Replies !   View Related
Subtract Day From User Input
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,

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

View Replies !   View Related
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
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.


View Replies !   View Related
I'm Very New To Access & Need To Figure Out How To Subtract One Field Form Another Wi
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.

View Replies !   View Related
Set Values In A Query
I have a DB where after I close out a month of sales I no longer want to be able to view records in my SalesEntryForm. I have a little check box that is the ClosedOut Check box. When I close out Febuary, I want to change all of the ClosedOut Check boxs to yes if the invoice was made in February. I have the query to get all of the records I want to close. How can I automate changing the values of these check boxes

Your help is appreciated,
JOe K.

View Replies !   View Related
How To Subtract Previous Record From Record
I have a Motorhome which I want to check it's Fuel Economy, seems simple!
I record the odomiter readingwhen I fill up with petrol and the amount of petrol pumped in to fill tank and the cost
I have done it in Excel 97 but I would like to convert to an Access 97 Database.
The purpose is to open an Access Form, add a record with Three Fields, namely the odomiter reading, Amount of Fuel pumped into tank, and Cost.
I would then like Access to subtract the previous record's odomiter reading from the new odomiter reading to obtain the distance traveled since last fill and using this number and the other 2 fields I can calculate my fuel economy
I tried to attached the Excel (97) spreadsheet for your information but it was too large for this forum
The calculations are based on Australian Dollars, LPGas and Litres but it would make no differance if it were Gallons, US Dollars etc.
I would very much appriciate a note to tell me if anyone is able to assist me with this problem.
Thank You
Paul Leaver
Queensland Australia

View Replies !   View Related
0 Values Ina Crosstab Query
Hi, I have a crosstab query, but for any product that has no entries on a certain date a NULL value is put in its place. Is there a way to have a 0 display there? Example of NULL:

Aug Total:216276

See how there is a NULL for 8/1 under PRODB? I would like that to be a 0

View Replies !   View Related
Minimum Values In A Query

I have a table (tbl1) with three fields/columns (Primary key, NameID, Number). E.g.:

NameID ... Number
Mike ........ 2
Mike ........ 3
Mike ........ 19
Ronald ...... 1
Ronald ...... 2
Greg ........ 7

I would like to make a query that lists each NameID only ONCE, with corresponding MINIMUN Number. So the result from the upper example would be:

NameID ... Number
Mike ......... 2
Ronald ....... 1
Greg ........ 7

How do I do it???

Thank you in advance


View Replies !   View Related
Decile Values Query
I need to write a query that will let me determine the values to use as input for another query which will calculate results based on deciles:

eg I have a series of data ranging from 0 to 100 and I need to sort by this field and then pull out the value of the 10th, 20th, 30, ....90th and 100th records.

is there any way I can do this? also what if I want to pull out every 23rd record for example?


edited to add:

The way I would do this manually in excel is to sort by the target value column and then fill down the numbers 1-23 in a new column and then sort by that column, copy the results and load them into access so I can run a query based on them. This is a little time consuming and hardly elegant but gets the job done. Just hoping someone can help me with a niftier way.

View Replies !   View Related
Adding Values In A Query
can someone help with this. I have 2 fields in a query

tbl1_nbil = 3
tbl2_nbil = 0

however when I add them using

Bill = [tbl1_nbil] + [tbl2_nbil], I only get a few of the added numbers in the calculated field. In some records it works, but in the majority it does not

It onl seems to add when both fields are greater than zero

tbl1_nbil : tbl2_nbil : Bill
1 : 1 : 2
1 : 0 : 0
2 : 1 : 3
0 : 2 : 0
4 : 0 : 0

View Replies !   View Related
3 Table Values In One Query
I have looked at the forum to see if I can find my answer, but it doesn't seem to be there.

I have 3 tbl SchoolDetails, School Activities and Keystages

The values from each table SchoolDetails ID & School Name
SchoolActivities: Activity Title, Date, Start Time, No of Workshops
Keystages: Keystage and NoOfPupils

I am trying to send one confirmation to each school. My problem is that when we book out the time it is booked out either by HalfAM or HalfPM, therefore if a school books a full day they get 2 records in the SchoolActivities tbl.

They also get numerous records in the Keystage tbl depending on how many classes are involved.

I am trying to bring together the sum of NoOfPupils - even though this can have two records (AM & PM) and also the numbers can be in numerous records in Keystage.

I just need to know how best to do this.

Any help would be appreciated.

View Replies !   View Related
Query On A Field That Has Several Values
I am using a form where I select values from combo boxes to enter parameters for a select query. It works fine where the table queried only has one value in the field concerned. However, it does not work on the fields that have several values (entered through a multi-selection box).
Are there any ways to query fields with a multi-selection?
Thanks, Niels

View Replies !   View Related
Null Values In Query
I need to create a query with the MTD field
I have created the MTD and it works!!! but now I need it to show all of the PartNum fields even if null. do I need to convert all nulls to zeros? if so how do I go about this?

Here are the fields in the query

First Field:
Field: PartNum
Table: Part
Total: Group By

Second Field:
Field: UnitPrice
Table: OrderDetail
Total: Group By

Third Field:
Field: NumOrdered
Table: OrderDetail
Total: Sum

Fourth Field:
Field: MTD: Sum([NumOrdered]*[UnitPrice])
Table: (blank)
Total: Expression

Fifth Field:
Field: CurrentYear: Year([OrderDate])
Table: (blank)
Total: Group By
Criteria: Year(Date())

Sixth Field:
Field: CurrentMonth: Month([OrderDate])
Table: (blank)
Total: Group By
Criteria: Month(Date())

When I run the query I only get 2 parts and their total. but I want all parts to show up even if they have not been ordered in the month. the parts that have not been ordered should say $0.00 in the MTD field.

Any Suggestions???

View Replies !   View Related
Returning All Values In A Query
Can someone help me?
I am trying to make a query return all values (please see attached jpg)
It returns some of the values, but is not returning values that have nothing in the "material" field (Null Values?)
In this example, I would like it to return all values that fulfill the Completion_Date, CW_Department, Handed_Over and Chargable fields, whether they have any data in the Material field or not.

Do I need to use the NZ, IIF or IsNull function? I have seen this is previous threads, but nothing like this example.....I apologise if there is.

Many Thanks.


View Replies !   View Related
Query And Merging Values
SELECT IIf((Count([PO]))>1,1,Count([PO])) AS PO_Num, IIf((Count([PO]))>1,'Multiple',[Category2]) AS Cat
FROM [Final]
GROUP BY [Final].Category2, [Final].[PO];

k so stage 1 seems to work, all the PO's that are multiples for the one category have been grouped and their value set to 1... but there are PO's with the same number pointing to another category.

I need to "simply" merge those PO's to be just one line_item.

1001 cat1
1001 cat1
1001 cat1
1001 cat2
1001 cat2
1001 cat2
1002 cat3
1002 cat3

should just combine down to
1 multiple
1 cat3

It would make more sense on a bigger scale but hopefully i'm clear here. But the ones that are not multiple do need to show the category name.

Can anyone spot the issue here?

View Replies !   View Related
Approximate Values Query
I am just wondering whether there is any way I could define a query in such a way to include a record in Table A if field FA is approximately equal to field FB in Table B by no more than, say 12.



View Replies !   View Related
Delete Query - No Values

I have a condition < 25 in a delete query.

When it is executed, it is not deleting when there is no value in the field.

How can I delete when there is no value?

Thanks in advance.



View Replies !   View Related
Subtracting Query Values
How do i subtract one value, worked out using a query, from another value, worked out using a query, and display it in a text box?

View Replies !   View Related
Values Comparison Query
I really have looked everywhere

I have 2 fields and I want a query to pull out the records with a lower or equal to quantity value than the stock.



As you can see it shuold pick out the top record because its lower, I just need a way of telling it to look at each records Reorder Value

View Replies !   View Related
Comparing Values In A Query
Okay I have a query that pulls from a table. It's pretty simple - nothing big.The query will be used to correct information in some sales tax data.I have 3 colums with sales tax info, and they should all be the same. I want to compare each column to show only those which don't match each other.NetTax, TotalTax, and TaxB are the fields.In the query, for the criteria of NetTax, I put in:<> [TotalTax]When I run the query, I get results, but they are all equal.EG:4.35 4.35 4.35I don't want them to show, and not sure if that's the right way to go about it or what.If I run without that criteria, I get 21,600 results.With that in the criteria, I get 304 results. So it is working somewhat...but for whatever reason some don't seem to be seen as being equal? I don't get it. Data type is number and they are formatted the same way.

View Replies !   View Related
Comparing Values In A Query
I will try to keep this succinct.

I have a query that totals a column in a sub-form. The query also gets a sum from the main form for the same record. Is it possible to have a message/colour change/warning if the total of the column in the sub-form exceeds the other number in the query? I would need this comparison to take place within this query, rather than creating a new one.


View Replies !   View Related
Can't Modify Values In A Query

It has happen to me many times that when I create specific queries I'm then not able to modify the values of em...

Do you know the possible causes of this?

Is it because I'm filtering? Ordering by? Grouping? Using fields of differents tables related 1-many? Calculated fields? Expressions? Modules?


One specific case that has presented to me is a query that returns info from 1(main)-many(details) relationship. When I use the sorting of a field of the main table it doesn't allow me to edit values, tho when I delete this sorting it works fine.

Nevertheless, I also have another query which just returns values from one table and whether I use sorting or not, I can modify the values.

What's happening??? :( :confused:

View Replies !   View Related
Using Values From Form In Query
I have a table of records, each of which has a date.

I want to create a form which has two boxes on it for the user to input 2 dates and then get Access to query the table of records and display only those which fall between the two dates specified.

I could do set this up using a parameter query but am not sure how to make the query pick the dates up from the form.

How can I do this?


View Replies !   View Related
Query To List Max Values
I have a Select query that lists, among other things, two fields:

Each entry in ID_Clients may be listed several times

I want to generate a Select query based on the above query that lists each value of ID_Clients just once, by selecting the record for that ID_Clients that has the maximum value in the field EndDate.

So, sample source data

BBB............. 2005-06-30
BBB............. 2004-06-30

Desired end result:

Any ideas how I should achieve this would be appreciated.

View Replies !   View Related
Query To List Max Values
Quote: Originally Posted by ansentry Have a look at the attached sample.

Hi John,

I am new to this site. I can't seem to find where the attachment being referred to is. I have a similar question. Basically, I have this data:

Parcel No. ......Soil Type......Area
001-001 flathead 75
001-001 whitefish 33
001-001 kiwanis 45
001-002 kiwanis 22
001-002 flathead 33
001-002 chamokane 74
002-005 flathead 20
002-005 kiwanis 53
002-005 chamokane 27

The desired result is:

Parcel No. Soil Type Area
001-001 flathead 75
001-002 chamokane 74
002-005 kiwanis 53

Can you please help me? Thanks.

View Replies !   View Related

Copyright 2005-08, All rights reserved