Subtracting One Query From Another?

Hi 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

View Replies


ADVERTISEMENT

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

Subtracting Months From A Date In Query

I 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 Replies View Related

Date Query - Subtracting Months

Hello 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 Replies View Related

Subtracting In Access

I'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]

View Replies View Related

Subtracting Time

I 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 Replies View Related

Subtracting Stocl Levels

Hi, 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 Replies View Related

Subtracting Stock Levels

Hi, 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 Replies View Related

Subtracting Results From Two Different Queries.

Hi, 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.

View Replies View Related

Subtracting Military Time

My 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

View Replies View Related

Subtracting Dates, Getting Whole Months

I 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

View Replies View Related

Subtracting Time Over 24 Hours?

Any 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

View Replies View Related

Subtracting Time Fields

Before 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?

View Replies View Related

Subtracting Inside Of A Field? CAN IT BE DONE!!!

I 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.

View Replies View Related

Subtracting Values From Two Consecutive Records

I 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

View Replies View Related

Subtracting Info Using Data From Another Table

hi, 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

View Replies View Related

Subtracting Time Without Date Diff

Hi 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 Replies View Related

Subtracting Two Times To Fill Out Textbox?

I have 2 time boxes with times in them. How do I get the 3rd textbox to display the difference between those times?

View Replies View Related

Subtracting From Inventory - Version: 2000 (9.0)

Each unit I repair I use a form that records parts used, the unit cost, and the labor involved. I also have an inventory of parts on another report where I keep a record of all the parts used for all the units that I repair. Is there a way that I can connect the two reports so that when I use x number of parts on a unit, it will automatically subtract from my inventory report? If so, could I get some assistance?

View Replies View Related

Adding/Subtracting Across Adjacent Table Rows

Is it possible to perform calculations across 2 adjacent rows in SQL?

At the moment, I have a running count that tracks the number of updates that go into our database against a specific JobID.

Is it possible to subtract the running count for a previous update from the current update.

For example, I would like my resulting table to end up looking like this

JobID Time Updates Since Last Update
1 10:10:00 1 1
1 10:30:45 3 2
1 11:15:10 9 6


In this example, the Since Last Updates should be found by
subtracting the Updates at 10:10:00 from the Updates at 10:30:45 giving 2 and the Updates at 10:30:45 from 11:15:10 giving 6. It would continue like this for as many rows of data have the same JobID (which could be several hundred!)

What I would then like to do is use the 'Since Last Update' number to display only those updates that are applicable to a certain Time, perhaps using Select Top in SQL.

Am I approaching this from the right direction, or would it perhaps be better to try to do this using Time, as this will always be unique?


Thanks for looking

W1dge

View Replies View Related

Subtracting 2 Dates And 2 Times From Seperate Boxes

Jus thought I'd offer this code to the forum as spent a long time finding a suitable solution..

SELECT DischargeTime.ID, [Arrival Times].ID, [Arrival Times].[A&EArrivalDate], [Arrival Times].[A&EArrivalTime], DischargeTime.[Date of outcome], DischargeTime.[Time of outcome], DateDiff("d",[A&EArrivalDate],[Date of outcome]) AS DateDifference, [Time of outcome]-[A&EArrivalTime] AS TimeDifference, IIf([DateDifference]>=1,([DateDifference]*(1/24)*576)+[TimeDifference]*(1/24)*576,([DateDifference]*(1/24)*576)+[TimeDifference]*24) AS hours3
FROM DischargeTime INNER JOIN [Arrival Times] ON DischargeTime.ID = [Arrival Times].ID;

Basically I had an admission date and in a seperate box an admission time and then I had a discharge date and discharge time again in seperate boxes. The information was spread over two tables hence the join. How I solved the problem was by gaining both the days difference between and then the difference in hours and then I created an iff function and played around with it till it worked. Dont really know how i got it working in the end but it does so hopefully this code will save some1 alot of time!

(I know that if id shown my dates as date/time in 1 box the problem would have been much easier solved, however I dont personally like displaying dates and times that way especially when you have inexperienced people entering data)

Find below a result from the query

DischargeTime.IDArrival Times.IDA&EArrivalDateA&EArrivalTimeDate of outcomeTime of outcomeDateDifferenceTimeDifferencehours3
1119/06/200715:0420/06/200712:00103:0420.93

View Replies View Related

Subtracting 12 Hours From A Date-time Field

I am trying to subtract 12 hours from a time-date field when the payperiod does not equal 01.

in a query I have:

