Peculiar Code

[Diogo]

OK I hope I can explain myself the best as possible:
My data presents it self like this in a table. Always! Several hundred lines.

AAA BBB
....... ......
80693000 00000000000
00070590 00000010003
00100672 21170570001
00350197 00021549431
00100113 35327580001
00330236 45314199513
01600000 00000000000
....... ......

What I need is the following:
Need a query or code that looks for the substring "0693000" of the first
line,
substring because the first number can change, and then if the corresponding
collum BBB is full of "00000000000" it selects all records until the one
where it finds the next value of "00000000000" in collum BBB. Basically all I
need are the values bettween the zeros of collum BBB (col AAA and col BBB).
Is this possible? Or do I need VB code. Any way could someone layout the
code or the query for me. Thanks a lot.

 

[Sergey Poberezovskiy]

It is pretty hard to advise a method with this limited knowledge of the data
structure. If you could answer the following questions, I will try to write
an SQL for you:
-do you have a unique identifier on your table
- are the records sorted in any particular order
- what output are you looking for - how many columns, what do you want
displayed in every column

 

[Diogo]

Sergey
I want the query to select the records I displayed.
The "......" I put there represent the rest of data above and below this
segment, wich I don't care for.
I don't have unique identifier
Records have to be sorted by col BBB
I want the same display as the table but just want those records I displayed.
Thanks in advance for the help.

 

[Sergey Poberezovskiy]

Lack you unique identifier is posing a problem. If the table is not huge (say
no more than a few dozen of thousands rows), is it possible to create a temp
table with an Autonumber field and the two fields of interest.
Otherwise we will need to use VBA, which may not be as efficient.

Please indicate which way we need to go

 

[Diogo]

Man I have no idea , we can go wich ever way you think is more
efficient, probably the one with an temp table an auto number Autonumber...
you choose...

 

[Sergey Poberezovskiy]

I have just sent you a reply with comments - and my session has expired, so
the reply was lost. So here is just the code:

select
a.AAA,
a.BBB
from
tblAutoNumbered As a,
(select t.Id,
t.AAA,
( select Min(Id)
from tblAutoNumbered
where Id > t.Id
and BBB = '00000000000'
) As MaxID
from tblAutoNumbered As t
where AAA like '?0693000'
group by
t.Id,
t.AAA
) As b
where a.Id Between b.Id And b.MaxId + 1
or (b.MinId is null
and a.Id >= b.Id)
order by
b.AAA,
a.BBB

if some things are not clear - I will be happy to answer any questions

 

[Sergey Poberezovskiy]

Just ensure that you have set your ID column as primary key and you may also
want to index the other two columns for performance

 

[John W. Vinson]

OK I hope I can explain myself the best as possible:
My data presents it self like this in a table. Always! Several hundred lines.

AAA BBB
....... ......
80693000 00000000000
00070590 00000010003
00100672 21170570001
00350197 00021549431
00100113 35327580001
00330236 45314199513
01600000 00000000000
....... ......

What I need is the following:
Need a query or code that looks for the substring "0693000" of the first
line,
substring because the first number can change, and then if the corresponding
collum BBB is full of "00000000000" it selects all records until the one
where it finds the next value of "00000000000" in collum BBB. Basically all I
need are the values bettween the zeros of collum BBB (col AAA and col BBB).
Is this possible? Or do I need VB code. Any way could someone layout the
code or the query for me. Thanks a lot.

You're thinking spreadsheet.

An Access table HAS NO ORDER. It's an unordered "bag" of records. The concept
"until it finds the next" simply has no meaning in the relational context!

Where does this data come from? Are you (I hope) importing it from some
(ordered!) external text file or spreadsheet or other source?


John W. Vinson [MVP]

 

[Diogo]

John i'm importing this from a .txt file and data comes organized like I
showed it but above an below there arre records wich I don't need I just want
those I layed out.

 

[Diogo]

Sergey it doesn't seem to work, access doesn't know what is a.AAA or a.BBB.
Could you please help?

 

[Sergey Poberezovskiy]

Diogo,

The assumption is that you import your text file into tblAutoNumber (and let
Access assign Primary key for you). After you imported the data, ensure that
you have renamed the relevant columns to the names that we use in the query,
namely:
Primary Key column - ID
The first column (as you defined in the very first post) - AAA
the second column - BBB

I just went with the names you have specified. During Import process, unless
you specify column names in "Advanced" button of import wizard, the column
names may not be the same.

Let me know how you go.

 

[Diogo]

where am I suposed to put this code?

Diogo,

The assumption is that you import your text file into tblAutoNumber (and let
Access assign Primary key for you). After you imported the data, ensure that
you have renamed the relevant columns to the names that we use in the query,
namely:
Primary Key column - ID
The first column (as you defined in the very first post) - AAA
the second column - BBB

I just went with the names you have specified. During Import process, unless
you specify column names in "Advanced" button of import wizard, the column
names may not be the same.

Let me know how you go.

 

[Sergey Poberezovskiy]

into a new query

where am I suposed to put this code?

 

[Diogo]

Sergey
when I run the query it asks for t.AAA parameter, a.AAA parameter, a.BBB
parameter and b.MinId parameter...
Any thoughts?

 

[Sergey Poberezovskiy]

Sorry, my bad - I have a typo:

select
a.AAA,
a.BBB
from
tblAutoNumbered As a,
(select t.Id,
t.AAA,
( select Min(Id)
from tblAutoNumbered
where Id > t.Id
and BBB = '00000000000'
) As MinID
from tblAutoNumbered As t
where AAA like '?0693000'
group by
t.Id,
t.AAA
) As b
where a.Id Between b.Id And b.MinId - 1
or (b.MinId is null
and a.Id >= b.Id)
order by
b.AAA,
a.BBB

if this does not work - (I checked it on my sample database - it runs fine),
could you please send me your database (I do not need the whole lot, just the
table & query), so that I can tell you what needs to change to
(e-mail address removed)

 

[Diogo]

Many Thanks Sergey, it's working fine now.
No words to thank you.

 

[John W. Vinson]

John i'm importing this from a .txt file and data comes organized like I
showed it but above an below there arre records wich I don't need I just want
those I layed out.

I see Sergey has given you a working solution; thanks to him, and
congratualtions to you!

John W. Vinson [MVP]