By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,509 Members | 2,201 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,509 IT Pros & Developers. It's quick & easy.

How do I create an In-Statement in MS Access using records from a table?

P: n/a
My problem is as follows:

I need automatically iterate through a single field in a table and use
the values in the field to create an in-statement. Currently, the
character limitation in the "Zoom" feature of the MS Access query
builder limits the number of characters I can enter into my
In-Statement. As a result, I need to iterate through a particular
field (Customer ID Numbers) and use each record as a criteria in a
query. I want to be able to:
Step 1: Starting at record #1, create an in-statement for records 1-50
Step 2: For records 51-100, execute the same query and append the results to the table previously created in step 1.
Step 3: Without knowing how many records are in the table, iterate through the field each time running the same query and appending the results to the table created in step 1 in blocks of 50 and ending when the last record in the table is recognized.

Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
"Peter Phelps" <pp*****@ups.com> wrote in message
news:45**************************@posting.google.c om...
My problem is as follows:

I need automatically iterate through a single field in a table and use
the values in the field to create an in-statement. Currently, the
character limitation in the "Zoom" feature of the MS Access query
builder limits the number of characters I can enter into my
In-Statement. As a result, I need to iterate through a particular
field (Customer ID Numbers) and use each record as a criteria in a
query. I want to be able to:
Step 1: Starting at record #1, create an in-statement for records 1-50
Step 2: For records 51-100, execute the same query and append the results to the table previously created in step 1. Step 3: Without knowing how many records are in the table, iterate

through the field each time running the same query and appending the results
to the table created in step 1 in blocks of 50 and ending when the last
record in the table is recognized.

I can't tell whether this will be suitable for your situation, but instead
of building up a vast list of CusID numbers to be used in the IN clause,
have you considered using a sub-query?

SELECT CusID, CusCompany, CusAccountNo
FROM tblCustomers
WHERE CusID IN
(SELECT OrdCusID FROM tblOrders
WHERE OrdDate > #12/31/2002#)

The query above gives me in effect all the customer ID's for orders placed
this year. This beats going through the orders table and manually
extracting the CusID.
Could you use this, or had you already considered this?

Fletcher
Nov 12 '05 #2

P: n/a
I'm not 100% sure I'm following what you need, but I'll take a
guess.....you need to get 50 records at a time...if you add a new temp
field and:
1. init it to NULL
2.then select the top 50 where the new field is NULL - this will give
you the first 50
3. set the first 50 to some non-NULL char (such as 'Y')
This way you can select 50 recs at a time....

Perhaps what you're looking for is a way to compare records in a table
against themselves??? If so you can join a table to itself and compare
fields against each other - for example

Select a.*, b.*
From Table1 as a Left Join Table2 as b
On a.field1 = b.field1 and a.field2 <> b.field2.....etc.
pp*****@ups.com (Peter Phelps) wrote in message news:<45**************************@posting.google. com>...
My problem is as follows:

I need automatically iterate through a single field in a table and use
the values in the field to create an in-statement. Currently, the
character limitation in the "Zoom" feature of the MS Access query
builder limits the number of characters I can enter into my
In-Statement. As a result, I need to iterate through a particular
field (Customer ID Numbers) and use each record as a criteria in a
query. I want to be able to:
Step 1: Starting at record #1, create an in-statement for records 1-50
Step 2: For records 51-100, execute the same query and append the results to the table previously created in step 1.
Step 3: Without knowing how many records are in the table, iterate through the field each time running the same query and appending the results to the table created in step 1 in blocks of 50 and ending when the last record in the table is recognized.

Nov 12 '05 #3

P: n/a
pp*****@ups.com (Peter Phelps) wrote in
news:45**************************@posting.google.c om:
My problem is as follows:

I need automatically iterate through a single field in a table
and use the values in the field to create an in-statement.
Currently, the character limitation in the "Zoom" feature of
the MS Access query builder limits the number of characters I
can enter into my In-Statement.
Is what you REALLY want to create an in statement that contains all
the values of the field?
As a result, I need to iterate
through a particular field (Customer ID Numbers) and use each
record as a criteria in a query. I want to be able to:
If yes, simply use the following as the Criteria in your query:
IN (SELECT [Customer ID Numbers] from Table)
instead of typing each [Customer ID Numbers] into the Zoom box.

This gets around the limitation you face.

Bob Q.
Step 1: Starting at record #1, create an in-statement for
records 1-50 Step 2: For records 51-100, execute the same
query and append the results to the table previously created
in step 1. Step 3: Without knowing how many records are in
the table, iterate through the field each time running the
same query and appending the results to the table created in
step 1 in blocks of 50 and ending when the last record in the
table is recognized.


Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.