Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Magnus1982 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2017
    Posts
    29

    Get first line of data intervals

    I am working with access some time but I am struggle with problem.



    I have query which have data(one column)- example :

    AT:12GH3323-332HJ0001AT:12GH3323-332HJ0002AT:12GH3323-332HJ0003AT:12GH3323-332HJ0008AT:12GH3323-332HJ0009AT:12GH3323-332HJ0015AT:12GH3323-332HJ0016AT:12GH3323-332HJ0017AT:12GH3323-332HJ0018AT:12GH3323-332HJ0030

    I need another query which will show only first record of intervals. As bellow :

    AT:12GH3323-332HJ0001AT:12GH3323-332HJ0008AT:12GH3323-332HJ0015AT:12GH3323-332HJ0030

    This column can have more than 1000 records and random intervals . I need solution which will find and show first row from this intervals as example above.

    Please help

  2. #2
    Magnus1982 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2017
    Posts
    29
    Sorry for formating

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Your data isn't clear - that is one record, or several? If several, break it into rows.
    Expected result not clear. Why not 12GH3323-332HJ0002AT? Seems like 12GH3323 is the common denominator?
    You need to clarify. More than one input and output example would help.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    Magnus1982 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2017
    Posts
    29
    sry I am placing another examples"

    Example 1

    Input :
    AB:123HJ333:323AD0001
    AB:123HJ333:323AD0002
    AB:123HJ333:323AD0003
    AB:123HJ333:323AD0004
    AB:123HJ333:323AD0005
    AB:123HJ333:323AD0006
    AB:123HJ333:323AD0010
    AB:123HJ333:323AD0011
    AB:123HJ333:323AD0012
    AB:123HJ333:323AD0014
    AB:123HJ333:323AD0015
    AB:123HJ333:323AD0016
    Output:
    AB:123HJ333:323AD0001
    AB:123HJ333:323AD0010
    AB:123HJ333:323AD0014

    Example 2
    Input:
    AB:123HJ333:323AD0145
    AB:123HJ333:323AD0146
    AB:123HJ333:323AD0147
    AB:123HJ333:323AD0148
    AB:123HJ333:323AD0149
    AB:123HJ333:323AD0150
    AB:123HJ333:323AD0180
    AB:123HJ333:323AD0181
    AB:123HJ333:323AD0182
    AB:123HJ333:323AD0204
    AB:123HJ333:323AD0205
    AB:123HJ333:323AD0206

    Output:
    AB:123HJ333:323AD0145
    AB:123HJ333:323AD0180
    AB:123HJ333:323AD0204


    This is just an example. I can have thousands records. I have to pick up the beginnings of the numerical intervals determined by the last four digits



  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Still not clear. Examples of last 4 digits are??
    Determined how??
    In the below sample output you posted, there aren't even four consecutive digits anywhere but at the end, and they're all different so none of them could be determinative as far as I can see.

    AB:123HJ333:323AD0145
    AB:123HJ333:323AD0180
    AB:123HJ333:323AD0204

  6. #6
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    I think the OP means a break in the single digit advance of the sequence of the last four digits. Maybe the output could be extracted by a query, but I would probably rely on a sequential DAO recordset extraction, with output appended to an empty recordset. With thousands of records, it might take a few seconds.

  7. #7
    Magnus1982 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2017
    Posts
    29
    I will try explain. Imagine you have in box 1000 pcs . You have order for 500pcs. You pic up random parts from box and you scaning barcode, and you have data which I showed above. When you will scan 500pcs you doing sorting from lower to higher. And you can see you have some continueses numbers and some gaps. I need this output to place on report . I can place 500 lines in the report and this topic is close BUT If i will have order for 5000pcs ? I don't want to print 5000 lines of data insted I have acceptation from customer that on this report I can place only first record from intervals. Click image for larger version. 

