How do I add a sequential line number to this data

This is query:

SELECT [ti bom010 Bill of Material].t_mitm, [ti bom010 Bill of Material].t_pono, [ti bom010 Bill of Material].t_sitm
FROM [ti bom010 Bill of Material]
WHERE ((([ti bom010 Bill of Material].t_mitm)="         AAJ0400"))
ORDER BY [ti bom010 Bill of Material].t_pono;


How do I add a field with a sequential line number starting at 1?
When the contents of field "t_mitm" changes I wish to reset counter and start numbering again from 1

The current output is:

t_mitm      t_pono      t_sitm
         AAJ0400      10               AAX0400
         AAJ0400      20               PCA0006
         AAJ0400      30               PCA0106
         AAJ0400      40               RAA0009
         AAJ0400      500               XRGA1025

Thanks
Paul
Paul BurrowsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aikimarkCommented:
I think you'll need a report to do that
Paul BurrowsAuthor Commented:
t_mitm      t_pono      t_sitm      Sequence
         AAJ0400      10               AAX0400      1
         AAJ0400      20               PCA0006      2
         AAJ0400      30               PCA0106      3
         AAJ0400      40               RAA0009      4
         AAJ0400      500               XRGA1025      5
         AAK1011      10               AAX1011      1
         AAK1011      20               PEE0346      2
         AAK1011      25               PEE0027      3
         AAK1011      30               RAA0001      4
         AAK1011      500               XRFA1071      5

Are you sure?  To make it clearer .... the last column is what I need to generate

aikimarkCommented:
It might look something like this, but it won't run very fast if you have a lot of data (typical of BOM databases).
SELECT [ti bom010 Bill of Material].t_mitm, [ti bom010 Bill of Material].t_pono, [ti bom010 Bill of Material].t_sitm, 
(Select Count(*) From [ti bom010 Bill of Material] As BOM Where BOM.t_mitm = [ti bom010 Bill of Material].t_mitm And BOM.t_pono > [ti bom010 Bill of Material].t_pono Order By t_mitm, t_pono)+1 As Sequence

FROM [ti bom010 Bill of Material]
WHERE ((([ti bom010 Bill of Material].t_mitm)="         AAJ0400"))
ORDER BY [ti bom010 Bill of Material].t_pono;

Open in new window

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Hamed NasrRetired IT ProfessionalCommented:
Compare with this:
Table a:
f1      f2
1      20
1      40
1      30
1      10
2      10
2      30
2      40
3      10
3      20

Query:
SELECT a.f1, a.f2, (select count(f1) from a  as a1 where a1.f1=a.f1 and a1.f2<=a.f2  )  as seq
FROM a order by 1,2

Result:
f1      f2      seq
1      10      1
1      20      2
1      30      3
1      40      4
2      10      1
2      30      2
2      40      3
3      10      1
3      20      2
Gustav BrockCIOCommented:
Use the function below to build the row numbers.
Note the in-line comments and this typical usage:

' Usage (with group key):
'   SELECT RowCounter(CStr([ID]),False,CStr[GroupID])) AS RowID, *
'   FROM tblSomeTable
'   WHERE (RowCounter(CStr([ID]),False) <> RowCounter("",True));

For your query it would read using [t_mitm] & Format([t_pono],"00000") as a unique key:

SELECT
  [ti bom010 Bill of Material].t_mitm,
  [ti bom010 Bill of Material].t_pono,
  [ti bom010 Bill of Material].t_sitm,
  RowCounter([t_mitm] & Format([t_pono],"00000"),False,[t_mitm]) AS Sequence
FROM
  [ti bom010 Bill of Material]
WHERE
  ([ti bom010 Bill of Material].t_mitm="         AAJ0400")
  AND
  (RowCounter([t_mitm] & Format([t_pono],"00000"),False) <> RowCounter("",True));
ORDER BY
  [ti bom010 Bill of Material].t_mitm,
  [ti bom010 Bill of Material].t_pono;

