Auto Numbering In Query Column.
We know how to create an Auto-number Field in a Table to generate Unique Sequence numbers for the records added to the Table. We know how to insert line numbers sequentially for data lines on Reports.
On The Reports.
On Reports, create a TextBox in the Detail Section of the Report, write the expression =1 in the Control Source Property, and change the Running Sum Property Value to Over All or Over Group.
If you need sequence numbers starting with 1 for each Group separately, depending on the Sorting and Grouping settings on the Report, then the Over Group option must be set in the Property. Otherwise, set the Overall All option for continuous numbers from the start of the Report to the End.
If you want to create a Running Sum value of a Field, like Quantity or Total Price, then set the Running Sum Property value as explained above. For more details on Running Sum as well as creating Page-wise Totals on Access Reports, visit the Page with the Title: MS-Access Report and Page Totals.
In The Query Column.
However, asking for auto-numbering in a query column might seem unusual—unless the query results are meant for display purposes or the output requires sequence numbers for a specific reason.
Products Category Group-level sequence numbers or for creating a Rank List for students based on their obtained marks, and so on.
Or after filtering the records in the Query, the Auto-number field values are out of sequence.
This requirement was actually raised by a participant in an online MS Access Users Forum. No one, including myself, was able to suggest a definitive solution, only some alternatives. I offered a solution of my own, even though I wasn’t entirely satisfied with it either.
The Access User who raised the question in the Forum asked for a solution via email.
This prompted me to revisit the topic and experiment with a few simple methods. Eventually, I developed a function that accomplishes the task, and I’m sharing it here so that you can try it out too.
Need Trial and Error Runs.
It is important to understand how to use the QrySeq() function in a new query column to generate sequence numbers. The function must be called with specific parameter values, often derived from the query’s own columns. Before presenting the VBA code for the function, the details of its parameters are explained below.
Usage of the Function in the Query Column is as shown below:
Syntax: Target Column Name: QrySeq([Field Value], "Field Name", "Query Name")
SRLNO: QrySeq([ORDERID], "[ORDERID]", "QUERY4")
The QrySeq() Function needs three Parameters.
The First Parameter must be Unique Values available from any Column in the Query.
The second Parameter is the Column Name of the first parameter in Quotes.
The third Parameter is the Name of the Query from which you call the Function.
The query from which the QrySeq() function is called should include a column of unique values, such as an AutoNumber or a Primary Key field. If such a column is not readily available, you can create one by combining two or more existing fields—for example:
Ensure that this concatenation produces unique values for all records, and then pass this column ([NewColumn]) as the first parameter to the function.
The first Parameter Column Name must be passed to the Function in Quotes ("[NewColumn]") as the second parameter.
The Name of the Query must be passed as the third parameter.
NB: Ensure that you save the Query first, after every change to the design of the Query, before opening it in Normal View, to create the Sequence Numbers correctly.
The QrySeq() Function Code.
The simple rules are in place, and it is time to try out the Function.
- Copy and Paste the following VBA Code into a Standard Module in your Database:
Option Compare Database
Option Explicit
Dim varArray() As Variant, i As Long
Public Function QrySeq(ByVal fldvalue, ByVal fldName As String, ByVal QryName As String) As Long
'-------------------------------------------------------------------
'Purpose: Create Sequence Numbers in Query in a new Column
'Author : a.p.r. pillai
'Date : Dec. 2009
'All Rights Reserved by www.msaccesstips.com
'-------------------------------------------------------------------
'Parameter values
'-------------------------------------------------------------------
'1 : Column Value - must be unique Values from the Query
'2 : Column Name - the Field Name from Unique Value Taken
'3 : Query Name - Name of the Query this Function is Called from
'-------------------------------------------------------------------
'Limitations - Function must be called with a Unique Field Value
' - as First Parameter
' - Need to Save the Query after change before opening
' - in normal View.
'-------------------------------------------------------------------
Dim k As Long
On Error GoTo QrySeq_Err
restart:
If i = 0 Or DCount("*", QryName) <> i Then
Dim j As Long, db As Database, rst As Recordset
i = DCount("*", QryName)
ReDim varArray(1 To i, 1 To 3) As Variant
Set db = CurrentDb
Set rst = db.OpenRecordset(QryName, dbOpenDynaset)
For j = 1 To i
varArray(j, 1) = rst.Fields(fldName).Value
varArray(j, 2) = j
varArray(j, 3) = fldName
rst.MoveNext
Next
rst.Close
End If
If varArray(1, 3) & varArray(1, 1) <> (fldName & DLookup(fldName, QryName)) Then
i = 0
GoTo restart
End If
For k = 1 To i
If varArray(k, 1) = fldvalue Then
QrySeq = varArray(k, 2)
Exit Function
End If
Next
QrySeq_Exit:
Exit Function
QrySeq_Err:
MsgBox Err & " : " & Err.Description, , "QrySeqQ"
Resume QrySeq_Exit
End FunctionThe Sample Trial Run.
Import the Orders Table from C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb sample database.
Copy and Paste the following SQL String into the SQL Editing View of a New Query and save the Query with the Name: AutoNumberQuery:
SELECT Orders.*, QrySeq([OrderID],"OrderID","AutoNumberQuery") AS SRLNO
FROM Orders;
Select Save from the File Menu or click the Save Toolbar Button.
Open the Query in the normal view.
Check the SRLNO Column for Sequence Numbers.
In this case, the OrderID field in the Orders table already contains unique values, so we can generate sequence numbers directly in the SRLNO column without any additional steps.
However, if the query does not contain a single column with unique values, we must create one by combining two or more existing query columns. This newly created column with unique values can then be passed to the QrySeq() function.
Let us try such an example with the Orders Table.
- Copy and paste the following SQL String into a new Query and save the Query with the name AutoNumberQuery2.
SELECT Orders.*, [ShipName] & [RequiredDate] AS NewColumn, _
QrySeq([NewColumn],"NewColumn","AutoNumberQuery2") AS SRLNO
FROM Orders;
- Open the Query in Datasheet View to check whether the Serial Numbers were created correctly.
Ensuring Accuracy.
When a query contains hundreds or thousands of records, it is impractical to manually verify that the column values passed to the QrySeq() function are truly unique and that the generated serial numbers contain no duplicates. Instead, we can use a Total Query to count serial numbers that appear more than once. For this, we use the AutoNumberQuery2 as the source, which allows us to quickly identify any duplicate serial numbers in the dataset.
- Create a new Query that uses the following SQL String and name the new Query as DuplicatesCheckQ:
SELECT AutoNumberQuery2.SRLNO,
Count(AutoNumberQuery2.SRLNO) AS CountOfSRLNO
FROM AutoNumberQuery2
GROUP BY AutoNumberQuery2.SRLNO
HAVING (((Count(AutoNumberQuery2.SRLNO))>1));
- Open DuplicatesCheckQ Query in Normal View.
The result will show that the SRLNO column contains the same number appearing more than once in the records. This indicates that the column values of the QrySeq() function are not unique and contain duplicates.
This can be rectified only by adding more Column Values to the NewColumn expression to eliminate the chance of ending up with duplicates.
This method serves as an alternative when an AutoNumber or Primary Key field is not available, and it does not guarantee 100% accuracy. When additional records are added to the source table, the method may fail again. In such cases, the only solution is to combine more fields in the NewColumn expression to reduce the likelihood of duplicates and ensure uniqueness.
To correct the query above, include the [Freight] column in the NewColumn expression. Alternatively, you can copy and paste the following SQL string into the AutoNumberQuery2 query, overwriting the previous SQL, and then save the query.
SELECT Orders.*,
[ShipName] & [RequiredDate] & [Freight] AS NewColumn,
QrySeq([NewColumn],
"NewColumn";,"AutoNumberQuery2") AS SRLNO
FROM Orders;
Open the DuplicatesCheckQ Query again to check for duplicates. If the result is empty, then the Sequence Numbers will be correct.
If you know a better solution, please share it with me. I’m not looking for a refinement of the existing code or method, but for a different approach that can achieve the same—or even better—results.
Improved Versions related to this topic:
Find New Auto-Numbers in Query Column Version-2 on this link.
Next:
Download
Download Demo QryAutoNum.zip
- Auto-Numbering in Query Column
- Product Group Sequence with Auto-Numbers.
- Preparing Rank List.
- Auto-Number with Date and Sequence Number.
- Auto-Number with Date and Sequence Number-2.
