Auto Numbering In Query Column

Introduction

Find New Auto-Numbers in Query Column Version-2 on this link.

For creating "Running Sum Values in Query-Column" visit the following link:

Running Sum in MS-Access Query.

We know how to create an Auto-number Field in a Table to generate Unique Sequence numbers automatically for the records added to the Table. We know how to create Sequence Numbers 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 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 Over All value, 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.

But, Auto-numbering in the Query Column looks somewhat strange to ask for, unless you want to use the Query result for display purposes or the output created from that should have sequence numbers for some reason. 

Products Category Group-level sequence numbers or for creating 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 gone out of sequence.

Anyway, this requirement was raised by a participant in an MS-Access Users Forum on the Net and nobody (including me) could give a clear-cut solution except for some alternatives. I chipped in with a solution of my own, even though I was not happy with that either.

The Access User who raised the question in the Forum made direct contact by sending an e-mail to me asking for a solution.

This made me think again on that topic and did a few trial runs of a few simple methods. Finally, I could come up with a Function that can do the trick and I am presenting it here so that you can also use it if you really need it.

Need Trial and Error Runs.

It is important to know the usage of the QrySeq() Function in a new Column of Query to create Sequence Numbers. The Function must be called with a few Parameter Values using the value(s) from the Query Column(s) itself. So, before presenting the VBA Code of the Function I will give some details of the Parameters.

Usage of the Function in the Query Column is as shown below:

SRLNO: QrySeq([ORDERID], "[ORDERID]", "QUERY4")

The QrySeq() Function needs three Parameters.

1. The First Parameter must be Unique Values available from any Column in the Query.
2. The second Parameter is the Column Name of the first parameter in Quotes.
3. The third Parameter is the Name of the Query, from which you call the Function.

The Query, from where the QrySeq() Function is called should have a column of Unique Values, like Autonumber or Primary Key Field. If this is not readily available, then create a Column by joining two or more existing fields (like NewColumn:([OrderlD] & [ShippName] & [RequiredDate] & [Quantity] from the existing column values and ensure that this will form Unique values in all records and pass this Column value ([NewColumn]) as the first Parameter.

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

Now then, the simple rules are in place and it is time to try out the Function.

1. 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 Function

   The Sample Trial Run

2. Import the Orders Table from C:\Program Files\Microsoft Office\Office11\Samples\Northwind.mdb sample database.
3. 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;
   

4. Select Save from File Menu or click on the Save Toolbar Button.
5. Open the Query in the normal view.

Check the SRLNO Column for Sequence Numbers.

Here, the OrderID in the Orders Table has unique field values and we could easily get away with the Sequence Numbers correctly in SRLNO Column.

Let us pretend for a moment that we don't have a single field with Unique Values in the Query. We must create a Column with Unique Values by joining two or more Columns available in the Query and pass it to the QrySeq() Function.

Let us try such an example with the Orders Table.

1. 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;

2. Open the Query in normal View to check whether the Serial Numbers were created correctly or not.

Ensuring Accuracy

When there are hundreds/Thousands of records in the Query it is difficult to check whether the Column Values we have passed to the Function are really unique and the Serial Numbers generated have no duplicates in them by manually checking through the records. Instead, we will take a Count of Serial Numbers appearing more than once in the Records, if any, with the use of a Total Query using AutoNumberQuery2 as the Source.

1. Create a new Query 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));
   

2. Open DuplicatesCheckQ Query in Normal View.

You will find the following result showing SRLNO Column is having the same number appearing more than once in the records indicating that the Unique Column Values we have created for the Function are not really Unique and have duplicates in them.

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 is only an alternative in the absence of an AutoNumber or Primary Key field Values and not with a 100% percent success rate because when you add more records to the Source Table it is likely that it can fail again. In this case, the only solution is to join more fields to the expression in NewColumn so that we can reduce the chance of failures.

Now, to correct the above Query adds the [Freight] Value Column also to the NewColumn expression. Or Copy and paste the following SQL String into the AutoNumberQuery2 Query overwrites the earlier SQL string in there and 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 generated will be correct.

Found Different Method, Share it With me.

If you have a better solution to this, then please share it with me too. I don't need a refined version of the above Code or method, but a different approach to arrive at the same or better result.

Next:

Autonumber with Date and Sequence Number.

Download

Download Demo QryAutoNum.zip

1. Auto-Numbering in Query Column
2. Product Group Sequence with Auto-Numbers.
3. Preparing Rank List.
4. Auto-Number with Date and Sequence Number.
5. Auto-Number with Date and Sequence Number-2.