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!
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
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.
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
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.
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
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.
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.
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.
You have to use the Where argument in the DCount() to control what it is counting.
Select DCount("*"), "YourQuery", "WorkOrderID = " & Forms!yourform!cboWorkOrde
From YourQuery
Order by ....
The assumption is that the WorkOrderID will be specified by some form or report.
Select DCount("*"), "YourQuery", "WorkOrderID = " & Forms!yourform!cboWorkOrde
From YourQuery
Order by ....
The assumption is that the WorkOrderID will be specified by some form or report.
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:
The form builds the dataset when it's loaded if that helps:
Private Sub Form_Load()
Me.RecordSource = "SELECT...."
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.
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.
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.
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]
Creating a second query, using the first query as the data set, results in similar errors:
Trying to format the DCount function in a different way results in the count being the total number of samples for the set:
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"));
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]
SELECT Table_1.WorkOrderID, Table_1.SampleID, DCount([SampleID],[Query1],[WorkOrderID]="ABC1925") AS SeqNum
FROM Table_1
WHERE (((Table_1.WorkOrderID)="ABC1925"));
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;
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;
SELECT Table_1.WorkOrderID, Table_1.SampleID, DCount("*", "Query1", "WorkOrderID = " & WorkOrderID & " AND SampleID < " & SampleID)) AS SeqNum
FROM Table_1
WHERE (((Table_1.WorkOrderID)="A
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
FROM Table_1
WHERE (((Table_1.WorkOrderID)="A
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
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:
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));
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
Another option would be to use a query that looks like:
HTH
Dale
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)
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
HTH
Dale
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
Many thanks to everyone who helped me with this problem. In the end Dale Fye's suggestion worked perfectly, thanks Dale!
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.
Gustav had a lot of great code, unfortunately when I tried it I got an "Undefined function 'RowCounter' in expression" error.
Thanks again to all the experts, you guys are always awesome!
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"));
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));
Thanks again to all the experts, you guys are always awesome!
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
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.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
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/