Need to increment a field based upon the value in a second

  • Thread starter Thread starter TS
  • Start date Start date
T

TS

Similar to Linda's Subject: Changing an Access Table to a Cobol
friendly format to load to a mainframe issue Date: 2003-10-15
18:37:54 PST

What I need is to add a value in a field that numbers the records
where field no 1 is the same.

Table:XYZ
Fields:User,Book,BookNo
Table XYZ
------------------------------------
User Book BookNo
John01 Free Willy 0001
John01 Punked 0002
Jan01 Punked 0001
Bill23 Air Tight 0001
Bill23 Free Willy 0002
Bill23 Punked 0003

The point is that the "BookNo" field will increment 1 if the "User"
field is the same for the next record. And start over at 0001 if the
"User" field changes.
The user field will have both Numbers and Letters combined. I
appreciate your answer to Linda, I am not sure how to apply it to my
problem. Thanks for helping somebody a "little" over his head.

Access 2000
Tony
 
Dear Tony:

Before you can assign a ranking number to the sequence of all rows
starting with John01, you must define an ordering by which to number
them. And, this ordering must be unique if the ranking is to be
unique.

I'll assume the ordering should be Book, then BookNo. There's no way
I can tell if this is the order you would choose, or if it would be
unique.

By unique, I mean simply that the combination User/Book/BookNo must
never repeat.

The query could then be written on this basis:

SELECT User, Book, BookNo,
(SELECT COUNT(*) + 1 From XYZ T1
WHERE T1.User = T.User AND (T1.Book < T.Book
OR (T1.Book = T.Book AND T1.BookNo < T.BookNo))
AS Rank
FROM XYZ T
ORDER BY User, Book, BookNo

If there are two rows in XYZ that have the same value of
User/Book/BookNo, then the ranking will not be unique. This is the
same way as in a horse race. If there is a tie for first place, then
2 horses will be first place finishers, and the next horse is third
place.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Tom, Thank you for your reply. That worked, but I need to take it one
step further.

For this I will use another example.

Table=XYZ
Fields=User, Payee, AcctNo, ID

User Payee AcctNo ID
BOB01 PGE 1234567 1
BOB01 PGE 7659838 2
BOB01 SBC 555-1298 3
BILL67 PGE 1238133 1
BILL67 SBC 555-1234 2
BILL67 SBC 555-1234 2

What I am trying to accomplish at this point is to create the values
for the fourth column (ID)
I need to be able to say that ID will increment 1 for every User when
the combination of User,Payee, and AccountNo are unique values.

I will thank you in advance for your thoughtful consideration of my
problem.
 
qUESTION 1 Would it be like this?

SELECT User, Payee, AcctID,(SELECT COUNT(*)+1 From XYZ T1 WHERE
T1.User = T.User AND(T1.Payee < T.Payee OR (T1.Payee = T.Payee AND
T1.AcctNo < T.AcctNo]))) AS Rank FROM XYZ T ORDER BY XYZ.User,
XYZ.Payee, XYZ.AcctNo;

Question 2 In Access, Rank would be the value for ID field?
 
Dear Tony:

Except for some name changes, this looks like the same thing:

