Sequencing Problem using an MS Access Query

[robboll]

Here is a sequencing problem for MS Access (or I guess T-SQL) if
someone can assist.

I am trying to build a function that counts the members of the group.
In this case, the group consists of COL1 and COL2, and the members are
in COL3 (alpha order)

Table1

COL1 COL2 COL3 Counter
1 1 a
1 1 c
1 1 d
1 2 a
1 2 f
1 2 g
1 2 j
1 3 b
1 3 g
2 1 a
2 1 i
2 2 e
2 2 h
2 2 k


I found some code that looked promising and tried to apply it
with catastrophic failure!

SELECT Col1, Col2, Counter
(SELECT COUNT(*) + 1 FROM table1 T1
WHERE T1.COL1 = T.G1 AND T1.G2 = T.G2
AND T1.R < T.R) AS Counter
FROM Table1 T
ORDER BY G1, G2, . . ., R


The query result should be like this:

COL1 COL2 COL3 Rnk
1 1 a 1
1 1 c 2
1 1 d 3
1 2 a 1
1 2 f 2
1 2 g 3
1 2 j 4
1 3 b 1
1 3 g 2
2 1 a 1
2 1 i 2
2 2 e 1
2 2 h 2
2 2 k 3

Thanks for any help with this one.

RBollinger

[jimfortune@compumarc.com]

Re: Sequencing Problem using an MS Access Query

robboll wrote:[color=blue]
> Here is a sequencing problem for MS Access (or I guess T-SQL) if
> someone can assist.
>
> I am trying to build a function that counts the members of the group.
> In this case, the group consists of COL1 and COL2, and the members are
> in COL3 (alpha order)
>
> Table1
>
> COL1 COL2 COL3 Counter
> 1 1 a
> 1 1 c
> 1 1 d
> 1 2 a
> 1 2 f
> 1 2 g
> 1 2 j
> 1 3 b
> 1 3 g
> 2 1 a
> 2 1 i
> 2 2 e
> 2 2 h
> 2 2 k
>
>
> I found some code that looked promising and tried to apply it
> with catastrophic failure!
>
> SELECT Col1, Col2, Counter
> (SELECT COUNT(*) + 1 FROM table1 T1
> WHERE T1.COL1 = T.G1 AND T1.G2 = T.G2
> AND T1.R < T.R) AS Counter
> FROM Table1 T
> ORDER BY G1, G2, . . ., R
>
>
> The query result should be like this:
>
> COL1 COL2 COL3 Rnk
> 1 1 a 1
> 1 1 c 2
> 1 1 d 3
> 1 2 a 1
> 1 2 f 2
> 1 2 g 3
> 1 2 j 4
> 1 3 b 1
> 1 3 g 2
> 2 1 a 1
> 2 1 i 2
> 2 2 e 1
> 2 2 h 2
> 2 2 k 3
>
> Thanks for any help with this one.
>
> RBollinger[/color]

See:

http://groups-beta.google.com/group/comp.databases.ms-access/msg/7fb6eb508f22fa9d?hl=en&

under RankingWithinGr oup inside of qryRankForMedia n.

Your COL2 seems to correlate with the Groups field.

Post back if you need more help.

James A. Fortune

[jimfortune@compumarc.com]

Re: Sequencing Problem using an MS Access Query

jimfortune@comp umarc.com wrote:
[color=blue]
>
> See:
>
> http://groups-beta.google.com/group/...f22fa9d?hl=en&
>
> under RankingWithinGr oup inside of qryRankForMedia n.
>
> Your COL2 seems to correlate with the Groups field.
>
> Post back if you need more help.
>
> James A. Fortune[/color]

Ignore my post. The OP also posted in another newsgroup and obtained
the following answer:
[color=blue]
>I have tested this one < g >:[/color]
[color=blue]
>SELECT Table1.COL1, Table1.COL2, Table1.COL3,
>(SELECT Count(*) FROM Table1 AS T
>WHERE T.COL1 = Table1.COL1 AND
>T.COL2 = Table1.COL2 AND
>T.COL3 <= Table1.COL3) AS Counter3
>FROM Table1
>ORDER BY Table1.COL1, Table1.COL2, Table1.COL3;[/color]
[color=blue]
>-- Ken Snell <MS ACCESS MVP>[/color]

James A. Fortune