Calculating the Difference in a Query

I need to calculate the difference between two fields in a query. The query
is called “qryRemote” and within the query are the fields “10-8Time” and “10-
97Time”. The data entered into these fields are in the format “0000” through
to “2400”. I need to subtract the “10-8Time from the “10-97Time” and then sum
the results and display the result in a report.

For example, after the query is run the results are:

10-8Time	   10-97Time
---------------------------------
1307             1331
1858             1909
2018             2023

First the difference is determined: 10-8Time is subtracted form the 10-97Time
for each.

10-97Time    10-8Time
--------------------------------
1331       -	   1307	=  24
1909       -	   1858      =  51
2023       -	   2018	=  5

Then the differences are added.

24+51+5 = 80

The number 80 is displayed as a number within a report as the Total Time in
Seconds.

Thanks

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200710/1

0
pushrodengine
10/12/2007 4:38:06 AM
access.queries 6343 articles. 1 followers. Follow

4 Replies
719 Views

Similar Articles

[PageSpeed] 44

From the little you have given us you seem to want a query like

SELECT SUM([10-97Time]-[10-8Time]) as TotalTime
FROM [YourTable]

Or you can use an expression for a control's control source in a report.

=DSUM("[10-97Time]-[10-8Time]","[YourTable]")

Or if the total time is to be displayed in a control in a group footer or 
report footer
=SUM([10-97Time]-[10-8Time])

-- 
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"pushrodengine via AccessMonster.com" <u36225@uwe> wrote in message 
news:79903e3d461aa@uwe...
>I need to calculate the difference between two fields in a query. The query
> is called "qryRemote" and within the query are the fields "10-8Time" and 
> "10-
> 97Time". The data entered into these fields are in the format "0000" 
> through
> to "2400". I need to subtract the "10-8Time from the "10-97Time" and then 
> sum
> the results and display the result in a report.
>
> For example, after the query is run the results are:
>
> 10-8Time    10-97Time
> ---------------------------------
> 1307             1331
> 1858             1909
> 2018             2023
>
> First the difference is determined: 10-8Time is subtracted form the 
> 10-97Time
> for each.
>
> 10-97Time    10-8Time
> --------------------------------
> 1331       -    1307 =  24
> 1909       -    1858      =  51
> 2023       -    2018 =  5
>
> Then the differences are added.
>
> 24+51+5 = 80
>
> The number 80 is displayed as a number within a report as the Total Time 
> in
> Seconds.
>
> Thanks
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200710/1
> 


0
John
10/12/2007 11:45:10 AM
The important thing I need is the difference (subtraction) of the query
results for "10-97Time" and "10-8Time. 

Subtract the "10-97Time" form the "10-8Time" equals the difference of the two
numbers.

Can this be done in a query? 


John Spencer wrote:
>From the little you have given us you seem to want a query like
>
>SELECT SUM([10-97Time]-[10-8Time]) as TotalTime
>FROM [YourTable]
>
>Or you can use an expression for a control's control source in a report.
>
>=DSUM("[10-97Time]-[10-8Time]","[YourTable]")
>
>Or if the total time is to be displayed in a control in a group footer or 
>report footer
>=SUM([10-97Time]-[10-8Time])
>
>>I need to calculate the difference between two fields in a query. The query
>> is called "qryRemote" and within the query are the fields "10-8Time" and 
>[quoted text clipped - 32 lines]
>>
>> Thanks

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200710/1

0
pushrodengine
10/12/2007 4:29:04 PM
IF you have two fields that are number fields (or two columns, if you 
prefer) with those names, all you need to do is enter the following in the 
query grid.

Field: TheDifference: [10-97Time]-[10-8Time]

-- 
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"pushrodengine via AccessMonster.com" <u36225@uwe> wrote in message 
news:799673383eeec@uwe...
> The important thing I need is the difference (subtraction) of the query
> results for "10-97Time" and "10-8Time.
>
> Subtract the "10-97Time" form the "10-8Time" equals the difference of the 
> two
> numbers.
>
> Can this be done in a query?
>
>
> John Spencer wrote:
>>From the little you have given us you seem to want a query like
>>
>>SELECT SUM([10-97Time]-[10-8Time]) as TotalTime
>>FROM [YourTable]
>>
>>Or you can use an expression for a control's control source in a report.
>>
>>=DSUM("[10-97Time]-[10-8Time]","[YourTable]")
>>
>>Or if the total time is to be displayed in a control in a group footer or
>>report footer
>>=SUM([10-97Time]-[10-8Time])
>>
>>>I need to calculate the difference between two fields in a query. The 
>>>query
>>> is called "qryRemote" and within the query are the fields "10-8Time" and
>>[quoted text clipped - 32 lines]
>>>
>>> Thanks
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200710/1
> 


