How can I calculate difference value

[Guest]

I have a table contain of 3 field like this (All data is number)

ID Ind Adj
1 I1 A1
2 I2 A2
3 I3 A3
4 I4 A4

In fact Ihave n record and I want to get the result like this

IDnew Dev
1 I2 - A1
2 I3 - A2
3 I4 - A3

Dev = Difference value between ind and adj, but it is not in a same record
How can i get it . Help me please, It is a big problem for me

 

[Marshall Barton]

I have a table contain of 3 field like this (All data is number)

ID Ind Adj
1 I1 A1
2 I2 A2
3 I3 A3
4 I4 A4

In fact Ihave n record and I want to get the result like this

IDnew Dev
1 I2 - A1
2 I3 - A2
3 I4 - A3

Dev = Difference value between ind and adj, but it is not in a same record

If your ID field is as sequential as your example indicates:

SELECT T1.ID, T2.Ind - T1.Adj As Dev
FROM table As T1
INNER JOIN (SELECT X.Ind FROM table As X) As T2
ON T1.ID = T2.ID -1

 

[John Spencer]

I would suggest

SELECT T2.ID As IDNew, T2.Ind - T1.Adj As Dev
FROM table As T1
INNER JOIN Table as T2
ON T1.ID = T2.ID -1

Marshall,
I don't understand how your query would work. Can you explain? For one
thing I see you joining the table and the subquery on a value that is not in
the subquery (ID).

SELECT T1.ID, T2.Ind - T1.Adj As Dev
FROM table As T1
INNER JOIN (SELECT X.Ind FROM table As X) As T2
ON T1.ID = T2.ID -1

Nova,
Both Marshall and I have made the assumption that ID is sequential with no
gaps. If that is not the case, post back.

 

[Marshall Barton]

You're right John. I'm glad you were monitoring this
thread. I started to work this out for non-consequitive IDs
and changes horses in midstream after I got dragged off to
fix something else.

 

[Guest]

I don't understand your answer John and Marshall
Can you tell me what These are
T1, T2, and TABLE
Because I have only one table, If you assign my table name is T1,
then where T2 and Table come from?
Thankyou verymuch

"Marshall Barton" เขียน:

You're right John. I'm glad you were monitoring this
thread. I started to work this out for non-consequitive IDs
and changes horses in midstream after I got dragged off to
fix something else.
--
Marsh
MVP [MS Access]

I would suggest

SELECT T2.ID As IDNew, T2.Ind - T1.Adj As Dev
FROM table As T1
INNER JOIN Table as T2
ON T1.ID = T2.ID -1

Marshall,
I don't understand how your query would work. Can you explain? For one
thing I see you joining the table and the subquery on a value that is not in
the subquery (ID).

SELECT T1.ID, T2.Ind - T1.Adj As Dev
FROM table As T1
INNER JOIN (SELECT X.Ind FROM table As X) As T2
ON T1.ID = T2.ID -1

Nova,
Both Marshall and I have made the assumption that ID is sequential with no
gaps. If that is not the case, post back.

 

[Randy Harris]

Pardon my butting in here, perhaps I can be a little help.

In the code that Marsh and John suggested, "Table" is the name of your
table. The one containing the three fields - ID, Ind and Adj. T1 and T2
are just temporary names that are assigned for the use of the query (they
are called aliases).

If you want, just paste the code into the SQL page of the query builder and
change the word "Table" to the actual name of your table (2 places), then
run the query.

Note, they said that this particular approach will only work if the ID's in
your table are sequential.

I hope this is of some help.

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.

I don't understand your answer John and Marshall
Can you tell me what These are
T1, T2, and TABLE
Because I have only one table, If you assign my table name is T1,
then where T2 and Table come from?
Thankyou verymuch

"Marshall Barton" เขียน:

You're right John. I'm glad you were monitoring this
thread. I started to work this out for non-consequitive IDs
and changes horses in midstream after I got dragged off to
fix something else.
--
Marsh
MVP [MS Access]

I would suggest

SELECT T2.ID As IDNew, T2.Ind - T1.Adj As Dev
FROM table As T1
INNER JOIN Table as T2
ON T1.ID = T2.ID -1

Marshall,
I don't understand how your query would work. Can you explain? For one
thing I see you joining the table and the subquery on a value that is not in
the subquery (ID).

SELECT T1.ID, T2.Ind - T1.Adj As Dev
FROM table As T1
INNER JOIN (SELECT X.Ind FROM table As X) As T2
ON T1.ID = T2.ID -1

Nova,
Both Marshall and I have made the assumption that ID is sequential with no
gaps. If that is not the case, post back.


Nova wrote:
I have a table contain of 3 field like this (All data is number)

ID Ind Adj
1 I1 A1
2 I2 A2
3 I3 A3
4 I4 A4

In fact Ihave n record and I want to get the result like this

IDnew Dev
1 I2 - A1
2 I3 - A2
3 I4 - A3

Dev = Difference value between ind and adj, but it is not in a same

record

 

[Guest]

It worked !!!, In fact I don't know about aliases before and my ID field is
not sequential, however I will try it by myself first
Thankyou very much for everybody to help me Thank you

"Randy Harris" เขียน:

Pardon my butting in here, perhaps I can be a little help.

In the code that Marsh and John suggested, "Table" is the name of your
table. The one containing the three fields - ID, Ind and Adj. T1 and T2
are just temporary names that are assigned for the use of the query (they
are called aliases).

If you want, just paste the code into the SQL page of the query builder and
change the word "Table" to the actual name of your table (2 places), then
run the query.

Note, they said that this particular approach will only work if the ID's in
your table are sequential.

I hope this is of some help.

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.

I don't understand your answer John and Marshall
Can you tell me what These are
T1, T2, and TABLE
Because I have only one table, If you assign my table name is T1,
then where T2 and Table come from?
Thankyou verymuch

"Marshall Barton" เขียน:

You're right John. I'm glad you were monitoring this
thread. I started to work this out for non-consequitive IDs
and changes horses in midstream after I got dragged off to
fix something else.
--
Marsh
MVP [MS Access]


John Spencer wrote:
I would suggest

SELECT T2.ID As IDNew, T2.Ind - T1.Adj As Dev
FROM table As T1
INNER JOIN Table as T2
ON T1.ID = T2.ID -1

Marshall,
I don't understand how your query would work. Can you explain? For one
thing I see you joining the table and the subquery on a value that is not in
the subquery (ID).

SELECT T1.ID, T2.Ind - T1.Adj As Dev
FROM table As T1
INNER JOIN (SELECT X.Ind FROM table As X) As T2
ON T1.ID = T2.ID -1

Nova,
Both Marshall and I have made the assumption that ID is sequential with no
gaps. If that is not the case, post back.


Nova wrote:
I have a table contain of 3 field like this (All data is number)

ID Ind Adj
1 I1 A1
2 I2 A2
3 I3 A3
4 I4 A4

In fact Ihave n record and I want to get the result like this

IDnew Dev
1 I2 - A1
2 I3 - A2
3 I4 - A3

Dev = Difference value between ind and adj, but it is not in a same record