Public Function RowCounter( _
  ByVal strKey As String, _
  ByVal booReset As Boolean, _
  Optional ByVal strGroupKey As String) _
  As Long
  
' Builds consecutive RowIDs in select, append or create query
' with the possibility of automatic reset.
' Optionally a grouping key can be passed to reset the row count
' for every group key.
'
' Usage (typical select query):
'   SELECT RowCounter(CStr([ID]),False) AS RowID, *
'   FROM tblSomeTable
'   WHERE (RowCounter(CStr([ID]),False) <> RowCounter("",True));
'
' Usage (with group key):
'   SELECT RowCounter(CStr([ID]),False,CStr[GroupID])) AS RowID, *
'   FROM tblSomeTable
'   WHERE (RowCounter(CStr([ID]),False) <> RowCounter("",True));
'
' The Where statement resets the counter when the query is run
' and is needed for browsing a select query.
'
' Usage (typical append query, manual reset):
' 1. Reset counter manually:
'   Call RowCounter(vbNullString, False)
' 2. Run query:
'   INSERT INTO tblTemp ( RowID )
'   SELECT RowCounter(CStr([ID]),False) AS RowID, *
'   FROM tblSomeTable;
'
' Usage (typical append query, automatic reset):
'   INSERT INTO tblTemp ( RowID )
'   SELECT RowCounter(CStr([ID]),False) AS RowID, *
'   FROM tblSomeTable
'   WHERE (RowCounter("",True)=0);
'
' 2002-04-13. Cactus Data ApS. CPH
' 2002-09-09. Str() sometimes fails. Replaced with CStr().
' 2005-10-21. Str(col.Count + 1) reduced to col.Count + 1.
' 2008-02-27. Optional group parameter added.
' 2010-08-04. Corrected that group key missed first row in group.

  Static col      As New Collection
  Static strGroup As String
  
  On Error GoTo Err_RowCounter
  
  If booReset = True Then
    Set col = Nothing
  ElseIf strGroup <> strGroupKey Then
    Set col = Nothing
    strGroup = strGroupKey
    col.Add 1, strKey
  Else
    col.Add col.Count + 1, strKey
  End If
  
  RowCounter = col(strKey)
  
Exit_RowCounter:
  Exit Function
  
Err_RowCounter:
  Select Case Err
    Case 457
      ' Key is present.
      Resume Next
    Case Else
      ' Some other error.
      Resume Exit_RowCounter
  End Select

End Function

Open in new window


This runs very fast.

/gustav

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GRayLCommented:


SELECT a.t_mitm, a.t_pono, a.t_sitm
(SELECT Count(b.*) FROM [ti bom010 Bill of Material] b WHERE b.t_mitm=a.mitm AND b.pono<=a.pono) As ItemCount
FROM [ti bom010 Bill of Material] a
ORDER BY a.t_pono;
aikimarkCommented:
@GrayL

Don't you need the +1 to get an sequence starting with 1?
GRayLCommented:
That's what the <= takes care of.
Gustav BrockCIOCommented:
One day you should try and run a test with my mthod using a collection on a not-so-small dataset and watch how it is browsable. Quite amazing!

/gustav
GRayLCommented:
I see a mistake:

SELECT a.t_mitm, a.t_pono, a.t_sitm
(SELECT Count(b.pono) FROM [ti bom010 Bill of Material] b WHERE b.t_mitm=a.mitm AND b.pono<=a.pono) As ItemCount
FROM [ti bom010 Bill of Material] a
ORDER BY a.t_pono;
Paul BurrowsAuthor Commented:
You are correct.  This runs incredibly fast and works great

Thanks very much

Awesome
Paul BurrowsAuthor Commented:
Thanks GRayL

Your solution looked more elegant without need for other code

I obviously didn't set up correctly as screens returned were like this. Screen.doc
Gustav BrockCIOCommented:
You are welcome!
Thanks for the feedback.

/gustav
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.