SELECT User, Payee, AccountNo,
(SELECT COUNT(*) + 1 From XYZ T1
WHERE T1.User = T.User AND (T1.Payee < T.Payee
OR (T1.Payee = T.Payee AND T1.AcctNo < T.AcctNo))
AS ID
FROM XYZ T
ORDER BY User, Payee, AcctNo

Your example shows, in the last two rows, the circumstance where User,
Payee, and AcctNo are all the same, creating a tie in the ranking,
which you accurately portray in the ID column. What you don't show is
what would happen in the next row if it were for the same User but for
a different Payee and/or AcctNo. The ID value would then be 4,
skipping 3 because of the tie (just like a horse race).

This could be changed if necessary.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Tom Ellison said:
Your example shows, in the last two rows, the circumstance where User,
Payee, and AcctNo are all the same, creating a tie in the ranking,
which you accurately portray in the ID column. What you don't show is
what would happen in the next row if it were for the same User but for
a different Payee and/or AcctNo. The ID value would then be 4,
skipping 3 because of the tie (just like a horse race).

This could be changed if necessary.
Click to expand...

Your right of course.Lets do it.I'm using Sql Server 2000,
I'm sure your familiar with it:)
I'm also throwing in an identity (autonumber) column.

create table #TS
(rowno int identity,[User] varchar(10),Payee char(3),AcctNo varchar(10),[ID]
int null)
go
insert #TS ([User],Payee,AcctNo)Values('BOB01','PGE','1234567')
insert #TS ([User],Payee,AcctNo)Values('BOB01','PGE','7659838')
insert #TS ([User],Payee,AcctNo)Values('BOB01','SBC','555-1298')
insert #TS ([User],Payee,AcctNo)Values('BILL67','PGE','1238133')
insert #TS ([User],Payee,AcctNo)Values('BILL67','SBC','555-1234')
insert #TS ([User],Payee,AcctNo)Values('BILL67','SBC','555-1234')
-- Add extra row for 'BILL67'
insert #TS ([User],Payee,AcctNo)Values('BILL67','TLC','555-1234')

Your query and result:

SELECT [User], Payee, AcctNo,
(SELECT COUNT(*)+1 From #TS T1 WHERE
T1.[User] = T.[User] AND(T1.Payee < T.Payee OR (T1.Payee = T.Payee AND
T1.AcctNo < T.AcctNo))) AS Rank
FROM #TS T
ORDER BY t.[User],
t.Payee, t.AcctNo

User Payee AcctNo Rank
---------- ----- ---------- -----------
BILL67 PGE 1238133 1
BILL67 SBC 555-1234 2
BILL67 SBC 555-1234 2
BILL67 TLC 555-1234 4 <---
BOB01 PGE 1234567 1
BOB01 PGE 7659838 2
BOB01 SBC 555-1298 3

You can eliminate the ties with a Count(Distinct..)

SELECT [User], Payee, AcctNo,
(SELECT COUNT(Distinct cast([user] as varchar(10))+cast(Payee as
char(3))+cast(AcctNo as varchar(10)))+1 From #TS T1 WHERE
T1.[User] = T.[User] AND(T1.Payee < T.Payee OR (T1.Payee = T.Payee AND
T1.AcctNo < T.AcctNo))) AS Rank
FROM #TS T
ORDER BY t.[User],
t.Payee, t.AcctNo

(Notice that even the Server Count(Distinct..) is a half ass
implementation since it takes only one argument).

User Payee AcctNo Rank
---------- ----- ---------- -----------
BILL67 PGE 1238133 1
BILL67 SBC 555-1234 2
BILL67 SBC 555-1234 2
BILL67 TLC 555-1234 3 <--
BOB01 PGE 1234567 1
BOB01 PGE 7659838 2
BOB01 SBC 555-1298 3

Several points:
These queries are not easy to write.There's probably no
more than a dozen Access users who could do it:)
These kind of ranking queries become much easier to conceptualize
and code using the OLAP features of sql99 (ie.Row_number(),Rank()
and Dense_rank().Of course you won't find these features in Access
or even Sql Server 2000.You will find them in Oracle and DB2.

The RAC utility for S2k tries,among many other things, to simulate
the sql99 OLAP functions without having the user writing any sql code.
For example here's the above query in RAC:

Exec Rac
@transform='_dummy_',-- Not doing a crosstab.
-- Note here the identity column [rowno] is
-- used to make each row unique.(RAC works
-- on grouping).The sequence of columns in @rows
-- also implies the sort order.(There are other
-- options to sort).
@rows='[User] & Payee & AcctNo & [rowno]',
@pvtcol='Access Reports',
@from='#TS',
@grand_totals='n',@rowbreak='n',
-- This is the rank we want.It's a Dense_rank()
-- in sql99.The column name for it is 'test'.
@rowindicators='AcctNo{test}',
@defaultexceptions='dumy & [rowno]'


User Payee AcctNo test
------ ----- -------- -----
BILL67 PGE 1238133 1
BILL67 SBC 555-1234 2
BILL67 SBC 555-1234 2
BILL67 TLC 555-1234 3 <--
BOB01 PGE 1234567 1
BOB01 PGE 7659838 2
BOB01 SBC 555-1298 3

Just my $.02 :~)

For S2k check out RAC v2.2 and QALite @
www.rac4sql.net
 
Dear Rufus:

You make it all seem as easy as "Duck Soup"

Your points are well taken. In my experience, it is the duplicates in
the ranking that are most often an objection, not the skipping of the
ranking value after the duplicates, but this may help some of those
who are reading this.

