Subtracting values in a query

[LV]

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!

 

[KARL DEWEY]

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;

 

[Klatuu]

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;

 

[daniel gideon]

i have two tables one is the stock record table and the second one is the sales record table. both tables contain product quantity field, i want the product quantity field from the sales record to be subtracted from the product quantity field of the stock record table. any time a sale is made. help me pls.