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.
ASKER
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Many thanks to everyone who helped me with this problem. In the end Dale Fye's suggestion worked perfectly, thanks Dale!
ASKER
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!
A pity.
All you need is a copy-paste into a module. It can't fail, so I don't know what have done.
All you need is a copy-paste into a module. It can't fail, so I don't know what have done.
ASKER
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 never to late.
Now, enjoy the speed.
Now, enjoy the speed.
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/