Subtracting Results From Two Different Queries.

Jun 16, 2005

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?


View Replies


Subtracting Dates In Queries

Feb 1, 2013

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

View 1 Replies View Related

Queries :: Subtracting Numeric Value From Date Value

Jan 15, 2015

in my query to subtract [Days] (Numeric Value) from a date field [TravelDate]

View 4 Replies View Related

Queries :: Subtracting Date Fields

Jul 10, 2013

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

Queries :: Subtracting Rows In Access

Dec 8, 2013

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

Queries :: Subtracting Stock Numbers With A Query

Jan 2, 2015

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

View 4 Replies View Related

Queries :: Joining 3 Queries And Displaying Results In Separate Columns

Jul 31, 2013

I have 3 queries named Mech Final Equipment 3 Mth, Mech Final Equipment 6 Mth, and Mech Historical Final Equipment.They all have two fields-Final equipment and Sum of Sum of Down (calculating the number of minutes each piece of equipment was down in the time period selected).

My ultimate goal is to join the three queries to display a pivot chart that uses the Final Equipment as the category field and 3 Mth, 6Mth, and Historical as seperate data fields.What I have is a join query (Which I have named Mech Final Equipment H63 Joined)

Using this SQL:


SELECT DISTINCTROW [Mech Final Equipment 3 Mth].[Final Equipment], Sum([Mech Final Equipment 3 Mth].[Sum Of Down]) AS Duration
FROM [Mech Final Equipment 3 Mth]
GROUP BY [Mech Final Equipment 3 Mth].[Final Equipment]


Which returns a table that looks like this:

Final Equipment, Duration

Ancillary Equipment, 225
Ancillary Equipment, 401
Ancillary Equipment, 1787
Brush Unit , 1252
Brush Unit , 2519
Brush Unit , 8004

And so on.What I need the table to look like is this

Final Equipment, 3 Mth, 6 Mth, Historical

Ancillary Equipment, 225, 401, 1787
Brush Unit, 1252, 2519, 8004

And so on, like a cross tab.I tried to do a crosstab query but I don't have enough fields.

View 6 Replies View Related

Queries :: Combining Data Results From Multiple Queries

Sep 30, 2014

I am working on a report that has some special characteristics.

Let's say I have a list of groups of Vendors in a table, complete with VendorID. I have 3 other tables that use the VendorID: Complaints, Complements, and Terminations.

Each of these tables has a date that the Complaint, Complement, and Termination notice was received.

Every Fiscal Quarter, a report is pulled that looks back over the 4 preceding quarters to determine if a 5% threshold has been crossed by any of the vendor-groups in regards to the amount of Complaints they received.

The equation used for that is : (complaints/vendors_in_group)*100

It is imperative that the information has the current fiscal year and fiscal month (which I am tracking with functions from MS website), and I need to be able to store the information attached to the fiscal year and month.So when a user goes to the form and inputs the desired Fiscal Year and Fiscal Month, the database can display the 4 previous quarters of information...split into Q1, Q2, Q3, and Q4.

What I would like to have happen is to be able to have one table where the information is stored, quarterly, so that it can be retrieved for the report.

1. Is it possible to have one line, per VendorID, that has the total number of Complements, Complaints, and Terminations, as well as the threshold percent stored in a table? Right now, I am getting LOTS of duplicates and blank lines when I try to put them all together. It has the right data, but takes about 10 rows per VendorID.

2. It is very important that the total number of Vendors in a group be captured on that quarterly report, so maintaining that number, in the same table, is essential and must be tied to the VendorID.

3. I have looked at Union Queries and Crosstabs, but I just dont know enough about them to make it work.

View 4 Replies View Related

Queries :: Export Results From Two Queries To XLSM

Feb 9, 2015

I am a beginner Access user. I’m trying to export the results from two queries "TestData" and "ReportDate" to a saved .xlsm file. These queries are generated from tables linked to a SQL server.

The first query “TestData” should be copied to a sheet named ”TestData” and the second query “ReportDate” should be copied to a sheet named “ReportDate” in the same Workbook ”Test.xlsm”.

Both queries have two parametesr passed from Form1 [Start Date] and [End Date].

Public Sub ExportToExcel()
'Step 1: Declare your variables
Dim XL As Excel.Application
Dim wbTarget As Workbook
Dim ReportDate As QueryDef
Dim TestData As QueryDef

[Code] .....

View 14 Replies View Related

Subtracting In Access

Oct 10, 2005

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

Subtracting Time

Jun 20, 2006

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

Subtracting One Query From Another?

Feb 23, 2008

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 Im not confident that this approach is the best but thats 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 Im taking the wrong approach, suggestions on what I should try next. Thanks!!!


