Restart numbering

  • Thread starter Thread starter CJ
  • Start date Start date
C

CJ

Hi groupies.

My issue is that I am not sure how to restart numbering.

I have a database that will keep track of all of the jobs that we look
after. Every day, a job report needs to be created. For each new job, I
would like the job reports to start numbering at 1.

For example:

Job Report
A 1
A 2
A 3
B 1
B 2
C 1
C 2
C 3

I know that this can be done, I just can not get it straight in my head.

Could somebody please help me out?
TIA
CJ
 
Here's an example of generating sequential numbers on the fly in a
query. It works in the Northwind sample database. It returns the order
details, sorted by OrderID and ProductID, with a sequential number that
starts at 1 for each OrderID:

SELECT
(SELECT COUNT(C.OrderID)
FROM [Order Details] AS C
WHERE C.OrderID = A.OrderID
AND C.ProductID <= A.ProductID) AS Seq,
A.OrderID,
A.ProductID,
A.UnitPrice,
A.Quantity
FROM [Order Details] AS A
ORDER BY A.OrderID, A.ProductID
;
 
Wow, I didn't realize you could create a table like that. Very informative.
I gave it a try and it worked, so I should be able to make it work for my
needs as well.

Thanks John.

John Nurick said:
Here's an example of generating sequential numbers on the fly in a
query. It works in the Northwind sample database. It returns the order
details, sorted by OrderID and ProductID, with a sequential number that
starts at 1 for each OrderID:

SELECT
(SELECT COUNT(C.OrderID)
FROM [Order Details] AS C
WHERE C.OrderID = A.OrderID
AND C.ProductID <= A.ProductID) AS Seq,
A.OrderID,
A.ProductID,
A.UnitPrice,
A.Quantity
FROM [Order Details] AS A
ORDER BY A.OrderID, A.ProductID
;

Hi groupies.

My issue is that I am not sure how to restart numbering.

I have a database that will keep track of all of the jobs that we look
after. Every day, a job report needs to be created. For each new job, I
would like the job reports to start numbering at 1.

For example:

Job Report
A 1
A 2
A 3
B 1
B 2
C 1
C 2
C 3

I know that this can be done, I just can not get it straight in my head.

Could somebody please help me out?
TIA
CJ
Click to expand...
Click to expand...
 
Back
Top