Readers also often do not realize that when you sort your results by a
non-unique key set, you have failed to specify the order in which the
result will appear. Although it is probably that no implementations
of database engines would do so, it would be perfectly acceptable to
run such a query twice and get different results back-to-back. If two
rows do not sort uniquely, they may be presented in any random order
and the result is still correct. The ranking problem follows this
same fact, but makes it much more apparent.

In my experience (in the newsgroups), the skipped rank is usually
preferable if there are going to be duplicates, but in my own work
there are never any duplicates to start with. For this reason, the
DISTINCT is not generally preferable, as it introduces an extra but
unnecessary step, again assuming the key is unique. Having the engine
look for duplicates when you already know there aren't any would be a
waste of time.

One tiny change I would suggest. In your use of DISTINCT:

COUNT(Distinct cast([user] as varchar(10))+cast(Payee as
char(3))+cast(AcctNo as varchar(10)

I would recommend the cast() be only to char, not varchar. By keeping
the trailing spaces on all columns used, you avoid possible ambiguity.
For example:

user payee acctno
AB CD EF
A BC DEF

If you put all these as varchars, the two will look alike. ABCDEF
both times. If as char the trailing spaces will align the columns of
characters with the columns of data, preventing possible ambiguity.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

Tom Ellison said:
Your example shows, in the last two rows, the circumstance where User,
Payee, and AcctNo are all the same, creating a tie in the ranking,
which you accurately portray in the ID column. What you don't show is
what would happen in the next row if it were for the same User but for
a different Payee and/or AcctNo. The ID value would then be 4,
skipping 3 because of the tie (just like a horse race).

This could be changed if necessary.
Click to expand...

Your right of course.Lets do it.I'm using Sql Server 2000,
I'm sure your familiar with it:)
I'm also throwing in an identity (autonumber) column.

create table #TS
(rowno int identity,[User] varchar(10),Payee char(3),AcctNo varchar(10),[ID]
int null)
go
insert #TS ([User],Payee,AcctNo)Values('BOB01','PGE','1234567')
insert #TS ([User],Payee,AcctNo)Values('BOB01','PGE','7659838')
insert #TS ([User],Payee,AcctNo)Values('BOB01','SBC','555-1298')
insert #TS ([User],Payee,AcctNo)Values('BILL67','PGE','1238133')
insert #TS ([User],Payee,AcctNo)Values('BILL67','SBC','555-1234')
insert #TS ([User],Payee,AcctNo)Values('BILL67','SBC','555-1234')
-- Add extra row for 'BILL67'
insert #TS ([User],Payee,AcctNo)Values('BILL67','TLC','555-1234')

Your query and result:

SELECT [User], Payee, AcctNo,
(SELECT COUNT(*)+1 From #TS T1 WHERE
T1.[User] = T.[User] AND(T1.Payee < T.Payee OR (T1.Payee = T.Payee AND
T1.AcctNo < T.AcctNo))) AS Rank
FROM #TS T
ORDER BY t.[User],
t.Payee, t.AcctNo

User Payee AcctNo Rank
---------- ----- ---------- -----------
BILL67 PGE 1238133 1
BILL67 SBC 555-1234 2
BILL67 SBC 555-1234 2
BILL67 TLC 555-1234 4 <---
BOB01 PGE 1234567 1
BOB01 PGE 7659838 2
BOB01 SBC 555-1298 3

You can eliminate the ties with a Count(Distinct..)

SELECT [User], Payee, AcctNo,
(SELECT COUNT(Distinct cast([user] as varchar(10))+cast(Payee as
char(3))+cast(AcctNo as varchar(10)))+1 From #TS T1 WHERE
T1.[User] = T.[User] AND(T1.Payee < T.Payee OR (T1.Payee = T.Payee AND
T1.AcctNo < T.AcctNo))) AS Rank
FROM #TS T
ORDER BY t.[User],
t.Payee, t.AcctNo

(Notice that even the Server Count(Distinct..) is a half ass
implementation since it takes only one argument).

User Payee AcctNo Rank
---------- ----- ---------- -----------
BILL67 PGE 1238133 1
BILL67 SBC 555-1234 2
BILL67 SBC 555-1234 2
BILL67 TLC 555-1234 3 <--
BOB01 PGE 1234567 1
BOB01 PGE 7659838 2
BOB01 SBC 555-1298 3

Several points:
These queries are not easy to write.There's probably no
more than a dozen Access users who could do it:)
These kind of ranking queries become much easier to conceptualize
and code using the OLAP features of sql99 (ie.Row_number(),Rank()
and Dense_rank().Of course you won't find these features in Access
or even Sql Server 2000.You will find them in Oracle and DB2.

The RAC utility for S2k tries,among many other things, to simulate
the sql99 OLAP functions without having the user writing any sql code.
For example here's the above query in RAC:

Exec Rac
@transform='_dummy_',-- Not doing a crosstab.
-- Note here the identity column [rowno] is
-- used to make each row unique.(RAC works
-- on grouping).The sequence of columns in @rows
-- also implies the sort order.(There are other
-- options to sort).
@rows='[User] & Payee & AcctNo & [rowno]',
@pvtcol='Access Reports',
@from='#TS',
@grand_totals='n',@rowbreak='n',
-- This is the rank we want.It's a Dense_rank()
-- in sql99.The column name for it is 'test'.
@rowindicators='AcctNo{test}',
@defaultexceptions='dumy & [rowno]'


User Payee AcctNo test
------ ----- -------- -----
BILL67 PGE 1238133 1
BILL67 SBC 555-1234 2
BILL67 SBC 555-1234 2
BILL67 TLC 555-1234 3 <--
BOB01 PGE 1234567 1
BOB01 PGE 7659838 2
BOB01 SBC 555-1298 3

Just my $.02 :~)

For S2k check out RAC v2.2 and QALite @
www.rac4sql.net
Click to expand...
 
Tom Ellison" said:
Dear Rufus:
You make it all seem as easy as "Duck Soup"
Click to expand...

Touche! A most important connection:~)
Your points are well taken. In my experience, it is the duplicates in
the ranking that are most often an objection, not the skipping of the
ranking value after the duplicates, but this may help some of those
who are reading this.
Click to expand...