work date: iif([payperiod]="01",[StartTime],[starttime]-#12:00:00#) - Access added a PM before the second hash so it looks like this:

iif([payperiod]="01",[StartTime],[starttime]-#12:00:00 PM#)

When I run the query I get a message box: "syntax error (missing operator) in query expression '00:00:PM#,2))". I am unable to open the query to correct the error. I can cop the unaltered query from a back up database.

My question is how do I subtract the 12 hours.

Thanks

Steve

View Replies View Related

Subtracting One Week Using DateSerial - Version: 2003 (11.0)

I need to have a criteria for a date field so that it is always after one week earlier from the current date. I have:

[DateAssigned]>DateSerial(Year(Date()),Month(Date()),Day(Date()-7))

but I don't seem to get the correct result. Is there a problem with it?

View Replies View Related

Subtracting 2 Values...getting A Weird Result - Version: 2003 (11.0)

I really hope I don't confuse you. I'm just trying to give you a little insight on what I'm doing to maybe help you understand my issue.

I'm working on a project that deals with direct deposit.

For one user, the total amount to be broken out is $2121.16
$200 to Bank1 $50 to Bank2 75% to Bank3
25% to Bank1

My routine select all setups that have an amount instead of percent. This is priority over percentages (whatever is left over after amounts are taken out then divided up with the percentage entries).

RunningTotal = Total Amount (2121.16)
1st time through, $200 is subtracted from RunningTotal -- new amount 1921.16
2nd time through, $50 is subtracted from RunningTotal -- new amount 1871.16

Now percentages is calculated:
75% of RunningTotal (1871.16 * .75) is calculated. This amount is tracked --> TotalAmountPercent = (1871.16 * .75)
25 % of RunningTotal (1871.16 * .25) is calculation. This amount is added to TotalAmountPercent

I then take (RunningTotal - TotalAmountPercent) just to make sure that the amount left is 0. Well, when it comes to this rountine, both RunningTotal and TotalAmount are 1871.16, but when subtracted, I get the following: 2.27373675443232E-13 NOT 0

I am guessing this value is ALMOST 0.

Why does this happen? Each of my variables are defined as double. Is this the reason why? I have run into an issue like this before...inserting into a table and incrementing the values by .01 (or .001) and I sometimes got values of 10.000000000001 or something like that.

Should I change my variable type to something else?

Again, I hope I didn't confuse you. The main issues is the subtraction.

Nick

View Replies View Related

Subtracting Values In Report Grouping - Version: 2003 (11.0)

I have 4 groups in my report... 1. Tracking Number, 2. Scenario Number, 3. Historical/Current, 4. Type.
My DETAIL section will always have 2 lines for each type, B or G.
What I would like to do is to be able to subtract the B of the Current Grouping from the B of the Historical Grouping; and do the same with the G. In my Type Footer, I am currently totaling B+G for each Historical and Current grouping.

Any suggestions?

Thanks!

View Replies View Related

Subtracting 2 Field's Values To Create A New Table Field - Version: 2000 (9.0)

This is my second try at posting. Here goes. I want to create a third field in a table's design view that subtracts one field's value from another field's value to create the field Units in Stock. It works when I create the field in the form but I don't know how to a this in the table's design view. I am going to the expression builder and typing in this Ex:= [Inventory Received]-[Inventory Consumed] I am not even sure if I am in the right section of the properties details. Please help.

View Replies View Related

Subtracting Dates In An Access 2000 Form - Version: 2000 (9.0)

Hi,

In Access 2000, I am trying to subtract two dates to get an answer in number of days...but for some reason I am trying DateDiff and other methods but still am not able to get the right answer. Can someone help? This is what I am trying to do:

I have a field called AdmitDate which is set as a short date as the format. AdmitDate's value is 4/15/2008
I have a field called DOB which is set as a short date as the format. DOB's value is 2/1/2008

I have the third field called Age which I am trying to calculate by doing AdmitDate-DOB but cannot get an answer in days.

So I am trying to answer this question...what is 4/15/2008 - 2/1/2008 = ?????(in days)

In the "Control Source" part of the Age field I put the following function in but it's not working....

DateDiff("d",([AdmitDate]-[DOB]))

I tried different versions of the above function and changed the Age field's format from Fixed to General Number several times but nothing is working.

Hope someone can help. Thanks.

View Replies View Related

Subtracting A Grand Total With Another Total From 2 Difference R - Version: 2000 (9.0)

gd day.

i have tried to use 'expression builder' to do a subtraction between 2 values found in 2 reports. however it shows '#error'.

pls help.

View Replies View Related

Subtracting Date/time Field From Integer Field

Hello everyone, I'm encounter a problem trying to write a code.

EX:
Date Months_to_credit Final_result
01/31/06 4 10/01/05
02/28/06 6 09/01/05
03/31/06 8 08/01/05


Does anyone have any ideas how to to substract Months_to_credit from Date and to return a date in the Final_result field?

View Replies View Related

Subtracting Dates From Adjacent Dates In Same Column

Hiya-

I have a database with 5000 entries, corresponding to about 10 entries for about 500 people. Each of the entries is dated, and I need to calculate the time intervals between each person's sequential entries in the table.

One way of doing this is to create another column that contains the date of the previous entry. I can then use DateDiff to subtract one date from the other and give me the difference in days.

This approach falls down if I then work with only a subset of the entries - I would have to re-enter the previous entry dates as the time intervals would have changed.

What I really need is a way of subtracting the date from the date in the cell directly above it. Will Access let me do this, or is there a better way?

Many thanks, Jules.

View Replies View Related

Adding And Subtracting Short Time Values Together/changing Short Time Into Minutes

Hello 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

View Replies View Related

Subtracting A Column From A Column

I have two columns, one is number needed and the other is number stocked. How can I get the number stocked column to subrtract from the number needed column. I need to have it done in query. Those are also the title of the columns. Thanks.

View Replies View Related

Query In Forms Question (printing A Single Selected Query Record From A Form?)

What is the best way to impliment a query in a form so that the user can view the query records, and have the option to print or save the selected record using command buttons?

I tried subforms but I could not get the command buttons to work in the subform after it went into the form, it wanted to print the entire form instead of the selected record from the subform.

So in a nutshell I have 3-4 queries that are built, and I want to have them show up on my form in a format that the user can scroll through the results and select a single record of the results and then print or save that individual record from the form, if such a thing is possible.

Thanks in advance

Todd

View Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved