Help needed filling 2 table columns 1

[VickyC]

hello

This is a small part of a larger problem that i'm having trouble with. Consider the columns shown in the UNFINISHED LEFT tables below. Moving down column X, each value is always >= the preceding value. The values in column Y are always 63 - the X value.

I'm having trouble correctly filling columns X_Freq and Y_Freq. Here are the rules for filling these columns: Each value in column X_Freq shows the number of times the X value appears in column X (for any given ID). For example, 22 appears 1 time, 26 appears 3 times, etc.

The next rule is a bit trickier. Each value in column Y_Freq shows the number of times the Y value appears IN COLUMN X . For example, 56 appears 0 times in column X, 52 appears 1 time, etc.

The finished table is shown below to the RIGHT.

  [b]
TABLE (UNFINISHED)                            TABLE (FINISHED)  [/b]
ID   Rank   X   X_Freq   Y   Y_Freq           ID   Rank   X   X_Freq     Y   Y_Freq
100   1      7          56                    100   1      7     1      56      0
100   2     11          52                    100   2     11     1      52      1
100   3     22          41                    100   3     22     1      41      0
100   4     26          37                    100   4     26     3      37      2
100   5     26          37                    100   5     26     3      37      2
100   6     26          37                    100   6     26     3      37      2
100   7     35          28                    100   7     35     1      28      0
100   8     37          26                    100   8     37     2      26      3
100   9     37          26                    100   9     37     2      26      3
100  10     52          11                    100  10     52     1      11      1
101 ...  etc ...                              101 ...  etc ...

Thank you in advance for any clues!
Vicky C.

 

[PHV]

A starting point:
SELECT ID, Rank, X, (SELECT Count(*) FROM Unfinished WHERE X=A.X) AS X_Freq
, Y, (SELECT Count(*) FROM Unfinished WHERE Y=A.X) AS Y_Freq
FROM Unfinished AS A

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[VickyC]

Thaks PH. That did the trick. Vicky C.