Subtracting values in a query

 I have a query with multiple repeating values, each value has a set of 
readings assigned to it  How do I subtract the max/min from the readings for 
each value (looking for change over time)?

Example: 

Value       Reading over time      
1             2 
1             3
1             0.5
2             3 
2             4
2             1
3             7            
3             2             
3            0.3
 
I would like Access to automatically subtract 3 - 0.5 for value 1; 4 - 1 for 
value 2; and so on.  
Is this possible?  Thanks!
0
Utf
12/20/2007 4:15:02 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
2814 Views

Similar Articles

[PageSpeed] 10

Replace 'z' with your [Value] and 'x' with your [Reading over time] to get 
your desired results.
SELECT [Change Requests].z, Max([Change Requests].x) AS MaxOfx, Min([Change 
Requests].x) AS MinOfx, Max([x])-Min([x]) AS Difference
FROM [Change Requests]
GROUP BY [Change Requests].z;
 
-- 
KARL DEWEY
Build a little - Test a little


"LV" wrote:

>  I have a query with multiple repeating values, each value has a set of 
> readings assigned to it  How do I subtract the max/min from the readings for 
> each value (looking for change over time)?
> 
> Example: 
> 
> Value       Reading over time      
> 1             2 
> 1             3
> 1             0.5
> 2             3 
> 2             4
> 2             1
> 3             7            
> 3             2             
> 3            0.3
>  
> I would like Access to automatically subtract 3 - 0.5 for value 1; 4 - 1 for 
> value 2; and so on.  
> Is this possible?  Thanks!
0
Utf
12/20/2007 4:58:02 PM
Here is the SQL that will do that for you.  You will have to change the names 
to your actual names.  Value should not be a name.  It is an Access reserved 
word.

SELECT Table1.The_value, Max(Table1.ReadingOverTime) AS High, 
Min(Table1.ReadingOverTime) AS Low, 
Max([readingovertime])-Min([readingovertime]) AS Diff
FROM Table1
GROUP BY Table1.The_value;

-- 
Dave Hargis, Microsoft Access MVP


"LV" wrote:

>  I have a query with multiple repeating values, each value has a set of 
> readings assigned to it  How do I subtract the max/min from the readings for 
> each value (looking for change over time)?
> 
> Example: 
> 
> Value       Reading over time      
> 1             2 
> 1             3
> 1             0.5
> 2             3 
> 2             4
> 2             1
> 3             7            
> 3             2             
> 3            0.3
>  
> I would like Access to automatically subtract 3 - 0.5 for value 1; 4 - 1 for 
> value 2; and so on.  
> Is this possible?  Thanks!
0
Utf
12/20/2007 5:06:03 PM
Reply:

Similar Artilces:

Loop to retrieve value in worsheet A and put into worksheet B
I am hoping someone out there cyber-world can help me with some code that I need. I have a workbook from an outside source. I need to loop through Worksheet named "Details", column A to find the characters "C:" and return the value from Worksheet "Details" , column B into Worksheet "New Details", Column A. There are blank rows in the data and the only designation between what would be considered a new record is a series of dashes (14 dashes to be exact). Is Anyone able to help with this? Anything is greatly appreciated. Is there an...

query error
Hi, I am running a query based on 3 queries q1 has this PurchDoc Item Rel Vendor Vendor name Order Type PO Date Material Short Text MRPCn Del Date Deliv Date PO Quantity GR Quantity QtyRed RemQty PD release Reschdate CC Created on Exc Net Order Value ID 46353228 190 F 10000019 RA LALLI NB 11/6/2006 109-0718-13-101 PASSACINGHIA AK7 6/13/2013 44 42 0 0 1/2/2010 11,396.00 14632 q2 has this PurchDoc Item Rel Vendor Vendor name Order Type PO Date Material Short Text MRPCn Del Date Deliv Date PO Quanti...

Create Modeless Dialog Boxes Query?
Hi Experts I have an SDI explorer type application with a tree view and a list view. In the listview i show the thumbnails of all the bmp files in a specific folder. Now when the user double clicks on any thumbnail i want to dynamically create a modeless dialog box. Now is it possible to specify the width & height of the modeless dialog box at the time of creation, say i want the dialog box to be of the size (400,300). If this is possible how can this b dun? Waiting for comments Regards mathu mathu, See below "Snav" <davinder_76@rediffmail.com> skrev i meddelandet ...

querying excel spreadsheets like database
I'm making a payroll journal, it has a column for the names, and the the cheque amt, cheque no., and some other stuff. In a separate sheet I need to pull up individual information pe employee and total. Eg. Payroll Journal: Name: cheque amt: cheque no: date: jane doe 2000.00 0001 01/01/01 john doe 1500.00 0002 01/01/01 jane doe 1800.00 0003 15/01/01 Employee Details: Name: cheque amt: cheque no: date: jane doe 2000.00 0001 0...

