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

[Peter Phelps]

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.

[Fletcher Arnold]

"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

[MeadeR]

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.

[Bob Quintal]

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.