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

 

[Ken Snell [MVP]]

You're close. Assuming that COL3 is a unique value for any combination of
COL1 and COL2 values:

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
ORDER BY T.COL3) AS Counter3
FROM Table1
ORDER BY Table1.COL1, Table1.COL2,
Counter3;

 

[robboll]

Ken,

When I try that it returns an error:

"You tried to execute a query that does not include the specific
expression 'T.COL3' as part of an aggregate function."

If the query works at your end, Do you think it might be a missing
library or something?

Thanks,

RBollinger

 

[Ken Snell [MVP]]

Oops... this was "air code for the SQL statement" and forgot that the ORDER
BY statement will make the query want to have the column in the GROUP BY
clause. Try this (still "air code"):

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
GROUP BY T.COL1, T.COL2, T.COL3
ORDER BY T.COL3) AS Counter3
FROM Table1
ORDER BY Table1.COL1, Table1.COL2,
Counter3;

 

[robboll]

Ken,

When I tried it the second time it first prompts me to:
"Enter Parameter Value" for Counter3

and when I click OK I then receive an MS Access error:
"At least one record can be returned by this subquery"

I appreciate your effort in helping me solve this.

RBollinger

 

[Ken Snell [MVP]]

I have tested this one < g >:

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;

 

[robboll]

Works like a dream! Thanks!

I have tested this one < g >:

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;

 

[robboll]

Ken,

Darn! My model examples don't fit what I am actually trying to
accomplish. When I try to apply it to actual data, COL3 is messing
things up since it has a lot of duplicates. This makes it necessary to
add a COL4. I modified the data in COL3 and added COL4 accordingly.


I tried to follow your logic by modifying your code as follows:

SELECT Table1.COL1, Table1.COL2, Table1.COL3, Table1.COL4, (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, Table1.COL4;

When I run it, this is the result:

COL1 COL2 COL3 COL4 Counter3
1 1 alpha alpha 3
1 1 alpha charley 3
1 1 alpha delta 3
1 1 bravo xray 4
1 2 alpha green book 1
1 2 bravo foxtrot 2
1 3 alpha tan ton 1
1 3 delta eat at Joe's 2
2 1 alpha tape 1
2 1 xray elf 2
2 2 bravo alf 3
2 2 bravo balloon 3
2 2 bravo narc closet 3
6 2 alpha lima 1
6 2 hotel november 2

I've been wrestling with the code to try to get this result:

COL1 COL2 COL3 COL4 Counter3
1 1 alpha alpha 1
1 1 alpha charley 2
1 1 alpha delta 3
1 1 bravo xray 4
1 2 alpha green book 1
1 2 bravo foxtrot 2
1 3 alpha tan ton 1
1 3 delta eat at Joe's 2
2 1 alpha tape 1
2 1 xray elf 2
2 2 bravo alf 1
2 2 bravo balloon 2
2 2 bravo narc closet 3
6 2 alpha lima 1
6 2 hotel november 2

It looks like in this case the select statement ignores columns 3 and
4. But the query orders them within the group (Col1 and Col2)

Something like?

SELECT Table1.COL1, Table1.COL2, Table1.COL3, Table1.COL4, (SELECT
Count(*) FROM Table1 AS T
WHERE T.COL1 = Table1.COL1 AND
T.COL2 <= Table1.COL2) As Counter3
FROM Table1
ORDER BY Table1.COL1, Table1.COL2, Table1.COL3, Table1.COL4;

I know this code doesn't work, but do you see what is wrong with it?

Thanks!

 

[Gary Walter]

SELECT
Table1.COL1,
Table1.COL2,
Table1.COL3,
Table1.COL4,
(SELECT Count(*) FROM Table1 AS T
WHERE
T.COL1 = Table1.COL1
AND
T.COL2 = Table1.COL2
AND
(T.COL3 & T.COL4) <= (Table1.COL3 & Table1.COL4)) AS Counter3
FROM Table1
ORDER BY
Table1.COL1,
Table1.COL2,
Table1.COL3,
Table1.COL4;

 

[robboll]

(T.COL3 & T.COL4) <= (Table1.COL3 & Table1.COL4)) AS Counter3

Awesome. Thank you