Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

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.
Share:

18 comments:

  1. Hello Ramachandran,

    I'm another developer who can use this query info. Thank you for putting this together!

    I copied and pasted your code and got as far as point #5. and tried it. I got an error message at "5. Open the Query in normal view." A parameter box appeared for OrderID, and no matter what value I entered, I got an error message 2471: The expression you entered as a query parameter produced this error: '[OrderID]'

    Can you help me with this and what was wrong? I copied and pasted your code. THANK YOU! Really want to get this to work.

    Paula

    ReplyDelete
  2. I have copied the Code and SQL String from the Web Page and tried out again. I could not find any problem with it. Are you sure that you have given the Query Name correctly as third Parameter of the Function?

    Anyway, you can download a sample database with the Code and the Queries from the following Link:

    http://www.msaccesstips.com/downloads/2010/01/QryAutoNum.zip

    Regards,

    ReplyDelete
  3. Sorry, correction to the Link:

    http://www.msaccesstips.com/downloads/2010/01/QryAutoNum.zip

    Regards,

    ReplyDelete
  4. a.p.r. pillai, thanks for the Link. It worked nicely!
    Paula

    ReplyDelete
  5. [...] need continuous sequencer numbers for the full Query Records then refer the following Blog Post: Autonumbering in Query Column. The above Code is a variant of this blog post and is intended to publish the solution in a [...]

    ReplyDelete
  6. [...] bad; I just used a Report to do it for me since that was where I needed the running sum anyway): http://msaccesstips.com/2010/01/auto...-query-column/ If it helps, glad I could [...]

    ReplyDelete
  7. thanks its works fine!!!! but i have some problems when i used some criteria based on text field in a form (my query filter based on that) its wont work the value of "SRL" number turning into zero values do you have any sugest ?

    i need the auto number because its the only way i know to have pagination report(in report view) on ms acces or do you have another suggest to make it?


    "im from indonesia sorry if i had bad english"

    ReplyDelete
  8. It will not work with Queries that references Form controls or Parameter Queries as criteria.

    Create an output Table by filtering the data by using above methods and create a separate query with the output table and call the function QrySeq() in a column in this final Query.

    There is another method for giving Group-wise Sequence numbers. This has to be done on the output table rather than on a Query. The link is given below:

    http://msaccesstips.com/2011/05/product-group-sequence-with-auto-numbers/

    ReplyDelete
  9. I have been needing this utility for quite some time but with an additional level of grouping. I have to begin re-numbering within a table everytime the PROPNUM changes. Basically the data needs to look like below. Notice the months are being counted sequentially. Can anyone help with providing code for this? MANY THANKS IN ADVANCE!

    PROPNUM MONTH VALUE COUNT
    45678 01-2010 600.00 1
    45678 02-2010 700.00 2
    45678 05-2010 500.00 3

    1234 01-2010 200.00 1
    1234 03-2010 450.00 2
    1234 07-2010 200.00 3

    ReplyDelete
  10. Take a look at the following link that does exactly what you require:

    Product Group Sequence with AutoNumbers

    ReplyDelete
  11. I have tried to use this function in my own database but I'm getting an error 13: Type Mismatch on QrySeqQ. The field I'm using as the parameter is a text field. Would this cause a problem?

    ReplyDelete
  12. You may use any field (numeric or text) or join several fields and use it as parameter to the function. There is only one condition the parameter value must be unique.

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

    The first parameter [ORDERID] is the data field reference having unique value (numeric or text) in it. If you don't have a single field with unique values then join one or more fields and create a separate column like: myKey: [EmployeeID] & [FirstName] & [LastName] then give the column name [myKey] as first parameter. Note, here EmployeeID is numeric field and FirstName and LastName are text. When all the three are joined the final result is text.

    Second Parameter is the name of the first parameter column enclosed in Quotes: "myKey". If you left a space between the words my Key then enclose them in square brackets "[my Key]", otherwise the [] are not needed.

    Third Parameter is the Query Name, from which you are calling the function, in quotes: "Query4".

    ReplyDelete
  13. Hi there, im having trouble with your script, that includes the fourth parameter, ctrlField,

    I can setup the code ok, but wheni run, I am getting 0 on every record.

    I have made sure that the parameters are as follows

    1 : Column Value - must be unique Values from the Query

    (setup by doing a calculated field that would render every record unique,)

    2 : Column Name - the Field Name from Unique Value Taken

    Column name from the calculated above

    3 : Query Name - Name of the Query this Function is Called from


    Self explanatory

    4 : Group Column Name - To check & Reset the Serial to Zero

    I used this field to be the field to check, that when it changes, reset the serial to 0. For this, I used a field that is repeated on each record, unti a change.


    I am using customer addresses as an example.

    Custid is non unique
    Shipto is non unique

    The calculated field is custid+shipto and the ctrlfield is custid.


    I expect the following results

    Cust KMART Ship id TOWNA Sequence 0
    Cust KMART Ship id TOWNB Sequence 1
    Cust KMART Ship Id TOWNC Sequence 2
    Cust BIGW Ship Id TOWNA Sequence 0
    Cust BIGW Ship Id TOWNB Sequence 1


    any help would be appreciated

    thanks
    Scott Lancaster

    ReplyDelete
  14. Sorry, worked out the last question I had, although I have another. How can I start the numbering at 1 instead of 0?

    thanks in advance,

    scotty

    ReplyDelete
  15. THANK YOU, THANK YOU, THANK YOU - I have been banging my head against this issue for 3 days and attempted numerous solutions detailed on-line. Your technique does exactly what I needed.

    ReplyDelete
  16. Thank you so much to the author. This is the only method I found working!

    ReplyDelete

Comments subject to moderation before publishing.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code