• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 43
  • Last Modified:

Numbering Data in a form Sequentially

How do I automatically generate a sequential number sequence and assign it to the rows returned on a form?

I have a table with two columns.  The first column is a Work Order ID that's assigned to multiple samples.  The second column in the Sample ID.  I have a form that will search on Work Order and return the Sample ID's for that work order.  The Sample ID's are numbers, and thus can be sorted numerically.  I want the form to sort these Sample ID's numerically and then assign them the number they are in the sequence.

Example:

Table_1

Example Table with Work Order and Sample ID

I would like the form to return the Sample ID's for Work Order ABC1925.  This would result in the Details section of the form returning:

15463
15464
15465
15466

I would then like to assign each Sample ID with the number they are on the current list.  So it would be:

1        15463
2        15464
3        15465
4        15466

If I closed the form and decided to search for another Work Order, I would want the form to assign the sequence for the new set.  So if I searched by Work Order LMN7643, it would return:

1        21015
2        21016
3        21025
4        21026
5        21032
6        21035

Note that the Sample ID's aren't sequential themselves.  That doesn't matter for my purposes.  I only want them numbered sequentially by descending Sample ID.

Thanks for everyone who read this, I hope you have a great day.
0
BugHugger
Asked:
BugHugger
  • 6
  • 3
  • 3
  • +2
3 Solutions
 
Nitin SontakkeDeveloperCommented:
Disclaimer first: I am not much of MS-Access guy.

It appears that you are looking for the DCount() function in MS-Access. Have a look at the following:

http://www.iaccessworld.com/use-dcount-function/
0
 
BugHuggerNoobAuthor Commented:
Hey, Nitin, thanks so much for your response!  

It looks like Dcount is mostly used for counting the total rows, and not so much for numbering rows sequentially.  Using DCount in my above example would only tell me that there are 4 Sample ID's total assigned to that Work Order.
0
 
PatHartmanCommented:
You have to use the Where argument in the DCount() to control what it is counting.

Select DCount("*"), "YourQuery", "WorkOrderID = " & Forms!yourform!cboWorkOrderID & " AND SampleID < " & SampleID) As SeqNum, WorkorderID, SampleID
From YourQuery
Order by ....

The assumption is that the WorkOrderID will be specified by some form or report.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
BugHuggerNoobAuthor Commented:
Hey, Pat, thanks for your answer.  I don't have much programming experience so I'm having a bit of a hard time following your solution.  Is what you suggested VBA for the form or a separate query that the form should pull data from?

The form builds the dataset when it's loaded if that helps:

Private Sub Form_Load()
Me.RecordSource = "SELECT...." 

Open in new window

0
 
PatHartmanCommented:
This is the query that you will use as the RecordSource for the form.  The Where argument is referencing a control on the formthat contains the WorkOrderID that the form is displaying.

I rarely use embedded SQL.  I prefer to use saved querydefs whenever possible because they are more efficient, easier to test, and reusable for other forms/reports.  So, create a query using the QBE that selects all the columns you need for the form and add the DCount() expression to generate the SeqNum.

This is an extremely inefficient function but if you have to have the lines numbered, then you have to do it.  Hopefully, the tables are indexed appropriately and each WorkOrder has only a few samples.  The DCount() is running a query for every row in your main query.  So, if you are selecting 1000 records to display on the form, 1000 queries need to be run to produce the count.  Pay attention to the punctuation in the sample.  The WHERE argument is essentially comparing a value from the current record with all other records specified by the WHERE clause.

Also, keep in mind that if the data changes, the sequence number assigned to a particular sample might change.  For example if you delete a sample, that will shift the SeqNum down 1 for every sample that followed the deleted sample.  This is the reason I would probably not assign sequence numbers but I can see that it might be easier to refer to sample #10 than it's long ID number.  Just as long as you are certain that both parties are looking at the same version of the data.
0
 
BugHuggerNoobAuthor Commented:
I think what I'm having a hard time understanding now is how exactly to format the DCount function.  I've successfully used DCount to count the total number of samples in a set, however I can't get it to number things sequentially.  Below is what I tried.


I've created a simple query to try to test the DCount function.

SELECT Table_1.WorkOrderID, Table_1.SampleID
FROM Table_1
WHERE (((Table_1.WorkOrderID)="ABC1925"));

Open in new window


Where and how to enter the DCount function is what's currently tripping me up.  Entering it into a column of the query results in an error in the DCount column and a prompt asking me what Query1 is:

SeqNum: DCount([SampleID],[Query1],[WorkOrderID]="ABC1925")

SELECT Table_1.WorkOrderID, Table_1.SampleID, DCount([SampleID],[Query1],[WorkOrderID]="ABC1925") AS SeqNum
FROM Table_1
WHERE (((Table_1.WorkOrderID)="ABC1925"));

Open in new window


Creating a second query, using the first query as the data set, results in similar errors:

SELECT Query1.SampleID, DCount([SampleID],[Query1],[WorkOrderID]="ABC1925") AS SeqNum
FROM Query1;

Open in new window


Trying to format the DCount function in a different way results in the count being the total number of samples for the set:

SELECT Query1.SampleID, DCount("[SampleID]","Query1",[WorkOrderID]="ABC1925") AS SeqNum
FROM Query1;

Open in new window

0
 
PatHartmanCommented:
SELECT Table_1.WorkOrderID, Table_1.SampleID, DCount("*", "Query1", "WorkOrderID = " & WorkOrderID & " AND SampleID < " & SampleID)) AS SeqNum
FROM Table_1
WHERE (((Table_1.WorkOrderID)="ABC1925"));

As I said, you need to pay attention to how I punctuated the string.  Counting * is more efficient than counting using a specific column.  When you use a specific column name, you force the query engine to look at every single row because it will only count items where the specific field is not null.  If nulls are not an issue or not selected, then "*" allows the query engine to optimize the count using statistics if it can.  As for the WHERE clause, you are comparing the value of a field in a different record to the value of a field in the current row.  Your Where clause is restricting the set to a specific workorder so that doesn't need to be repeated.  The current record just needs to be referenced.  To generate the count, you need to include all SampleID's less than the one on the current record.

Although hard coding the workorderID is fine for testing, you will not want to use the query this way.  You will want the workorderID to be a variable and the best method is to reference a form field so at some point you need to change thw WHERE clause to

WHERE (((Table_1.WorkOrderID)= Forms!myform!WorkOrderID));

so substitute the form name and the control name you have used.  Leave the Form!xxx!zzz intact
0
 
Gustav BrockCIOCommented:
Use my clever RowCounter  function. It is exactly for purposes like this.

The in-line comments list typical usages. For your case, it could be:

SELECT RowCounter(CStr([SampleID]),False,[WorkOrderID]) AS RowID, *
FROM tblSomeTable
WHERE (RowCounter(CStr([SampleID]),False,[WorkOrderID]) <> RowCounter("",True));

Open in new window

Here goes:
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

0
 
Dale FyeCommented:
Another option would be to use a query that looks like:
SELECT Count(Table2.SampleID) as Rank, Table_1.SampleID
FROM Table_1 LEFT JOIN Table_1 as Table2 
ON Table_1.WorkOrderID = Table2.WorkOrderID AND Table1.SampleID >= Table2.SampleID
WHERE (Table_1.WorkOrderID = "ABC1925")
GROUP BY Table_1.SampleID
ORDER BY Count(Table2.SampleID)

Open in new window

By joining the table to itself using the non-equi join (Table_1.SampleID >= Table2.SampleID, what you are doing is creating a recordset that looks like:
Table_1.SampleID     Table2.SampleID
15463                        15463
15464                        15463
15464                        15464
15465                        15463
15465                        15464
15465                        15465
15466                        15463
15466                        15464
15466                        15465
15466                        15466

Open in new window

and then grouping by the Table_1.SampleID and counting the Table2.SampleID gives you a count of all the values that are less than or equal to each Table_1.SampleID value.

HTH
Dale
0
 
BugHuggerNoobAuthor Commented:
Many thanks to everyone who helped me with this problem.  In the end Dale Fye's suggestion worked perfectly, thanks Dale!
0
 
BugHuggerNoobAuthor Commented:
This is just a quick follow up in case someone finds this question later looking for help.  


Pat had a lot of great advice, but I wasn't able to get his query to work.  When I tried Pat's DCount function the query returned #Error for the SeqNum column.

SELECT Table_1.WorkOrderID, Table_1.SampleID, DCount("*","Query1","WorkOrderID = " & WorkOrderID & " AND SampleID < " & SampleID) AS SeqNum
FROM Table_1
WHERE (((Table_1.WorkOrderID)="ABC1925"));

Open in new window


Gustav had a lot of great code, unfortunately when I tried it I got an "Undefined function 'RowCounter' in expression" error.

SELECT RowCounter(CStr([SampleID]),False,[WorkOrderID]) AS RowID, *
FROM Table_1
WHERE (RowCounter(CStr([SampleID]),False,[WorkOrderID]) <> RowCounter("",True));

Open in new window



Thanks again to all the experts, you guys are always awesome!
0
 
Gustav BrockCIOCommented:
A pity.
All you need is a copy-paste into a module. It can't fail, so I don't know what have done.
0
 
BugHuggerNoobAuthor Commented:
Gustav, you're right and I'm an idiot!  I'm so sorry!  I had completely misunderstood what you were saying.  Using this code now works incredibly well.  I can't apologize enough for not understanding, I'm sorry
0
 
Gustav BrockCIOCommented:
It's never to late.
Now, enjoy the speed.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 6
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now