Run Macro If Cell value = "Other"
Alright, I have a very simple macro at the moment that unhides a column and adjusts the print area to suit. What I need is a maco that will run this macro if the cell F4 = "Other" from a drop down menu. Seems painfully simple I just can't find where it is. Cheers If Range("F4").Value = "Other" Then Call yourMacro End If -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) <bobbly_bob@hotmail.com> wrote in message news:1163993734.539069.261810@j44g2000cwa.googlegroups.com... > Alright, I have a ...

OTHER options for chart not plotting null ("") or zero values
I've read the above suggestions for a chart not plotting zeros or null values. This worked....however, then I got errors because of other calculations (averages) that use these cells as references (and #NA just doesn't go over well when it's being used in another formula). Because of the many other formulas (in visual basic and in the exel file) that would need to be changed if i created an extra column, or did some of the other options I would REALLY prefer to not do any of those. Isn't there some way within the chart's options to exlude these values? Or is my best ...

Advanced Find 'Set' queries
Is it possible to use advanced find to perform any type of set query? Example: have setup a m-m relationship to a contact type lookup table (tried using v4 m-m feature and older technique (xref table and m-1 relationships to contact and lookup table). The issue of course is that you can't ask a question such as 'give me all contacts that are contact type A AND contact type B since you need to somehow define the set of records to look at instead of evaluating them one by one. One approach that seems to work is to use the marketing list functionality to add all contacts with co...

Query results where gaps exist Min & Max do not work
Hello all, I was using a min and max on the specimen id here to show the first and last specimen worked on, however; that doesn't work when a gap in the specimen id exists. Here is an example of the data: Year SampleID SpecimenID 2009 200999137 001 2009 200999137 002 2009 200999137 003 2009 200999137 004 2009 200999137 005 2009 200999137 006 2009 200999137 007 2009 200999137 008 2009 200999137 009 2009 200999137 010 2009 200999137 011 2009 200999137 012 2009 200999137 013 2009 200999137 014 2009 200999137 015 2009 200999137 016 2009 200999137 017 2009 200999137...

How do i get the product of max. price in access2007 query?
in query i want to get product ID with the Max. or Min. price The SQL statement for a query to do that would be SELECT ProductID FROM SomeTable WHERE Price = DMax("Price","SomeTable") OR SELECT TOP 1 ProductID FROM SomeTable ORDER BY Price DESC IF you need something more specific post more details on what you are attempting to do. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County amr wrote: > in query i want to get product ID with the Max. or Min. price ...

Linking parameter query from Access to pivot table in Excel
I am trying to link an Access query that prompts for several criteria to an Excel pivot table. When I refresh the pivot table, I want it to open the database and run the query to update the data in the pivot table automatically. When I try to link to the data source, when I get to the end of the "Query Wizard", it gives me the error of "Not enough parameters. Expected 4". But it never prompts me for the parameters. Please help. Is there a setting I need to change or do I link the query differently to avoid "Microsoft Query" tool? You can't cr...

Find cell which returned maximum value in formula
-- De De, try this, =CELL("address",INDEX(A:A,MATCH(MAX(A:A),A:A,0))) will show the first one if there are more than one -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "De" <De@discussions.microsoft.com> wrote in message news:A17DFFDA-BE05-463C-B68D-65FE235E6098@microsoft.com... > > -- > De Thanks, Paul, but I found this hard to follow. Excel kept returning that I had disabled macros but yo...

Setting a cell to not go below an X value, or above a Y value
Hey, This may be very easy to do, but I just don't have a clue, I've look a most excel variables and they just seem to choose MIN and MAX fro columns or tables. What I want to do is I've got the sum adding up say cells A2 and A3 i cell A4. I want cell A4 to never go below 80, or above 200. (Formula in cell A would be =SUM(A2:A3)) Anyone think they can help -- Snake ----------------------------------------------------------------------- Snakey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2785 View this thread: http://www.excelforum.com/showthre...

Getting value from a picklist
I'm trying to use the value in a select picklist (based on the Contacts table) in some SQL to get further data from the CRM database. I'm looking at something along the lines of (AIR CODE!): var oDataValue = crmForm.all.new_enduserid.DataValue; var oSQL = "SELECT * FROM whateverTable WHERE TableID = " + oDataValue; However, it doesn't work - I'm unable to get the value of the selected item in the picklist. This line of code: alert(oDataValue); returns: [object Object] while attempting to get SelectedText returns "undefined". What am I doing wrong? ...

access compare values and select higher of two
In Access database I want to compare the values in two fields in a form and then select the higher value, insert it into another field and then use in a formula. E.g. Value 1 = 500 Value 2 = 600, 600 to be inserted into another field and then be multiplied. On 11 apr, 21:26, Captain Turtle <Captain Tur...@discussions.microsoft.com> wrote: > In Access database I want to compare the values in two fields in a form a= nd > then select the higher value, insert it into another field and then use i= n a > formula. > > E.g. Value 1 =3D 500 =A0 Value 2 =3D 60...

Microsoft Query #3
I am trying to create a query in Microsoft Query that gets data from an Access Database. I click on New Query, which brings up a box for me to Choose Data Source. I select MS Access Database, then I select the the actual database. Next, a box titled Add Tables comes up, but there is nothing to select. I have done this successfully in the past, but I cannot figure out what is wrong. ...

Specific cell values
Hi everyone, i need to acheive the following but i'm not sure how to do it. i have a cell (K49), the cell needs to collect specific data from a range ( D7:D32 ) on the same worksheet. it needs to look in the range and if the cell value is CNC, then add the figure form the same row in column E. Example: D E F glass £200 26 August 05 metal £650 26 August 05 wood £300 26 August 05 CNC £1000 ...

UNION query changes results after close and reopen
I have a UNION query, and the first field is the Client id field. If I run the queries separately they return the correct client id. If I delete the UNION, run the query, then add the UNION back in, the correct client is retrieved. If I then save and close the query, then rerun it, all the client ids return as 1. This occurs even if I move the field to a different position or rename the field. Hi Jason, If you like to paste the sql for the union query to this newsgroup, we could make some suggestions. Jeanette Cunningham "Jason Christian" <Jason Christian@discussio...

Default Value for new Table Row
Is there a way to specify a "default" value for a field when a new row is created in a table? I have a simple table with the date as the first column. When I add a new entry to the table I would like the date to default to the current day. I tried browsing the Excel objects but could not find any objects or events related to tables. I could develop a form, but that would be a lot of work just to set a simple default. It seems seems like there should be an "on_new_table_row" or "on_table_row_add" event where you could set a default with just a line or two o...

Acccess 2007 Runtime and Queries
While I've created MDE files before, I've never used the Runtime for Access. I was wondering if the runtime limits you to only running existing queries, or can you modify them using the runtime too? TIA Mark On Thu, 15 Apr 2010 11:51:01 -0700, MChrist <MChrist@discussions.microsoft.com> wrote: The runtime hides the objects window, so queries are not accessible. You can see what the runtime will do by creating a shortcut like this: path_to_msaccess.exe path_to_your.accdb /runtime -Tom. Microsoft Access MVP >While I've created MDE files before, ...

IF statement that yields a value based on a formula
Help! If cell B12 >= 0, then I need the answer to be calculated based on the formual "B12 * .40". If cell B12 < 0, then the answer is zero. How do I do this? -- barbara h IF(B12>=0,B12*0.4,IF(B12,0,0)) Click yes if helped -- Greatly appreciated Eva "barbara h" wrote: > Help! If cell B12 >= 0, then I need the answer to be calculated based on the > formual "B12 * .40". If cell B12 < 0, then the answer is zero. > > How do I do this? > -- > barbara h Hi, Try this =MAX(0,B12*0.4) -- Mike ...

Last transaction in a query?
I have a table with 5 fields ID PersonID EDate Amount and TransType I want a query with the last record of each person based on date and I want the results like this PersonID LastOfEDate Amount TransType if a person has tow transactions in the max date I want only one of them How can i do that? thanks You must have a "main" table with a single list of PersonID. Lets assume it is called tblPeople, PK = "ID" So, use that table to provide a unquite list of PersonID. The query can be like: select FirstName, Lastname, WorkPhone, PersonID, EDate, Amount, Tr...

Crosstab Query by Week with Dates as Headers
Hi I am trying to write a crosstab query in Access that will give me the totals by week for each person. My data contains a list a people with dates and amounts. Here is a small sample of the data over five weeks for one person. I tried to put it in as a table but it wouldn't let me so spaces are breaking the columns: Date Name Supervisor Manager Amt 03/08/2010 Agent1 Supv1 Mgr1 3 03/07/2010 Agent1 Supv1 Mgr1 5 03/04/2010 Agent1 Supv1 Mgr1 8 03/03/2010 Agent1 Supv1 Mgr1 5 03/01/2010 Agent1 Supv1 Mgr1 9 02/28/2010 Agent1 Supv1 Mgr1 1 02/15/2010 Agent1 Supv1 Mgr...

replacing values in cells from a pre-designed table
I am working with an Excel Spreadsheet. I have, in column C, a different value on each row: 1.2 or 0.7 or 2.5, etc. In column D I want to have Excel automatically place, in each row, a new value based on a reference table I would create. For example If column C value is Then column D value on same row will b 0.7 9 0.8 9 0.9 9 1.0 9 ...

How do I get an automatic reminder with a certain cell value? #2
Is there a way to receive a pop-up comment or text box when a certain thing is typed into a cell? For example, each time I type "new instrument", I want a reminder to pop-up like a comment would that says "Call Property Accounting". Is there a function like this in Excel? Assuming data entry cells are col. A, right-click the worksheet tab, choose "View Code", copy in the code below, and press ALT+Q to close VBE. Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim sMsg As String With Target If .Column > 1 Then Exit Sub If .Count > 1 Then Exit...

Insert spaces between pasted values #2
Hi! Is there a way to insert spaces between values that are pasted? For example, i have in rows 1,2,3 values a,b,c and i want to paste them into another list in rows 1,3,5 respectively (with a single-row space between pasted values). Thanx! M I presume you have more than just 3 rows. A general approach would be to use a helper column next to your data and to fill a simple sequence down that column for as much data as you have. Then copy all the numbers that make up that sequence and paste them immediately below the sequence in the same column, so that if you have 200 numbers for example the...