substraction with same field values

[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

 

[strive4peace]

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
*

 

[Jerry Whittle]

Hi,

I'm a little confused about what you mean. Please provide an example or two.

 

[sivancr]

field name is consumption, and it contains in each registry a numeric value,
lets say Reg 1 = 100, reg 2 = 200, reg 3 = 300

 

[strive4peace]

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
*

 

[John Spencer (MVP)]

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

 

[strive4peace]

Hi John,

oh please continue, I'm getting out of the deep end

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
*

 

[sivancr]

John nailed it

Hi John,

oh please continue, I'm getting out of the deep end

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
*

 

[John Spencer (MVP)]

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

 

[sivancr]

well i have a shiftid field which is autonumeric

 

[John Spencer (MVP)]

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

 

[sivancr]

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