View 6 Replies View Related

Aggregating Results In Queries

May 21, 2005

I have a table with "date" as a field. Another field is "charge."

I want to create a query which returns the sum of all charges for a given date. How do I do this?

I tried doing it in a report; I summed in a group footer. However the table is very large (250,000 entries) and I kept getting "overload" message. I would rather do it in a query then select with a second query only those records I need (which are ones with a sum of charges within a certain range).

Any help would be appreciated! Thanks.

View 1 Replies View Related

Two Queries Limits Results

Mar 13, 2007

I have two queries combined into one query. The first query shows a sales summary by part number. No problem so far. The second query shows inventory by part number. No problem with that.
Now, I combine the queries, and get only those results that appear in BOTH queries. In other words, if there is no inventory, I do not get zero in the inventory column, the entire result for that part number is skipped.
How do I still display the results of the first query if there is no match in the second query?

View 9 Replies View Related

Queries :: Sum Of Non Matching Results

Jun 16, 2013

I have a table with sales in (TBL_Sales) as well as another (TBL_Key_Customers) which lists information about specific customers, in particular if they are part of a group e.g I would categorise Dave's Cars, Dave's Bikes and Dave's Coaches as being part of the Dave group.I would like to query the TBL_Sales to see how many sales were made to the Dave group but then also what else was sold. e.g if Factory 1 sold 100 items of which 60 went to Dave's group then the remaining 40 would be shown as "Other".

View 4 Replies View Related

Queries :: Showing 0 If No Results?

Mar 5, 2015

I have a query that i can type in an item number and it Sums all the locations and tells me how many i have in the building. If i do not have any in the building then there is no record of it in the table and comes back blank. How do i make it show a show 0 instead of blank if there is no records to sum?

View 9 Replies View Related

Subtracting Stock Levels

Nov 9, 2005

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

Subtracting Military Time

Oct 21, 2005

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


Column A Column B
1111 6.5

I need 1111 - 6.5 in Column C

View 4 Replies View Related

Subtracting Dates, Getting Whole Months

Dec 12, 2005

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

MsgBox Err.Description
Exit Function

End Function

View 1 Replies View Related

Subtracting Time Over 24 Hours?

Mar 27, 2006

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:


View 4 Replies View Related

Subtracting Query Values

Sep 7, 2007

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

Subtracting Time Fields

Mar 26, 2005

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

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

Jun 2, 2006

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

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

Queries- Running/Display Results?

Aug 30, 2007

Hey guys-
I have a database of properties for sale. I want to calculate the asking prices of each record (for sale) against the sold prices of all the houses in the same area. Area is defined as a numeric number, and each property has one assigned to it. My question is this-
I have a table of all the properties. Do I run the query and store the results in a specific cell in the table- and then use a form to display those results? Or do I run the query from a form and not store the calculated results at all? I will be adding/updating info on a daily basis, so these calculation results will also change daily. Obviously I am fairly new to Access and trying to figure out how to do this stuff. Using Access 2002. Either way, I assume i'll be using a form to display the results one way or another...

View 8 Replies View Related

Joining Results From Multiple Queries.

Nov 26, 2007


My SQL is fairly basic so excuse me if this is something simple.

This isn't my database, I am trying to create a report on someone else's and it's a poor structure (sound familiar :-).

If I have created two queries; one of which selects results on students from one table and the other selects results on students from a completely different table.
This means I have two Hi,

My SQL ability is fairly basic so excuse me if this is something simple.

This isn't my database, I am trying to create a report on someone else's and it's a poor structure (sound familiar :-).

If I have created two queries; one of which selects results on students from one table and the other selects results on students from a completely different table, how do I combine these results into one query so I can run a report on it?

To outline the situation, I have two different queries with identical data types but from two different tables. I need to query these two queries and (possibly) link in another student information table so that I can display the results from both queries as if they all came from the same place.

The addition of the extra table in the final query would only be so I can add extra student data into the final report.

Hope this makes sense!



different queries with identical data types

View 2 Replies View Related

Queries :: Query Showing Two Results?

Mar 19, 2013

I have a query that is showing two results for one product and I have no clue why. I have my tables in a one to many relationship and if I click on the + it shows the correct data for the product in question. Im guessing I have my table set up wrong its the only thing I can think of.

The table tbl_Carton has two entrys for a product (Flex Tape 2-1/16") (Product_PKEY #21) mabe I have it set up wrong? If I run my (qry_Switchboard ) and enter (21) its giving me an extra result for each of the two results thats suposed to be there.why?

View 4 Replies View Related

Copyrights 2005-15, All rights reserved