Transfer first line of seqence to report

[Magnus1982]

Hello Forum,

During my work with access I am struggle with problem I'm In My Knowledge is not Enough. I have guery with specialist number in one column . Let's say
Aaa001
Aaa002
Aaa003
Aaa004

I need to transfer on raport only numbers which are first in number grup. Example:

Aaa001
Aaa002
Aaa003
Aaa006
Aaa007
Aaa008

In this example i need to transfer on raport only

Aaa001 and Aaa001

Can somebody now how to do that

Thank you with advance

 

[Magnus1982]

Sry Aaa1 and Aaa6 need to be transfered

 

[June7]

Why Aaa1 and Aaa6? What criteria determines this? Not clear what you mean by 'number group'.

 

[Magnus1982]

This only example.

Numbers do not in create all the time. Some Times is a gap between them . I need to catch first number from seqence.
For example I am płacing serial number in to data base and the product is splited for bach. First batch is from 1 to 24 and second start from 72 till 138. I need to catch this 1 and 72

 

[Gasman]

Add a field to the DB where the first record of a batch has a value of "HDR", the rest have a value of "DTL" when you add the records.

Then look for those with a value of "HDR"

 

[Magnus1982]

It is not that easy. Imagin you have target 300. You taking scaner and you scaning serial numbers. Serial numbers are mixed then when you will scan 300pcs you dont know how many grups with following numbers was created. Problemy my example was confusing. The idea is that VBA or query will go.trought all number and will cach first numbers from grup. I can not add next column and mark first line becouse during scaning i have mixed numbers

 

[gemma-the-husky]

I think you need a temporary Y/N "Include" field in the table.

Then process the number sequence by iterating a recordset, and mark the ones you want with a "Y". You will process thousands of records very quickly this way.

 

[June7]

Data always starts with 3 alpha characters? If not, should post a true representation of data.

 

[Magnus1982]

Number is long . It have almost 30 characters . Include leters ,digit, and specialist characters as : . Last four position we talking about there are starting from 0001 and rising up

 

[Magnus1982]

E.q. SN:1234TTT-1234BTY0001
SN:1234TTT-1234BTY0002
SN:1234TTT-1234BTY0003

 

[gemma-the-husky]

I think this sort of logic should output all the sequence heads.
I hope it helps you.

air code

dim lastrecord
dim newsequencemarker

lastrecord = ""
sequencemarker= ""

open recordset  (sorted on product code)
while not end of file
     read thisrecord
     if thisrecord<>"" then
         if thisrecord = lastrecord+1  then
              lastrecord=  thisrecord
         else
              'you need to report the first record in this sequence
              [COLOR="Red"]report sequencemarker[/COLOR] 
              sequencemarker = thisrecord
              lastrecord= thisrecord
         end if
     else 'first record
              sequencemarker = thisrecord
              lastrecord= thisrecord
     end if
 loop
'at the end of this loop you will need to test the last item, as it may need reporting.