Relative data within one table

[Guest]

Hi,

I have a table with 3 columns: wkDate, ID, Value.

These might look like this
1/5/90 10 55.34
1/5/90 20 98.42
1/5/90 30 -22.11
etc...

What I would like to do is use one of the ID as the basis, and calculate the
others relative to the base value (say A - B).

For example, if the ID was the basis, then the above sample would like
1/5/90 10 0
1/5/90 20 43.08
1/5/90 30 -77.45

I guess once these relative values are calulated, I could not show the basis
group so I don't get all the zeros. I'm having a hard time figuring out how
to select the basis values and then make the other values relative to that.

I tried adding the table twice and joining them and use the base from one
and the values from the other, but I couldn't get anything close to working
properly.

Thank you for your assistance.

Regards,

Kohai

 

[Marshall Barton]

I have a table with 3 columns: wkDate, ID, Value.

These might look like this
1/5/90 10 55.34
1/5/90 20 98.42
1/5/90 30 -22.11
etc...

What I would like to do is use one of the ID as the basis, and calculate the
others relative to the base value (say A - B).

For example, if the ID was the basis, then the above sample would like
1/5/90 10 0
1/5/90 20 43.08
1/5/90 30 -77.45

I guess once these relative values are calulated, I could not show the basis
group so I don't get all the zeros. I'm having a hard time figuring out how
to select the basis values and then make the other values relative to that.

Use a subquery to calculate/select the base value. E.g:

SELECT table.wkDate, table.ID, table.[Value]
-(SELECT Avg(X.[Value])
FROM table As X)
FROM table