One can hope:)
Readers also often do not realize that when you sort your results by a
non-unique key set, you have failed to specify the order in which the
result will appear. Although it is probably that no implementations
of database engines would do so, it would be perfectly acceptable to
run such a query twice and get different results back-to-back. If two
rows do not sort uniquely, they may be presented in any random order
and the result is still correct. The ranking problem follows this
same fact, but makes it much more apparent.
Click to expand...

Indeed.In both sql99 OLAP functions and RAC a unique key
(ie. autonumber/identity) can solve the problem of a non deterministic,if
you will,
sort of a non-unique key set.You could use 'rowno' (autonumber) in
your query.Of course this does not come without some pain:).
In my experience (in the newsgroups), the skipped rank is usually
preferable if there are going to be duplicates, but in my own work
there are never any duplicates to start with. For this reason, the
DISTINCT is not generally preferable, as it introduces an extra but
unnecessary step, again assuming the key is unique. Having the engine
look for duplicates when you already know there aren't any would be a
waste of time.
Click to expand...

Of course prior knowledge of your data is advantageous.But it is the
ability of sql99 OLAP and the intention of RAC to construct *any type*
of rank/ranking logic as easily as possible.When one only has
sql89/sql92 to work with, IMHO this puts the *average* user of
Jet and even Sql Server at a distinct disadvantage in constructing
the appropriate sql syntax.Everyone *knows* sql, but few have the
necessary insight to make it sing.I'm with C.J.Date on sql: 'It's a mess'.
Anything to simply it is a plus.The everpresent "learn to think in terms
of sets" should not take precedence over how to conceptualize
a problem.The two concepts lead most to the ever present
"Please Help Me",ie please do this for me.Sql99 is a big step
forward because it relaxes the burden of complex *set* coding
and ironically allows thinking more in terms of *rows* which most
people find much more intuitive.There is no reason to think an
optimizer can't handle rows in this context:).Of course the idea that
"a database is optimized to handle sets" as opposed to row by
row processing must be galling to many.Those that work for living
will appreciate much of sql99.Those that teach will develope exta
bile from it:).Of course these same people cannot stand RAC
as it tries to hide as much dirty work as humanly possible:).It would
be a shame if Yukon does not have much of sql99.But MS'merized
users wouldn't know what there missing:).Server is years behind its
major competitors and it will be interesting to see just what their
RTM version of Yukon will contain.Report services,CLR and workbench
will not make up for major omissions of sql99 functions.And from what
I've seen the new Pivot/Unpivot does not demonstrate they've
learned much from RAC:).
One tiny change I would suggest. In your use of DISTINCT:

