Subtracting Query Values
Sep 7, 2007How 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
ADVERTISEMENT
Subtracting Values From Two Consecutive Records
Jan 17, 2006I am having a problem, probably due to my inexperience with Access. Here is the seniero:
-I have a form/table set up for operators to enter numbers on a daily basis.
-I am setting up queries/reports to display not only the entered data, but also calculated values from the entered values
--I have no idea on how to subtract a previous day's value from today's value and report that value.
- Since Access gives no "ownership" to any value I do not know how to reference yesterday's value in today's calculation.Any Ideas?
Example
Record #1
Date - 1/17/06
Value - 15,232
Record #2
Date - 1/16/05
Value - 14,111
Desired output
Date 1/17/06
Value - 1121
Subtracting One Query From Another?
Feb 23, 2008Hi everyone,
I am familiar with databases and basic SQL, but never used Access, so your help is greatly appreciated!
What I *think* I need to do here is take the results from one query, subtract another query from that, then create a table of the results, so that I can make a chart in a report. I say that I *think* I need do that because I’m not confident that this approach is the best but that’s a whole other story...
Please look at the following tables below for an example. The Item table has ItemCode as a primary key. The Bids Table has multiple rows for each ItemCode showing bids for the item:
Item Table
ItemCode Date Address
JH231 2008-Feb-20 123 Test St.
KH831 2008-Feb-21 512 Test St.
Bids Table
ItemCode Amount
JH231 100.00
JH231 60.00
JH421 310.00
KH831 20.00
KH831 40.00
KH831 10.00
KH831 90.00
To make a pretty graph, I would like a table (so a query, or macro, or VBA or something?) that returns the minimum Amount for an ItemCode subtracted from the maximum Amount for an ItemCode based on the example above. What I would end up is something like this:
ItemCode Difference Between Maximum and Minimum
JH321 250
KH831 80.00
I hope this is clear. Any help on how to do this from you Access pros would be great, OR if I’m taking the wrong approach, suggestions on what I should try next. Thanks!!!
Marc
Subtracting Months From A Date In Query
May 30, 2006I have a field called DATE_END_DEERS which is in a format of YYYYMMDD (20060530). I need to run a query that shows 6 months subtracted from this date. I can never get an answer that is even close. Can someone please help? ACCESS2000.
View 3 Replies View RelatedDate Query - Subtracting Months
Apr 10, 2008Hello Everyone,I have a query where I am trying to pull records where the user took a class that is 2.5 years old or newer based on their license expiration date. Can someone tell me what I'm missing/doing wrong?SELECT tblCE_Details.LIC_NUMBER, tblCE_Details.Course_Code, tblCE_Details.Course_Date, sociwork1.expirat_dtFROM Sociwork1 INNER JOIN tblCE_Details ON Sociwork1.LIC_NUMBER = tblCE_Details.LIC_NUMBERWHERE (tblCE_Details.LIC_NUMBER = Forms![SW form]!SLIC_NUMBER) AND tblCE_Details.Course_Date >= (sociwork1.expirat_dt - Month(30))ORDER BY tblCE_Details.Course_Date;Thanks,Crhodus
View 3 Replies View RelatedQueries :: Subtracting Stock Numbers With A Query
Jan 2, 2015I'm creating a sales system that allows customers to place orders using a customer account. when a customer completes an order the number items in the order needs to be subtracted from the stock numbers of each of those items stored in the system.
What I have so far is zipped and attached,
The way my system works should be easy enough to understand, there are four linked tables that store information on customers, products and orders. What I'm really struggling to understand is how to take the values in the records in the Invoice-Product table and take them away from the stock numbers for the products in the products table. But only new orders should take away from the stock number when a new order is placed.
If possible I also need to be able to add stock numbers for example if a delivery comes in, and if it's similar enough could that also be done here?
Adding And Subtracting Short Time Values Together/changing Short Time Into Minutes
Dec 9, 2004Hello again,
I think my subject heading explains it. How would you add or subtract two short time formatted values and produce a short time value? Also, how would you change a short time value into number minutes. (i.e. 5:30 into 330) Thanks!
G
Subtracting In Access
Oct 10, 2005I'm trying to do a calculation in access that will allow a user to receive a discount in one field and show the results in another. The form looks like this. Having a hard time figuring out the line total. It's giving me a the wrong amount. The employee discount is $.20 and the price is $2.27 for example.. Thanks
Bound text box [UnitPrice] - Bound text box [EmployeeDiscount] x Bound text box [Quantity] =
Bound text box [LineTotal]
Subtracting Time
Jun 20, 2006I have two times I need to subtract in a query. When I subtract these in the query I get a decimal point. How can I convert this data. Thanks
View 1 Replies View RelatedQueries :: Query Records With Both Duplicate Values And Different Values?
Jun 18, 2013I'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').
Subtracting Stock Levels
Nov 9, 2005Hi, I have a data base that is used to keep track of stock level. When i issue a stock item and move to the next record I would like the stock level to be reduced by 1, any ideas.
View 2 Replies View RelatedSubtracting Results From Two Different Queries.
Jun 16, 2005Hi, here's the situation:
I have a query A, that gives me the a set of numbers.
I have a query B, that gives me another set of numbers.
Numbers in B are always equal or larger than those in A. (B >= A)
I want to create a new query C, that does the subtraction. (C = B-A)
Here's an example to explain what I want.
Let's say I have A=4,7 and B=6,9
Now, when I create the query, I get the following result:
A | B | C=B-A
4 | 6 | 2
4 | 9 | 5
7 | 6 | -1
7 | 9 | 2
What I want is this:
A | B | C=B-A
4 | 6 | 2
7 | 9 | 2
How do I do that?
Thanks.
Subtracting Military Time
Oct 21, 2005My new job requires me to use access so I'm learning as I go. :confused:
I have already used previous threads from this forum to answer another problem I encountered. Thanks for providing this forum to the public!
I am attemping to run a query that will subtract military time. (I am not allowed to change the format of the times in the original tables. )
Example:
Column A Column B
1111 6.5
I need 1111 - 6.5 in Column C
Subtracting Dates, Getting Whole Months
Dec 12, 2005I found the following code on this site in a search for a method to subtract two dates ie [Startdate] and [EndDate] and display the result as a whole number. ie 2, 6 etc. This code finds the difference but displays the results as 1 year 5 months 2 days, where I need the equivalent as 17.
Can this code be modified to produce the desired result and if so how would I go about it. Thanks for the help.
Option Compare Database
Option Explicit
Public Function getTimeElapsed(StartDate, Optional EndDate As Date) As String
On Error GoTo Err_Handler
If IsNull(StartDate) Then
getTimeElapsed = "No startdate"
Exit Function
End If
Dim YY As Integer
Dim MM As Integer
Dim DD As Long
' use current day if EndDate is not supplied,
' + 1 makes both days inclusive.
EndDate = IIf(EndDate = 0, Date, EndDate) + 1
MM = DateDiff("m", StartDate, EndDate)
DD = DateDiff("d", DateAdd("m", MM, StartDate), EndDate)
' recalculate if DD is negative.
If DD < 0 Then
MM = MM - 1
DD = DateDiff("d", DateAdd("m", MM, StartDate), EndDate)
End If
YY = MM 12 ' integer division.
MM = MM Mod 12 ' remainder.
getTimeElapsed = YY & IIf(YY < 2, " year ", " years ") & _
MM & IIf(MM < 2, " month ", " months ") & _
DD & IIf(DD < 2, " day", " days")
Exit Function
Err_Handler:
MsgBox Err.Description
Exit Function
End Function
Subtracting Time Over 24 Hours?
Mar 27, 2006Any Help!
I have fields that are time formated 99/99/99" "00:00;0;_ and I want to subtract them.
I need to get the results in two forms; first in Days and second in Hours. I have used the obvious subtract one from the other and it gives me days in a rounded numbeer, but the hours is not working out.:confused:
DB
Subtracting Time Fields
Mar 26, 2005Before I start implementing this, I would like to know if it is possible to do in principle
I want two fields, say TimeStarted and TimeEnded. I then want to subtract the two from one another to see how long the task took.
Is this possible or would i be wasting my time trying it?
Subtracting Inside Of A Field? CAN IT BE DONE!!!
Jun 2, 2006I am looking to subtract number inside the same field. and then return the difference to a new field (column). This data is used to generate a graph and the this is currently done by hand. I know there has to be an
easier way to do this.
Below is an example of what I am looking for.
I have a table called Numbers, and fields or columns called...
Value(s) Result(s)
-70 0
-70 1.02
-68.98 1.09
-67.89 3.11
-71 .71
-69.29 1.52
-67.77
So what I am doing is subtracting row two from row one. The difference is then put in another field(column) in row one. The process repeats, row three from two, four from three, five from four, etc, etc. This occurs for hundreds of rows.
Is there a SQL statement that can do this? Your help is much appreciated!!!
Thanks for your help.
Subtracting Dates In Queries
Feb 1, 2013My form consists of a textbox with a selected date. I have a combo box with integers 1-15. I have a table with 15 people and their birthdays.
I am trying to find the birthdays after a selected date by the integer selected in the combo box. For example, if I select 2/1/2013, and pick 7 in the combo box, the subform should show me people with birthdays 2/1 through 2/8.
I have a query with a datediff function. This is my query expression Expr1: DateDiff("d",[DateofBirth],[Forms]![Calendar]![Text1]) The criteria is <=[Forms]![Calendar]![Combo3].
It displays some data, but it's not consistent at all. I'm hoping to fix this without any SQL changes or form code. But if that's what I need, I'll do it.
I'm using access 2010, and I'm fairly new at it.
Subtracting Info Using Data From Another Table
Nov 18, 2007hi, i've been trying to find a solution to this problem but i can't seem to get my head around it. i have searched through the forums but found all the threads to be confusing although it probably is simple.
im a doing a project for a2 for a trailer tent company
got 2 tables where the problem lies in
order
stock
they only sell one trailer tent to a customer
when a trailer tent is sold, i want to update no in stock in stock table
i.e. trailer tent sold --> "no in stock"field for that particular trailer tent goes down by one. I would like the user to be able to change stock level any time as well so don't want it to be fully automated
sorry if i'm not making it clear, not really sure how to explain it
thanks for help, much appreciated
Subtracting Time Without Date Diff
Jan 23, 2008Hi I have two fields one which represents the time participants went to sleep, one when they woke up however I dont have any dates - it refers to what they do usually. Is it possible to minus sleeptime from waketime to determine duration of time in bed without dates? Or do I assign dummy dates to use date diff function?
View 4 Replies View RelatedSubtracting Two Times To Fill Out Textbox?
Jan 31, 2006I have 2 time boxes with times in them. How do I get the 3rd textbox to display the difference between those times?
View 11 Replies View RelatedQueries :: Subtracting Numeric Value From Date Value
Jan 15, 2015in my query to subtract [Days] (Numeric Value) from a date field [TravelDate]
View 4 Replies View RelatedReports :: Totaling And Subtracting Semesters
Sep 19, 2014is this even possible and if yes how do you write a report (or query for the report) to do:
201030 = 1 spring semester
201050 = 1 summer semester
201070 = 1 fall semester
so then attendance from 201030 till 201230 = 7 semesters
and also going the other way so 201230 - 201030 = 7 semesters
Queries :: Subtracting Date Fields
Jul 10, 2013I have an Access query which has 2 fields 'Sent Date' and 'Return Date'. Sent date is input by the user when they return an item for repair, return date is input by the user when the item is sent back to us from repair. What I need is a 3rd calculated field which will work out the number of days the item was away for repair. I have included the 3rd column 'Repair Duration' but I don't know how to type the formula in the query to work this out.
View 7 Replies View RelatedQueries :: Subtracting Rows In Access
Dec 8, 2013I have a query with years and sales column. I need to make a column that displays the difference in sales between years. My query is named "Qry" if that is needed. "Year" and "Sales" are the column titles if you need that for the SQL.
View 14 Replies View RelatedSubtracting Aggregate Fields In A Report
Mar 21, 2012I'm trying to create an expense report based off two tables. I have a table for Bouts, and a table for Bout transactions. In the Bout transaction table there is a field that specifies whether the transaction is an expense or a revenue.
I'm trying to create a report that will split the transactions out by expenses and revenues, then in the summary show the total income, (income - expenses.) I have the report formatted, but I'm not sure how to get the grand total.
I currently have the report laid out as such:
Bout Name
Revenue
Sample Revenue 1 $100
Sample Revenue 2 $50
Revenue Total: $150
Expenses
Sample Expense 1 $100
Expense Total: $100
Net Total: (I would like to subtract the 150 from 100 here)