Query based on results from another query

[Ryan]

I am new to Acccess, but I have been searching them boards for quite a while
and have not found the info for my situation. I want to be able to do this
in Access without VB if possible. If it requires VB, then I will please tell
me and I will learn the necessary VB to do it.

I have a table of Bank Branches with each record being a unique branch of a
bank. The relevant fields for my query are Bank ID (BKID), Branch ID (BRID)
and Branch County (BC). I need to be able to query for a particular bank
which will return all branches of that bank and their respective counties. I
then want to "re-query" for the list of all branches of all banks in those
counties. In other words: query for a specific BKID returns list of BRID and
BC - that list of BC becomes new query criteria that returns new list of
BRIDs which are all banks that are in the counties of the branches of the
original bank.

Thanks for any responses and I apologize if this is a something simple I
should know.

 

[Tom Ellison]

Dear Ryan:

Don't apologize. Ignorance is only a sin if you aren't trying to do
something to remove it.

What you need first is a list of counties in which the beginning bank has
branches. Make sense?

SELECT DISTINCT BC
FROM SomeTable
WHERE BKID = 1234

Then find all banks with branches in those counties:

SELECT DISTINCT BKID
FROM SomeTable
WHERE BC IN (
SELECT DISTINCT BC
FROM SomeTable
WHERE BKID = 1234)

You need to change the SomeTable to the actual name of your table, and you
need to have some way of selecting the BKID, replacing where I put in 1234.

Tom Ellison

 

[Ryan]

Tom, thanks for the reply! Please bear with me as I try to learn. I am
learning VB by trial and error and I thought it would be easiest to start
with some basic code that just displayed the list of counties for a given
bank. Here's my code:

----
Public Sub Test()

Dim BankID As Integer
Dim Result As String

BankID = InputBox(Prompt:="What bank?")
Result = SELECT DISTINCT Branch.STCNTYBR FROM Branch WHERE Branch.CERT =
BankID

MsgBox (Result)

End Sub
----

Of course this does not work because it says I have the wrong syntax on the
"SELECT". What am I doing wrong?

Once I have this working, then I should be able to add the additional SELECT
DISTINCT statement to get the result I need. After that, I will move on to
passing the resulting list of records to a report.

 

[Tom Ellison]

Dear Ryan:

I'm afraid it looks like you have a ways to go.

First, if you are writing for Access, it's technically not VB but VBA. Such
technical differences are actually very important in communication with
others. VB is a considerably different animal.

Assuming you really are writing Access VBA, you've got a ways to go.

I will try to give you a few clues. But what you need is a book. That is
to say, the concepts you will need to study, absorb, and practice cannot be
easily transmitted just through a newsgroup. It is a stucy, while the
newsgroup is useful for occasional hints. I hope you can see the
difference.

The code to execute the query would need to build the query string first.
Some of the key lines of this would be like this:

Dim strSQL As String

strSQL = "SELECT DISTINCT Branch.STCNTYBR FROM Branch WHERE Branch.CERT = "
& CStr(BankID)

At this point you can test the code to make sure the proper query string has
been generated. You can take that string and paste it into a query's SQL
and see if it runs and gives what you want.

Put in these lines of code next:

Debug.Print strSQL
Stop

At this point, you can open and view the Immediate Pane. You should see the
value of strSQL, which can be executed as a query. That's what you can
paste into the SQL View of a new query and test.

I don't expect you are thinking this will do the thing you asked from me
before. Have you tested that yet? Do you know how?

I'm not sure what your level of experience and aptitude are, nor can I
really settle into the mode where I can lead you through each step. That's
probaby properly the role of a book or of a school course.

Tom Ellison

 

[Ryan]

Thanks for your help, Tom. I thought I could write some quick code and be
done since I only need to do one task for now. I definitely have a lot to
learn, so I'll get a book tonight and continue the learning process. You
have been more than patient and I appreciate your help. Thanks again!

 

[Ryan]

OK, now I get it! For some reason, I assumed I had to use VBA to do what I
needed. Now i see that what you originally listed was simple query script
for the SQL View in Access. Sorry for being so dull!!

But now when I test the original script, the "nested" SELECT fails to work
even though if I run each SELECT separately, they each work.

For example, this:

SELECT DISTINCT STCNTYBR
FROM Branch
WHERE CERT = ( 3792 )

produces the correct list of County IDs, and if I manually enter that list
of County IDs into the next SELECT, like this:

SELECT DISTINCT CERT
FROM Branch
WHERE STCNTYBR = ( 17101, 17191, 17193 )

it produces the correct list of Bank IDs. But if I combine them into a
single script like this:

SELECT DISTINCT CERT
FROM Branch
WHERE STCNTYBR IN (

SELECT DISTINCT STCNTYBR
FROM Branch
WHERE CERT = 3792 )

then the cursor goes to an hourglass for a few seconds and then Access just
locks up. It's as though the calculation is too much for Access.

If I have about 92,000 records in this database, should Access be able to
process this request? If not, is there a way to do this using 2 separate
steps?

 

[Douglas J Steele]

Do you have an index on Cert in your Branch table? Do you have an index on
STCNTYBR in your Branch table?

 

[Ryan]

BINGO!! Putting on the indices worked like a charm. Thanks to all for your
help!!

 

[Tom Ellison]

Dear Doug:

Yes, the very thing! Stole all my thunder!

Tom Ellison