0
John
10/12/2007 5:11:44 PM
Thank you very much John. Works Great!

John Spencer wrote:
>IF you have two fields that are number fields (or two columns, if you 
>prefer) with those names, all you need to do is enter the following in the 
>query grid.
>
>Field: TheDifference: [10-97Time]-[10-8Time]
>
>> The important thing I need is the difference (subtraction) of the query
>> results for "10-97Time" and "10-8Time.
>[quoted text clipped - 24 lines]
>>>>
>>>> Thanks

-- 
Message posted via http://www.accessmonster.com

0
pushrodengine
10/12/2007 7:02:20 PM
Reply:

Similar Artilces:

How do I increase query field length (>20 characters) ?
I want to exclude 3 or 4 variables from a particular query. Using Not "xx"or"yy" is fine, but when excluding more than this, query fails. I think the problem is that the total text characters is quite large (approx 60 characters in total, from four exclusions). Ideas, please ? 60 characters is not the limit. 1024 characters is the limit. POST the SQL of the query that is not working (View: SQL on the menu) or at a minimum post what you are attempting as the criteria. Also "query fails" does not tell us if you get an error (what is it) or the wrong resul...

Calculate the % increase for two columns
I have a pivot table, the data was first display by date, i know i can use the grouping function to group data into monthly basis. But I want to know that can I set the formula to calculate the months difference between, say the sales amount of June & July, and the % of the difference?? Million thanks If you have a grouped field, you won't be able to add a calculated item to the pivot table. In the source data, you could add a column to calculate the month for each record. Refresh the pivot table, and add the new field Add another copy of the Data field to the data area Right-c...

Count column difference
Hi Using MSExcel 97. I have two columns of data e.g. A1: A4, containing values 5,10, 3, 6 B1:B4, containing values 3, 8, 7, 4 I wish to perform a count (e.g. in C5) of the number of rows where the value in column A exceeds the respective value in column B (in this case count = 3, as A1>B1, A2>B2, and A4>B4). Just cannot get my formula right. Tried using an array (but difficult when comparing the difference between two columns), and COUNT. Thanks in advance for any suggestions. Wizzy ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.co...

Forms in two differant views
Is there a way to use the same sub form showing two differant views within the same Main Form. For example: Can I show on one tab a datasheet view and on the other a regular form view. -- Rose Hi Rose, Create a button on your form and under the "OnClick" event put 'Me.DefaultView = Datasheet Alternatively you could use a checkbox If me.checkbox1 = 0 then Me.DefaultView = 2 ' Datasheet Else Me.DefaultView = 0 'Single Form End if me.repaint This should flick the form between datasheet and form views. HTH, Nick. "Rose" wrote: > Is there a way to use t...

Query wildcard symbols
Just a heads-up in case this bites you, too. I migrated an application from Access 2002 to Access 2007. I have a search form for the users to enter some criteria ... the form dynamically builds a SQL WHERE clause before opening a "results" form based on an underlying query that includes all the fields available to search on. Once in Access 2007 format, the search form opens empty every time?! When I add selection criteria to the underlying query directly, the result set is empty every time?! Oh wait, I'm using the "*" (anything/everything) wildcard character. W...

Delivery Status Notification (DSN) different if sent from outside
When my users mailboxes are full above quota and not accepting any more mail, the sender receives a differently worded DSN depending on whether they sent email to that user from an internal account (one that is part of the exchange org) or an external account (not part of the Exchange organization). The one sent to the a user that is external is much less human friendly and is causing me a problem with some scirpted automation in my app. Is there a way to make the text the same for both? Examples pasted below: Here is the if sender is internal: Your message did not reach some or all o...

Run two copies of Outlook with different profiles
Hello! I'm trying to run two copies of Outlook with different (outlook)profiles at the same time. Normally i can choose the profile with the /profile switch, but if there is already a copy of outlook running, this switch seems to be ignored. Any ideas how to handle this are welcome. Regards, Reinhard "Reinhard" <reinhard.spieker@bkvibro.de> wrote in message news:60a7e218.0311190408.6523100b@posting.google.com... > Hello! > > I'm trying to run two copies of Outlook with different > (outlook)profiles at the same time. > > Normally i can choose the p...

