substraction with same field values

  • Thread starter Thread starter sivancr
  • Start date Start date
S

sivancr

i need help!!! anyone please help!!! i have a query where i am entering whole
values in one field, with several registry's, and i need to know if there is
a way to substract the value from registry 2 from registry 1, i the same field
 
Hi sivancr,

yes, there is a way

what is the fieldname and data type for the registry number?

what is the fieldname and data type of what you want to subtract?

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*
 
field name is consumption, and it contains in each registry a numeric value,
lets say Reg 1 = 100, reg 2 = 200, reg 3 = 300
 
so the consumption field contains:

"Reg 1"
"Reg 2"
....
"Reg 99"
...

??

"what is the fieldname and data type of what you want to subtract?"


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*
 
Crystal,

Pardon me for jumping in.

WARNING: Language alert.

Registry = Row = Record

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Ok, What information in the table tells you which record is the previous
record. RECORDS do not have an inherent order. As John Vinson likes to say
records are like a bag of marbles, there is no order to them.

Do you have a date field or something else such as a sequential number or an
autonnumber that can be used to uniquely organize the records in order needed
to determine the previous record? IF not, there is no way to determine which
record is the previous record.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Here are two different queries to play with

SELECT A.Consumption
, (SELECT C.Consumption
FROM SomeTable As C
WHERE C.ShiftID = (SELECT Max(B.ShiftID)
FROM SomeTable as B
WHERE B.ShiftID < A.ShiftID)) as PriorValue
FROM SomeTable as A

OR you might try

Select A.Consumption
, (SELECT TOP 1 C.Consumption
FROM SomeTable As C
WHERE C.ShiftID < A.ShiftID
ORDER BY C.ShiftID) as PriorValue
FROM SomeTable as A


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Thanks, I'll try both and in case it doesn't work i'll cry my way over here,
if it does I'll let you know. kudos
 
Back
Top