Results 1 to 2 of 2
  1. #1
    Harmoesh is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jul 2018
    Posts
    1

    sequential numbering with criteria

    Hello!

    Firstly let me introduce myself. I am an undergraduate student in Earth Sciences working with Access since a few months as part of my Thesis.As I have ~3000 boreholes with in total around 50k layers I can't rely on manually entering data but have to automate everything. I m working boreholes which contain distinctive layers. These layers can be lying continuous on top of each other or lie discontinuous on one another (which is called a 'boundary'). so for example a table may look like this:



    Borehole Boundary Sequence
    x no
    x Yes
    x no
    x no
    x Yes
    x no
    x no
    y no
    y no
    y Yes
    y no
    y no
    y no
    z no
    z Yes
    z no
    z Yes
    z Yes
    z no
    Now, what I want the query to do is create sequential numbering in the third column, where every new borehole the value is reset to '1' and every boundary the value is increased by '1'. i.e.:
    Borehole Boundary Sequence
    z no 1
    z yes 2
    z no 2
    z yes 3
    z no 3
    z no 3
    z yes 4
    z no 4

    What I have right now is wrong as it doesn't (clearly) updates everything at once.
    Code:
    IIf([Sterksel_Grainsizes]![nr]=[sterksel_grainsizes_1]![nr];IIf([Sterksel_Sequences]![Sequence_boundary]="boundary";[Sterksel_Sequences]![sequence_number]+1;[Sterksel_sequences]![sequence_number]);1)
    What I need is a query that looks at the row above before calculating a value. My question is if that is even possible within one or more queries.

    Many thanks in advance!

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    'the row above' is not a concept recognised by databases - to find the previous record (i.e. the row above) you must first define an order. With the data you have provided, this is not possible - how do you know which boundary comes first? You need some more data in order to define that order.

    It may be this can be done by having a row number - an autonumber might do it, but their purpose is simply to identify a unique record, not to provide a sort order

    Borehole RowNum Boundary Sequence
    x 1 no
    x 2 Yes
    x 3 no
    x 4 no
    x 5 Yes
    x 6 no
    x 7 no
    y 8 no
    y 9 no

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

Similar Threads

  1. Add sequential numbering to groups of data
    By astephan in forum Access
    Replies: 8
    Last Post: 06-07-2018, 08:34 AM
  2. Daily Reset Sequential Numbering Using Dmax.
    By NickTheG in forum Access
    Replies: 4
    Last Post: 09-20-2017, 01:29 PM
  3. Replies: 5
    Last Post: 10-15-2012, 12:18 AM
  4. Sequential Numbering in a Form
    By Falafa in forum Forms
    Replies: 6
    Last Post: 10-05-2012, 08:20 AM
  5. Help with Sequential Numbering
    By orion in forum Programming
    Replies: 3
    Last Post: 07-06-2009, 01:41 PM

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