Name:	Bez tytułu.jpg 
Views:	17 
Size:	73.1 KB 
ID:	38156

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    OK, the first record + the first of a group where there is a break in the sequence of the last four digits when taken as a whole; I would agree with that.
    Also agree that I probably would not try to do this in a query. It would take a fairly robust looping of a recordset. Using an ADO connectionless recordset would just complicate things, AFAIC. Easier to have a target table that can be cleared (or not) and the DAO results just appended to it - based on that requirement.
    EDIT: I posted to comment on davegri's post before I saw the previous.
    I presume you have these records in some table? In order to make getting them easier, some storage is needed for the results, even if it's temporary.
    So we're both saying that we don't see a query solution for this. Maybe somebody else will chime in with a query that can do it, but I/we are seeing only a code solution.

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    @Magnus1982: no info from you re: name of table or anything, so you can take it from here. I dumped your values into my table tblSequence with field "Sequence".
    Then I copied tblSequence as tblSequence2 (structure only) with field Sequence2. The following code seems to work with your posted data(105 to 206 group). You will have to modify it with your own table/field names and create the table to put the values in. I have included code notes to explain everything. As with any unfamiliar code, test on a copy of your db, or at least copies of any tables you plan to alter with this.
    Code:
    Sub getSequence()
    Dim rs As DAO.Recordset 'create DAO recordset to load sequence values
    Dim lngOne As Long, lngTwo As Long
    
    Set rs = CurrentDb.OpenRecordset("SELECT tblSequence.Sequence FROM tblSequence") 'populate rs
    
    If Not (rs.BOF And rs.EOF) Then 'if TRUE there are no records so just exit IF block
      rs.MoveFirst
      lngOne = Right(rs.Fields("Sequence"), 4) 'get last 4 characters from 1st record
        
      With CurrentDb
         .Execute "DELETE * FROM tblSequence2", dbFailOnError 'flush tblSequence2
         .Execute "INSERT INTO tblSequence2 (Sequence2) VALUES ('" & rs.Fields("Sequence") & "')", dbFailOnError 'add 1st complete sequence value
      End With
        
      rs.MoveNext 'move to next record...
      Do While Not rs.EOF 'quit when end of recordset file is reached
         lngTwo = Right(rs.Fields("Sequence"), 4) '...and get 4 characters of next sequence value. Leading zeros are ignored
         'subtract value 1 from value 2. If diff > 1, write complete sequence value to table
         If (lngTwo - lngOne) > 1 Then CurrentDb.Execute "INSERT INTO tblSequence2 (Sequence2) VALUES ('" & rs.Fields("Sequence") & "')", dbFailOnError
         lngOne = lngTwo 'now set value 1 = value 2 so as to compare to next value after 2
         rs.MoveNext 'move to next record to get next value
      Loop
        
    End If
    
    End Sub

  10. #10
    Magnus1982 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2017
    Posts
    29
    Thank you. I will try today and provide feedback. However I know how to relplace column name but where exacly place this code I am not sure. This code can be a criteria for query ?

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I say not. It needs a forward look at the next record and a simple select query won't provide that if code is used as a calculated query field.
    For testing, provide a button click event or change it to a function so that you have more options for calling it.
    Last edited by Micron; 04-20-2019 at 04:57 AM. Reason: Clarification

  12. #12
    Magnus1982 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2017
    Posts
    29
    Great. Really great. I check on 300 records aND 4 intervals. And is working. Thak you . Hówever I try to modyfi your code to show alsow in the same table last record for each intervals . I coudn't

  13. #13
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Hówever I try to modyfi your code to show alsow in the same table last record for each intervals
    Not sure what that means.
    So if data looks like
    Sequence
    AB:123HJ333:323AD0145
    AB:123HJ333:323AD0146
    AB:123HJ333:323AD0147
    AB:123HJ333:323AD0148
    AB:123HJ333:323AD0149
    AB:123HJ333:323AD0150
    AB:123HJ333:323AD0180
    AB:123HJ333:323AD0181
    AB:123HJ333:323AD0182
    AB:123HJ333:323AD0204
    AB:123HJ333:323AD0205
    AB:123HJ333:323AD0206

    You wanted
    1st of a group
    1st of next group where last 4 digits is not consecutive (even if it's only one record).
    such as
    AB:123HJ333:323AD0145
    AB:123HJ333:323AD0150
    AB:123HJ333:323AD0180
    AB:123HJ333:323AD0204


    now you want
    1st of a group
    last of that group where the last 4 digits are not consecutive in the next group
    1st of next group where last 4 is not consecutive (even if it's only one record)

    new result??
    AB:123HJ333:323AD0145
    AB:123HJ333:323AD0150
    AB:123HJ333:323AD0180
    AB:123HJ333:323AD0182
    AB:123HJ333:323AD0204
    AB:123HJ333:323AD0206

  14. #14
    Magnus1982 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2017
    Posts
    29
    Exacly as you show in this example.

  15. #15
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I need another query which will show only first record of intervals
    I call attention to your initial post. What's the guarantee you don't keep changing the requirements?

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 09-24-2017, 05:30 AM
  2. Replies: 4
    Last Post: 08-22-2017, 07:14 PM
  3. Line by Line Transactional Data Queries
    By defaultuser909 in forum Queries
    Replies: 7
    Last Post: 07-18-2012, 10:00 AM
  4. Time intervals
    By Dutch1956 in forum Queries
    Replies: 5
    Last Post: 07-12-2012, 11:21 PM
  5. Replies: 5
    Last Post: 12-06-2010, 10:15 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