473,837 Members | 1,604 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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
3 13833
"Peter Phelps" <pp*****@ups.co m> wrote in message
news:45******** *************** ***@posting.goo gle.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.

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
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.....et c.
pp*****@ups.com (Peter Phelps) wrote in message news:<45******* *************** ****@posting.go ogle.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
pp*****@ups.com (Peter Phelps) wrote in
news:45******** *************** ***@posting.goo gle.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.
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
2557
by: Daylor | last post by:
can i do it this way : from the defualt AppDomain : create thread. from the start function of the new thread , create new AppDomain,and then create my class through the AppDomain . this way is ok ? or there is other way to do that ?
2
1502
by: Amil | last post by:
example: // form classes: // ItemEditor // CategoryEditor private void OpenForm(string formClassName) { // I want to be able to instantiate a form based on the parameter. if (formClassName == "ItemEditor" ) ItemEditor editor = new ItemEditor();
9
3233
by: Bob Achgill | last post by:
I would like to use the timestamp on files to manage the currency of support files for my VB windows application. In this case I would only put the timestamp of the file in the management database and not the file itself. To do this I will need to have a File class property for Create time and date that will let me "set" the Create time and date of the file to my own chooseing. The VB file class does not appear to have the ability
17
8508
by: Jeffrey W. Baker | last post by:
Greetings, I have a 23GB data table upon which I am building a primary key of three columns. The data is mounted in a 137GB device and pg_xlog is mounted on a separate 3.5GB device. I have configured 24 checkpoint segments, which I expect gives me a worst-case usage in pg_xlog of 384MB. Unfortunately, during the CREATE INDEX, pg_xlog becomes full!
7
4340
by: MarkoH | last post by:
Wsdl.exe /server creates abstract class derived from WebService. Is there a way to create this class at runtime based on some WSDL file given at runtime ? What would be even better - creating concrete class but with empty method implementation of course since WSDL does not provide any info on implementation. Thanks M.
4
1603
by: Jeff | last post by:
hey ASP.NET 2.0 I'm preparing for a certification exam on asp.net 2.0 and yesterday I took a skill assessment test on microsoft.com. One of the questions was about creating cookies. This was the question: "Which object or objects will you use to create and retrieve cookies"... 6 alternative answer were listed, below I show only 2 most relevant alternatives
1
13656
by: orenbt78 | last post by:
Hi, I am trying to: 1. Create a SQL database (I am working with SQL 2005 Express) 2. with a C# code 3. when the user is not the computer administrator. I have managed to create the database file (code below). I am not sure it is the right way. Can you take a look please?
3
1309
by: gillian3114 | last post by:
i got 3 type username and password : 1. boss 2. manager 3. staff how should i check if the username is exist then pop up msgbox error else add username and password to database
4
16697
by: nano2k | last post by:
Hi For some purposes, I need to create a new binary file and to allocate a fixed length for that file on the disk. I need something like File.Create(string fileName, long initialSizeInBytes). Is there a framework/system method to do that or should I create the file and then fill it until the size is reached? Thanks
1
1285
by: Damodhar | last post by:
hi any one can help me to create the folder and sub folders for ex my project root is : test i want to create the folder like files/images/personal/profile/album/ damu i want to create the folder structure like
0
9695
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10902
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10583
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10286
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7824
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7013
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5680
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5863
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4060
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.