Peculiar query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 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.
 
Diogo said:
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 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.
Click to expand...

I don't think you can do this with a query. There is no way to guarantee
that records will be returned in a certain order.

And, honestly, it sounds like you may have a normalization problem.
Certainly you have a problem with your table structure in that you don't
have enough information stored in order to identify the records you need in
a query. If you post a full description of your business problem, we can
probably help you design a structure that makes the correct records easier
to select.

HTH;

Amy
 
You gonna need a field that controls the order that you pull your records for
starters. You see Access just throws the records in the storage pile without
regard as to what went in first or last unless you have a field that defines
which.

If my data is comming from Excel and I need such then I use =Row() function
in Excel and the autofill to do it for me.
 
This data is coming from a .txt file. I import it like it is, into a table.
What I need are the results between the data I refered. Could yo be a little
bit more specific. I didn't understood youur solution. Thanks
 
Amy,
I import this from a .txt file and tell access where to brake it, this is
continuum code in terms of lines. The substring identifies my store, the
zeros after that (col BBB) tell another aplication to start reading, the rest
(the part I'm interested in are transactions from my store) the last set of
zeros from (col BBB) tell the other aplication that the transactions from my
store have finished, then another store is followed with it's respective
string and transactions, wich can be more or less the my store.
Present time I search visualy for my store's start and end and print the
page where they come up (time consuming process), hence the necessity for
this query or code.
I hope you understood my problem. Thank you for any help you could share.
 
Diogo said:
Amy,
I import this from a .txt file and tell access where to brake it, this is
continuum code in terms of lines. The substring identifies my store, the
zeros after that (col BBB) tell another aplication to start reading, the
rest
(the part I'm interested in are transactions from my store) the last set
of
zeros from (col BBB) tell the other aplication that the transactions from
my
store have finished, then another store is followed with it's respective
string and transactions, wich can be more or less the my store.
Present time I search visualy for my store's start and end and print the
page where they come up (time consuming process), hence the necessity for
this query or code.
I hope you understood my problem. Thank you for any help you could share.
Click to expand...

So you're saying that all the records between the 0's are for one store,
then the next set are a different store? Then it seems that you can just
break it up on the first field where the second field does not contain all
0's. Could you be a bit more specific about what the select query is for?
Are you using it to power an append query?

-Amy
 
Diogo said:
The query is simply to select those records to print them out on a report.
Click to expand...

Please don't top post. It makes it very difficult to respond to the
entirety of the thread. I had to cut out all of the previous conversation
from this post in order to be able to put my response in a place both that
made sense and that you'd be able to find.

Then if that is what you need, then I'd just select on the store number and
put WHERE BBB <> "00000000000"

HTH;

Amy
 
Hi Diogo,

If the name of your table were "yurtable"
save the following query (changing "yurtable"
to actual name of table)

ALTER TABLE yurtable
ADD COLUMN ID
AUTOINCREMENT
CONSTRAINT PK_ID PRIMARY KEY;

then run above query after import data.

so...you will have a distinct field (ID) to identify
the "first" record (so ending record would
be first record with BBB of 0's whose ID
is greater than ID of first record)

ID AAA BBB
....... ......
5 80693000 00000000000
6 00070590 00000010003
7 00100672 21170570001
8 00350197 00021549431
9 00100113 35327580001
10 00330236 45314199513
11 01600000 00000000000
....... ......

From here, there are so many ways
to generate a report...

Will the name of the table always be the same?

If so, one way might be to base a report on
the table (with its new "ID" field).

Then, in click event of a command button to
open the report, identify the start and end ID's
and open report with those as criteria...

Say your command button was "cmdPreviewReport,"
your table name was "yurtable,"
and your report name was "rptMyStore,"
here might be your click event code:

Private Sub cmdPreviewReport_Click()
On Error GoTo Err_cmdPreviewReport_Click

Dim strDocName As String
Dim lngStart As Long
Dim lngEnd As Long
Dim strWhere As String

lngStart = DMin("ID","yurtable","Right([AAA],7) = '0693000' " _
& "AND [BBB] = '00000000000'")
lngEnd = DMin("ID","yurtable","[ID] > " & lngStart & " " _
& "AND [BBB] = '00000000000'")

strDocName = "rptMyStore"
strWhere = "[ID] >= " & lngStart & " AND [ID] <= " & lngEnd
DoCmd.OpenReport strDocName, acPreview, , strWhere


Exit_cmdPreviewReport_Click:
Exit Sub

Err_cmdPreviewReport_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewReport_Click

End Sub
 
Gary
Yours seems to be the most promising anwser yet.
Two things: Access complains about not knowing Right() function.
There are several other collums between the two AAA and BBB colums, I didn't
write them for simplicity. Those this code import them too?
How do I setup the report, where will it know where to put the choosen
records?
Thanks

Gary Walter said:
Hi Diogo,

If the name of your table were "yurtable"
save the following query (changing "yurtable"
to actual name of table)

ALTER TABLE yurtable
ADD COLUMN ID
AUTOINCREMENT
CONSTRAINT PK_ID PRIMARY KEY;

then run above query after import data.

so...you will have a distinct field (ID) to identify
the "first" record (so ending record would
be first record with BBB of 0's whose ID
is greater than ID of first record)

ID AAA BBB
....... ......
5 80693000 00000000000
6 00070590 00000010003
7 00100672 21170570001
8 00350197 00021549431
9 00100113 35327580001
10 00330236 45314199513
11 01600000 00000000000
....... ......

From here, there are so many ways
to generate a report...

Will the name of the table always be the same?

If so, one way might be to base a report on
the table (with its new "ID" field).

Then, in click event of a command button to
open the report, identify the start and end ID's
and open report with those as criteria...

Say your command button was "cmdPreviewReport,"
your table name was "yurtable,"
and your report name was "rptMyStore,"
here might be your click event code:

Private Sub cmdPreviewReport_Click()
On Error GoTo Err_cmdPreviewReport_Click

Dim strDocName As String
Dim lngStart As Long
Dim lngEnd As Long
Dim strWhere As String

lngStart = DMin("ID","yurtable","Right([AAA],7) = '0693000' " _
& "AND [BBB] = '00000000000'")
lngEnd = DMin("ID","yurtable","[ID] > " & lngStart & " " _
& "AND [BBB] = '00000000000'")

strDocName = "rptMyStore"
strWhere = "[ID] >= " & lngStart & " AND [ID] <= " & lngEnd
DoCmd.OpenReport strDocName, acPreview, , strWhere


Exit_cmdPreviewReport_Click:
Exit Sub

Err_cmdPreviewReport_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewReport_Click

End Sub


Diogo said:
This data is coming from a .txt file. I import it like it is, into a
table.
What I need are the results between the data I refered. Could yo be a
little
bit more specific. I didn't understood youur solution. Thanks
Click to expand...
Click to expand...
 
Back
Top