Results 1 to 6 of 6
  1. #1
    jmauldin01 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2013
    Posts
    3

    Incremental Number in a Query

    Issue: My database is structure in a hierarchical manner. The field is termed Tree_Structure_Path. I need to tag entries (matches in another table) and all entries lower in the hierarchical path for use the information from the previous record. To do this, it appears a need a sequential number.

    I'm an Access Novice: I've read quite a few posts, but I do not understand the components of the statement.

    I've created this formula but the numbering is not entirely sequential.

    Seq: DCount("Level_ID","XXXXXXX_Wrp_Levels","Level_ID <= " & [XXXXXXX_Wrp_Levels].[Level_ID])


    I have attached the table Structure & query results.


    Any help is appreciated!
    Attached Thumbnails Attached Thumbnails Query.png   Sequence Number.png  

  2. #2
    A S MANN is offline Advanced System Analyst
    Windows XP Access 2007
    Join Date
    Oct 2010
    Location
    India
    Posts
    161
    The query shows two Tables Link with Label_ID Field
    Only those Label_ID Which Exist in Both the table will be shown in u r Query result.
    Hence the numbering is not entirely sequential
    u r Filtering out the data by Dcount() function.
    Seq: DCount("Level_ID","XXXXXXX_Wrp_Levels","Level_ID <= " & [XXXXXXX_Wrp_Levels].[Level_ID])
    To use Sequential use function as shown in attachment
    Attached Files Attached Files

  3. #3
    jmauldin01 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2013
    Posts
    3
    Thank you for your quick response.

    I have copied / pasted your 2 modules into my database (no modifications). In addition I have:
    1) Removed the extra file since it is not needed to obtain a row number
    2) Used your query formula & edited to reference my query name and fields
    Expr1: Serialize("qry_SponsorHierarchy","Level_ID",[XXXXXXX_WRP_LEVELS].[Level_ID])
    I have to reference the table name or I get missing ")" messages
    I also tried this formula using Tree_Structure_Path, but had the same issues

    Any assistance is appreciated!

    Images follow:

    Click image for larger version. 

Name:	Addition of GetLineNumber - Error Message.png 
Views:	22 
Size:	5.9 KB 
ID:	14865
    Click image for larger version. 

Name:	Addition of GetLineNumber.png 
Views:	22 
Size:	110.4 KB 
ID:	14864

  4. #4
    A S MANN is offline Advanced System Analyst
    Windows XP Access 2007
    Join Date
    Oct 2010
    Location
    India
    Posts
    161
    The error might be due to Level_ID Being a Prime Key & hence the Data Type Conversion Error. Use some Other Text Field in place of Level_ID.

  5. #5
    jmauldin01 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2013
    Posts
    3
    I Change the Field to Short_Name and the error message went away -- Thank You.
    But the values are all zero

    Expr1: Serialize("qry_SponsorHierarchy","Short_Name",[XXXXXXX_WRP_LEVELS].[Short_Name])

    The 2 procedures that were included in your sample database: I did not make any changes to them. Is that correct?
    I am using Access 2010 (Windows XP). Could that be the problem?

  6. #6
    A S MANN is offline Advanced System Analyst
    Windows XP Access 2007
    Join Date
    Oct 2010
    Location
    India
    Posts
    161
    I suggest you Import your Table related to qry_SponsorHierarchy and qry_SponsorHierarchy in to rownumberVer2.accdb file and than try, These issue arrise due to to Refrences in VBA.
    Is success than inport all other objects in to new db file.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Incremental Numbers
    By dr4ke in forum Queries
    Replies: 7
    Last Post: 01-16-2013, 03:46 AM
  2. Incremental Table Updating
    By pyrman in forum Queries
    Replies: 4
    Last Post: 07-23-2012, 11:35 AM
  3. Autonumbers and Incremental Numbers
    By stacies in forum Access
    Replies: 2
    Last Post: 01-27-2012, 02:53 PM
  4. Real Time Incremental Back Up
    By GrnMtn7 in forum Access
    Replies: 1
    Last Post: 10-15-2011, 05:29 PM
  5. Creating Incremental Receipt Numbers
    By JorgeCUC in forum Queries
    Replies: 15
    Last Post: 08-25-2011, 08:50 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums