How can I calculate difference value

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Nova - 09 Jan 2006 02:45 GMT

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

Reply to this Message

Marshall Barton - 09 Jan 2006 03:34 GMT

>I have a table contain of 3 field like this (All data is number)
>
[quoted text clipped - 12 lines]
>
>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

Signature

Marsh
MVP [MS Access]

Reply to this Message

John Spencer - 09 Jan 2006 12:47 GMT

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.

>>I have a table contain of 3 field like this (All data is number)
>>
[quoted text clipped - 19 lines]
> INNER JOIN (SELECT X.Ind FROM table As X) As T2
> ON T1.ID = T2.ID -1

Reply to this Message

Marshall Barton - 09 Jan 2006 15:47 GMT

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.

Signature

Marsh
MVP [MS Access]

>I would suggest
>
[quoted text clipped - 33 lines]
>>>
>>>Dev = Difference value between ind and adj, but it is not in a same record

Reply to this Message

Nova - 10 Jan 2006 03:12 GMT

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
[quoted text clipped - 37 lines]
> >>>
> >>>Dev = Difference value between ind and adj, but it is not in a same record

Reply to this Message

Randy Harris - 10 Jan 2006 05:37 GMT

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.

Signature

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
[quoted text clipped - 46 lines]
> > >>>
> > >>>Dev = Difference value between ind and adj, but it is not in a same record

Reply to this Message

Nova - 11 Jan 2006 05:56 GMT

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.
>
[quoted text clipped - 66 lines]
> > > >>>Dev = Difference value between ind and adj, but it is not in a same
> record

Reply to this Message