Excel does not calculate my formulas
This is very strange...Excel does not calculate any formula. I can do a simple =A1+A2, and it just return a zero value. I made sure of the following: - Cell is set to General (tried it with different cell settings too) - Autocalc is on in settings (even a manual F9 calc doesn't work) -- Martin Verville We might need a few more clues: What is in A1? What is in A2? -- David Biddulph "Martin" <Martin@discussions.microsoft.com> wrote in message news:A9A25385-5BE8-4D38-A7A7-15E523C69DD9@microsoft.com... > This is very strange...Excel does not calculat...

Importing a table from Access query
What is the quickest and easiest way to do the above? Thanks in advance. Hi The way I usually do this is run the select query, click on the top-right box (which selects all records) and use Ctrl+C to copy and Ctrl+V to paste into my workbook. An alternative is to right-click on the query in the Database Window and left-click on Export. In the Save As dialog box, select ..xls type. -- Andy. "Trish" <Trish@discussions.microsoft.com> wrote in message news:6BF3DE22-6590-4CAD-9EE1-FC978A3BB63B@microsoft.com... > What is the quickest and easiest way to do the above? >...

Bug in receiving entry. error calculating Prev Qty Shipped
Dynamics GP Bug Report, version 9.0 service pack 1. Version Information: 9.00259 Location: Receiving Transaction Entry Description: As one enters receiving transactions lines and adjusts the Qty Shipped, the quantity previously shipped is calculated incorrectly. This PO transaction was for a quantity of 120. A quantity of 15 were received on a previous shipment. The Qty previously shipped should display 120-15-30=75 not 45. To make this error occur continue to change the Qty Shipped. Sometimes the error will occur quickly, other times it might take 25 changes. Products Load...

Reading empty query returns
When I open a form that has a bound query as the record source, no data appears (including the control objects of the form) if the query's select statement does not finds records that match the criteria I specify. This causes a problem in my VBA code as the variable I use to check the number of records returned by the query does not seem to be able to read empty query returns, even if I use isnull. So I guess I am asking how can I get Access to read an empty result set in VBA from a SQL select statement that is run??? Here is part of my code for this: DoCmd.OpenForm stDocName2, , ...

different versions in visio ole-object?
hello i have some problems printing a word document which includes some visio drawingns. In word i can see the visio object like i edited it but when printing the word-document, a completly old version of the visio object is printed out. The printed object is older that 2 weeks. I tryed printing the document from other PC without changes. I tryed copying the ole-object into an other word dokument (copy/paste) without changes. Printing the document from wordpad works great .... I also opened the visio object (doublecklick in word) and edited the object, saved the file and exited back to wo...

Query Flaw???
I need only one value from an aggregate Query - TheStores, which means the Number of Inspections in a "From - To" Timeframe. I use that as part of a calculation. The "Inspections" table contains only a Store and an InspectionDate. It gives me the result I need...but no Join...the Query has several other Fields from "Randy_F_FindFreq", but has been trimmed as an example here. SELECT Randy_F_FindFreq.AAll, Randy_Insp.TheStores, Round([AAll]/[TheStores],4) AS PerAll FROM Randy_F_FindFreq, Randy_Insp; Is this "sound"? TIA - Bob Without a join yo...

Difference 05-21-04
What is the difference between CRM standard and professional? -Johnny Johnny wrote: > What is the difference between CRM standard and professional? > > -Johnny Does it have anything to do with Outlook integration? -- - I am Johnny! Your Internet penpal. I have a scooter, how about you? "Johnny" <alphascooter-verizon@yahoo.com> wrote in message news:evfBJNwPEHA.3708@TK2MSFTNGP10.phx.gbl... > Johnny wrote: > > > What is the difference between CRM standard and professional? > > > > -Johnny > Does it have anything to do with Outlook inte...

Multiple Accounts held at the SAME Institution with DIFFERENT logons
I am trying to sync Money 2006 to my Banc of America Investment Services accounts. I have two accounts with two DIFFERENT logons, an IRA and a regular brokerage. Money will sync fine to one or the other, but I can't set it up to sync to both because when I click on Online Services it is already setup so I can't add a different login, etc. -- Daniel Blackmon Project Lead - Software Engineer Worldwide Environmental Products Inc. In microsoft.public.money, Daniel wrote: >I am trying to sync Money 2006 to my Banc of America Investment Services >accounts. I have two accounts w...

calculating quantity on hand based on date-time stamp
Hello: I am always unclear on the proper syntax for pulling date-related data. I need to pull a field in my query, below, based on the field called "IV00118.CHANGEDATE_I". This is the date in which an end user changed the cost of an item. To give you some background, the IV00102 table in my query below is the Item Quantity Master table, while the IV00118 table is the Item Cost Change History table. The field that I want to pull based on that date field is the IV00102.QTYONHND field. This field is the inventory quantity on hand field. How would I format the syntax bel...

feature difference btw 2000 & 2003
X-No-archive: yes I currently have Outlook 2000 and wanted to know if its worthwhile to upgrade to outlook 2003 ? I only use the mail portion of outlook and don't use calendar or any of the other options. Is 2003 more compatible with WinXP ? Thanks, Chris If you are just strictly e-mail (no calendar, contacts, tasks, .etc) then you are missing a good portion of what Outlook 2003 is bringing to the table. However some of things that you might like in Outlook 2003 are: 1) Better security. (bots, pictures, .etc that load from the web in html are blocked by default) 2) Cool new ...

copy data to differences places based on selection
I have a master list of entities name in master name sheets. Let say entity 1 to entitiy 100. If I select Entity 1 to Entity 10, these 1 to 10 entities insert below group1, group2, group 3 in sheet1. It also insert below group4, group5 in sheet2. I don't have preference about how to select the names in master list. Anything like check, or yes is fine to me. I am open to any idea or method. thank you in advance. ...

Microsoft Query #8
I'm using Excel to query an oracle database. When I go to Microsoft Query to edit my query, I can't view my criteria or tables. I click on the view menu and am unable to select view "criteria" or "tables". Thus, all that shows up in the screen is the data in my query. This has happend to me before. Can anybody help me? I was able to answer my own question. It seems when query is complicated enough, you are unable to view the criteria, since you are now using SQL to query the data. It seems that if you click on the SQL button and scroll down, delete th...

Totals Query Help!
Hi, I have a table with the following fields: Manufacturer Model Value SoldMonth What I'm trying to do is write a query that will give me an average Value for each month, but I want the average to be based only on records where the Model is consistent across all months. So if Model x is missing from one or more months all records for that model would be excluded from all the overall Monthly averages. So the query will only average records where the Model is present in each month. Any help with this would be greatly appreciated. Regards.....Jason It'll help us if you can ...

Compare 2 excel files for differences?
Is there something within MS Office 2007 which will do this?. Alternatievly anyone any recommendation? Duplicates This site is great for finding Uniques/Duplicates: http://www.cpearson.com/excel/Duplicates.aspx Also, for things pertaining to Data Validation, you may want to check this out: http://www.contextures.com/xlDataVal01.html ....and if you feel really ambitious: http://www.contextures.com/xlDataVal08.html best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Dave" <nospam@nospam.com> wrote in message news:85...

Need help with the query.
How can I get desire results below. I cannot seem to figure this out. Any help is greatly appreciate. IF OBJECT_ID('Tempdb.dbo.#Temp', 'u') IS NOT NULL DROP TABLE #Temp GO CREATE TABLE #Temp ( UserHistoryId INT NULL, UserId VARCHAR(6) NULL, EventDate DATETIME NULL ) GO INSERT INTO dbo.#Temp([UserHistoryID],[UserId],[EventDate])VALUES (664, 'User1', CONVERT(DATETIME, 0x00008eac00000000)) INSERT INTO dbo.#Temp([UserHistoryID],[UserId],[EventDate])VALUES (1014, 'User1', CONVERT(DATETIME, 0x00009c2600ef152a)...

Force data type from Text to Memo in a simple Make Table Query
I'm concatenating fields of various data types that upon completion sometimes reaches around 500 characters. Not huge, but larger than the Text limitation to which is what Access 2007 of course converts this. How can I force the data type to be Memo while I'm in the query so the resulting table displays all the data without any truncation. -- TIA Eric S UPRR I don't believe you can. I think you'll have to create the table first, and then append to it. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "E...

Using single button to run multiple query
I want to use single button on the form to run multiple queries. How it can be done? Where should I write queries and code? One way to do this would be to create your queries, then use the command button to run code that calls each query. Another approach would be to create all your queries, then create a macro that runs all your queries, and call that macro using code behind a command button. -- Regards Jeff Boyce www.InformationFutures.net Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ "Gajanan&qu...

Query Based Distribution Lists #3
I can create QBDL based on STATE, ZIP, LASTNAME and many other fields. I would like to make a list based on Group Membership. There is a field under USER called MEMBER OF but it does not give me any results when I do a preview. I have many employees who work in multiple locations and need to belong to several different groups even though they have only one login and work out of a single office with the same address etc. So I made them members of groups based on the different location in which they work and wanted to send mail based on this membership. McMurray <McMurray@disc...