Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Auto Numbering In Query Column

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 Reports, create a Text Box in 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 Over All for continuous numbers from start of the Report to the End.

If you want to create 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.

But, Auto-numbering in Queries 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. Any way this requirement was raised by a participant in a MS-Access Forum on the Net and nobody (including me) could give a clear cut solution except some alternatives. I chipped in with a solution of my own, even though I was not happy with that either.

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

This made me thinking again on that topic and did few trial runs of 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.

It is important to know the usage of the QrySeq() Function in Queries in a new Column to create Sequence Numbers. The Function must be called with few Parameter Values using the values from the Query 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 need three Parameters.

  1. The First Parameter must be Unique Values available from any Field in the Query.
  2. Second Parameter is the Column Name of the first parameter in Quotes.
  3. Third Parameter is the Name of the Query from where 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 first Parameter.

The first Parameter Column Name must be passed to the Function in Quotes ("[NewColumn]") as second parameter.

The Name of the Query must be passed as 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, with the Function in place, to create the Sequence Numbers correctly.

Now, the simple Rules are in place 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
  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 normal view.

Check the SRLNO Column for Sequence Numbers.

Here, the OrderID in the Orders Table have 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.

When there are several records 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 Source.

  1. Create a new Query using 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 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 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 add the [Freight] Value Column also to the NewColumn expression. Or Copy and paste the following SQL String into the AutoNumberQuery2 Query overwriting 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.

If you have a different solution to this problem, then 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.




Download Demo QryAutoNum.zip



Share:

15 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

Translate



PageRank
Your email address:

Delivered by FeedBurner

Search

Infolinks Text Ads

blog.feedspot.com Microsoft Access Blogs

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Labels

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

Featured Post

User-Defined Data Type-3

Last week we have learned how to define a User -Defined Data Type and it’s usage in programs.  If you have landed on this page, you may go t...

Labels

Blog Archive

Recent Posts