Sequence Number

  • Thread starter Thread starter Hasan
  • Start date Start date
H

Hasan

How do you use sequence number that would change on group
change. So its tied in with teh GROUP BY function so each
time the GROUP BY Field changes the number starts with 1
again.
This number isn't a primary key so using auto number is
useless.
Could you please tell me how to do this using either
another table or even through programming logic.
Thankyou
 
Dear Hasan:

I think you are asking to create a Rank (sequence number) that resets
according to each set of values in a GROUP.

SELECT Column1, Column2, . . .
(SELECT COUNT(*) + 1 FROM YourTable T1
WHERE T1.G1 = T.G1 AND T1.G2 = T.G2 . . .
AND T1.R < T.R) AS Rank
FROM YourTable T
ORDER BY G1, G2, . . ., R

In the above, substitute the name of your table for YourTable.

The columns G1, G2, . . . are the columns that define the group.

The column R is the column by which you are ranking. It is also
possible, but more complex, to rank by multiple columns. Let me know
if you need information on that.

The natural ranking starts from 0, so I have added 1 to the COUNT(*)
above so it starts from 1.

If you have difficulty implementing this, you may post the SQL to a
query that provides everything needed but the ranking. Also specify a
list of columns making up the GROUP and a list of columns by which to
count the ranking. I can probably build the rest for you.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Thanks for that,
So if i have a tabel called receipt with fields:
ID (Primary key)
shopno
receiptno
itemno
description
price

so at the moment itemno is a distinct number but now i'm
making a table where itemno should be a sequence number
1,2,3... rather then wat eva is in there at the moment.
so the itemno should increment for the same receiptno and
when the receiptno changes the itemno starts from 1 again.

so in access or sql what would the query be?
Thanks alot for your help
 
Dear Hasan:

The "Group" consists of only receiptno, and within each value of
receiptno the ranking is assigned in ascending order of itemno. There
will be duplicate rankings assigned then if any receipt contains the
same itemno more than once.

SELECT receiptno,
(SELECT COUNT(*) + 1 FROM receipt T1
WHERE T1.receiptno = T.receiptno
AND T1.itemno < T.itemno) AS Rank
FROM receipt T
ORDER BY receiptno, itemno

Does that begin to do what you want?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Yep thanks,
works a treat
-----Original Message-----
Dear Hasan:

The "Group" consists of only receiptno, and within each value of
receiptno the ranking is assigned in ascending order of itemno. There
will be duplicate rankings assigned then if any receipt contains the
same itemno more than once.

SELECT receiptno,
(SELECT COUNT(*) + 1 FROM receipt T1
WHERE T1.receiptno = T.receiptno
AND T1.itemno < T.itemno) AS Rank
FROM receipt T
ORDER BY receiptno, itemno

Does that begin to do what you want?

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




.
Click to expand...
 
Back
Top