COUNT(Distinct cast([user] as varchar(10))+cast(Payee as
char(3))+cast(AcctNo as varchar(10)

I would recommend the cast() be only to char, not varchar. By keeping
the trailing spaces on all columns used, you avoid possible ambiguity.
For example:

user payee acctno
AB CD EF
A BC DEF

If you put all these as varchars, the two will look alike. ABCDEF
both times. If as char the trailing spaces will align the columns of
characters with the columns of data, preventing possible ambiguity.
Click to expand...

What's the word I'm searching for...how bout stupid.Hope people
understand your point.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
Click to expand...

Best from:
RTF,Dr. Wolf J. Flywheel, Gloria Teasdale,Zeppo and all the rest:).
 
rtf,
Best from:
RTF,Dr. Wolf J. Flywheel, Gloria Teasdale,Zeppo and all the
rest:).
Click to expand...

Did I hear my name mentioned? :)

It would
be a shame if Yukon does not have much of sql99.But MS'merized
users wouldn't know what there missing:).Server is years behind
its major competitors and it will be interesting to see just what
their RTM version of Yukon will contain.Report services,CLR and
workbench will not make up for major omissions of sql99 functions.
And from what I've seen the new Pivot/Unpivot does not demonstrate
they've learned much from RAC:).
Click to expand...

Relax, all your favorite functions are coming. :)

http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/next/DWSQLSY.asp

Search on the page for dense_rank.

The word in the street is that these functions will be available in
beta 2. My tip: Sign up for beta 2 and piddle to your heart's
content.

:)

md
 
Gloria Teasdale" said:
The word in the street is that these functions will be available in
beta 2. My tip: Sign up for beta 2 and piddle to your heart's
content.
Click to expand...

Tip top.

RTF but not for long.
 
Mr. Driftwood! Mr. Driftwood! Paging Mr. Otis B. Driftwood!

(inside humor, "Night at the Opera")

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Hi,



SELECT a.user, a.Payee, a.AcctNo, COUNT(*) As rank
FROM t1 As a ,
(SELECT DISTINCT User, Payee, AcctNo FROM t1) As b
WHERE (a.user> b.user)
OR (a.user = b.user AND a.payee>b.payee)
OR (a.user = b.user AND a.ayee=b.payee AND a.AcctNo>=b.AcctNo )
GROUP BY a.user, a.Payee, a.AcctNo



can be done "graphically", and probably by "tons" of people...



Hoping it may help,
Vanderghast, Access MVP



Rufus T. Firefly said:
Tom Ellison said:
Your example shows, in the last two rows, the circumstance where User,
Payee, and AcctNo are all the same, creating a tie in the ranking,
which you accurately portray in the ID column. What you don't show is
what would happen in the next row if it were for the same User but for
a different Payee and/or AcctNo. The ID value would then be 4,
skipping 3 because of the tie (just like a horse race).

This could be changed if necessary.
Click to expand...

Your right of course.Lets do it.I'm using Sql Server 2000,
I'm sure your familiar with it:)
I'm also throwing in an identity (autonumber) column.

create table #TS
(rowno int identity,[User] varchar(10),Payee char(3),AcctNo varchar(10),[ID]
int null)
go
insert #TS ([User],Payee,AcctNo)Values('BOB01','PGE','1234567')
insert #TS ([User],Payee,AcctNo)Values('BOB01','PGE','7659838')
insert #TS ([User],Payee,AcctNo)Values('BOB01','SBC','555-1298')
insert #TS ([User],Payee,AcctNo)Values('BILL67','PGE','1238133')
insert #TS ([User],Payee,AcctNo)Values('BILL67','SBC','555-1234')
insert #TS ([User],Payee,AcctNo)Values('BILL67','SBC','555-1234')
-- Add extra row for 'BILL67'
insert #TS ([User],Payee,AcctNo)Values('BILL67','TLC','555-1234')

Your query and result:

SELECT [User], Payee, AcctNo,
(SELECT COUNT(*)+1 From #TS T1 WHERE
T1.[User] = T.[User] AND(T1.Payee < T.Payee OR (T1.Payee = T.Payee AND
T1.AcctNo < T.AcctNo))) AS Rank
FROM #TS T
ORDER BY t.[User],
t.Payee, t.AcctNo

User Payee AcctNo Rank
---------- ----- ---------- -----------
BILL67 PGE 1238133 1
BILL67 SBC 555-1234 2
BILL67 SBC 555-1234 2
BILL67 TLC 555-1234 4 <---
BOB01 PGE 1234567 1
BOB01 PGE 7659838 2
BOB01 SBC 555-1298 3

You can eliminate the ties with a Count(Distinct..)

SELECT [User], Payee, AcctNo,
(SELECT COUNT(Distinct cast([user] as varchar(10))+cast(Payee as
char(3))+cast(AcctNo as varchar(10)))+1 From #TS T1 WHERE
T1.[User] = T.[User] AND(T1.Payee < T.Payee OR (T1.Payee = T.Payee AND
T1.AcctNo < T.AcctNo))) AS Rank
FROM #TS T
ORDER BY t.[User],
t.Payee, t.AcctNo

(Notice that even the Server Count(Distinct..) is a half ass
implementation since it takes only one argument).

User Payee AcctNo Rank
---------- ----- ---------- -----------
BILL67 PGE 1238133 1
BILL67 SBC 555-1234 2
BILL67 SBC 555-1234 2
BILL67 TLC 555-1234 3 <--
BOB01 PGE 1234567 1
BOB01 PGE 7659838 2
BOB01 SBC 555-1298 3

Several points:
These queries are not easy to write.There's probably no
more than a dozen Access users who could do it:)
These kind of ranking queries become much easier to conceptualize
and code using the OLAP features of sql99 (ie.Row_number(),Rank()
and Dense_rank().Of course you won't find these features in Access
or even Sql Server 2000.You will find them in Oracle and DB2.

The RAC utility for S2k tries,among many other things, to simulate
the sql99 OLAP functions without having the user writing any sql code.
For example here's the above query in RAC:

Exec Rac
@transform='_dummy_',-- Not doing a crosstab.
-- Note here the identity column [rowno] is
-- used to make each row unique.(RAC works
-- on grouping).The sequence of columns in @rows
-- also implies the sort order.(There are other
-- options to sort).
@rows='[User] & Payee & AcctNo & [rowno]',
@pvtcol='Access Reports',
@from='#TS',
@grand_totals='n',@rowbreak='n',
-- This is the rank we want.It's a Dense_rank()
-- in sql99.The column name for it is 'test'.
@rowindicators='AcctNo{test}',
@defaultexceptions='dumy & [rowno]'


User Payee AcctNo test
------ ----- -------- -----
BILL67 PGE 1238133 1
BILL67 SBC 555-1234 2
BILL67 SBC 555-1234 2
BILL67 TLC 555-1234 3 <--
BOB01 PGE 1234567 1
BOB01 PGE 7659838 2
BOB01 SBC 555-1298 3

Just my $.02 :~)

For S2k check out RAC v2.2 and QALite @
www.rac4sql.net
Click to expand...
 
Hi,

well, except that it does not work (shame on me ) unless you add an
inner join between the table and that result...well... at least I tried.


Vanderghast, Access MVP


Michel Walsh said:
Hi,



SELECT a.user, a.Payee, a.AcctNo, COUNT(*) As rank
FROM t1 As a ,
(SELECT DISTINCT User, Payee, AcctNo FROM t1) As b
WHERE (a.user> b.user)
OR (a.user = b.user AND a.payee>b.payee)
OR (a.user = b.user AND a.ayee=b.payee AND ..AcctNo>=b.AcctNo )
GROUP BY a.user, a.Payee, a.AcctNo



can be done "graphically", and probably by "tons" of people...



Hoping it may help,
Vanderghast, Access MVP



Rufus T. Firefly said:
Tom Ellison said:
Your example shows, in the last two rows, the circumstance where User,
Payee, and AcctNo are all the same, creating a tie in the ranking,
which you accurately portray in the ID column. What you don't show is
what would happen in the next row if it were for the same User but for
a different Payee and/or AcctNo. The ID value would then be 4,
skipping 3 because of the tie (just like a horse race).

This could be changed if necessary.
Click to expand...

Your right of course.Lets do it.I'm using Sql Server 2000,
I'm sure your familiar with it:)
I'm also throwing in an identity (autonumber) column.

create table #TS
(rowno int identity,[User] varchar(10),Payee char(3),AcctNo varchar(10),[ID]
int null)
go
insert #TS ([User],Payee,AcctNo)Values('BOB01','PGE','1234567')
insert #TS ([User],Payee,AcctNo)Values('BOB01','PGE','7659838')
insert #TS ([User],Payee,AcctNo)Values('BOB01','SBC','555-1298')
insert #TS ([User],Payee,AcctNo)Values('BILL67','PGE','1238133')
insert #TS ([User],Payee,AcctNo)Values('BILL67','SBC','555-1234')
insert #TS ([User],Payee,AcctNo)Values('BILL67','SBC','555-1234')
-- Add extra row for 'BILL67'
insert #TS ([User],Payee,AcctNo)Values('BILL67','TLC','555-1234')

Your query and result:

SELECT [User], Payee, AcctNo,
(SELECT COUNT(*)+1 From #TS T1 WHERE
T1.[User] = T.[User] AND(T1.Payee < T.Payee OR (T1.Payee = T.Payee AND
T1.AcctNo < T.AcctNo))) AS Rank
FROM #TS T
ORDER BY t.[User],
t.Payee, t.AcctNo

User Payee AcctNo Rank
---------- ----- ---------- -----------
BILL67 PGE 1238133 1
BILL67 SBC 555-1234 2
BILL67 SBC 555-1234 2
BILL67 TLC 555-1234 4 <---
BOB01 PGE 1234567 1
BOB01 PGE 7659838 2
BOB01 SBC 555-1298 3

You can eliminate the ties with a Count(Distinct..)

SELECT [User], Payee, AcctNo,
(SELECT COUNT(Distinct cast([user] as varchar(10))+cast(Payee as
char(3))+cast(AcctNo as varchar(10)))+1 From #TS T1 WHERE
T1.[User] = T.[User] AND(T1.Payee < T.Payee OR (T1.Payee = T.Payee AND
T1.AcctNo < T.AcctNo))) AS Rank
FROM #TS T
ORDER BY t.[User],
t.Payee, t.AcctNo

(Notice that even the Server Count(Distinct..) is a half ass
implementation since it takes only one argument).

User Payee AcctNo Rank
---------- ----- ---------- -----------
BILL67 PGE 1238133 1
BILL67 SBC 555-1234 2
BILL67 SBC 555-1234 2
BILL67 TLC 555-1234 3 <--
BOB01 PGE 1234567 1
BOB01 PGE 7659838 2
BOB01 SBC 555-1298 3

Several points:
These queries are not easy to write.There's probably no
more than a dozen Access users who could do it:)
These kind of ranking queries become much easier to conceptualize
and code using the OLAP features of sql99 (ie.Row_number(),Rank()
and Dense_rank().Of course you won't find these features in Access
or even Sql Server 2000.You will find them in Oracle and DB2.

The RAC utility for S2k tries,among many other things, to simulate
the sql99 OLAP functions without having the user writing any sql code.
For example here's the above query in RAC:

Exec Rac
@transform='_dummy_',-- Not doing a crosstab.
-- Note here the identity column [rowno] is
-- used to make each row unique.(RAC works
-- on grouping).The sequence of columns in @rows
-- also implies the sort order.(There are other
-- options to sort).
@rows='[User] & Payee & AcctNo & [rowno]',
@pvtcol='Access Reports',
@from='#TS',
@grand_totals='n',@rowbreak='n',
-- This is the rank we want.It's a Dense_rank()
-- in sql99.The column name for it is 'test'.
@rowindicators='AcctNo{test}',
@defaultexceptions='dumy & [rowno]'


User Payee AcctNo test
------ ----- -------- -----
BILL67 PGE 1238133 1
BILL67 SBC 555-1234 2
BILL67 SBC 555-1234 2
BILL67 TLC 555-1234 3 <--
BOB01 PGE 1234567 1
BOB01 PGE 7659838 2
BOB01 SBC 555-1298 3

Just my $.02 :~)

For S2k check out RAC v2.2 and QALite @
www.rac4sql.net
Click to expand...
Click to expand...
 
Michel Walsh said:
well, except that it does not work (shame on me ) unless you add an
inner join between the table and that result...well... at least I tried.
Click to expand...

No shame, your effort was noble:).
:)

My point is that things like this *should* be done easily
by "tons" of people..." :)

I hope you will consider joining in on a future group RAC chat session.
What would be good time for you given USA Eastern Standard Time?
(We use Yahoo chat.If you have any other suggestion(s) please share).

Happy Holidays,
Steve aka O.B.Driftwood
 
wow!, Have tried the query posted by Tom, worked great, but is too slow,
any way to improve the performance?
 
Back
Top