Querying 500 records

[AJOLSON]

I have a table with 40,000 records in it. I am running a query where I want
to return 500 of those records based on the content of one of the primary
field. In Excel I have a field that takes the primary key and formats it in
this order; 45999 or 34210 or 12423………….. This repeats for as many records
as I wish, in this case 500.

My problem is this when I copy and paste the field from excel into the
criteria section of the query in access I get an error message saying “The
Text is to long to Editâ€. It will only allow me to paste about 50 records
in instead of the 500 records I wish to return.

Is there a work around?

 

[Michel Walsh]

Enter the 500 values in a table, one field (primary key), 500 records.

Use an inner join between your initial table and that temp table.


Vanderghast, Access MVP

 

[AJOLSON]

There is not temp table. Only the one table with 4K records. Are you saying
create a new table with the 500 records then innerjoin? That would work.
But not to user freindly for the novice. HUmmm but that does lead to other
posiblities
thanks

 

[Michel Walsh]

Yes. That is what I meant. You create a temp table.

You process is in two steps, once to empty then fill the temp table (easy to
fill it, with an Excel range, I assume), and, finally, to get the data from
the Access query.



Vanderghast, Access MVP

 

[AJOLSON]

Yes I am setting that up. Still limts them to 6500 records but hey if they
need that much well come see me and I extract the data for you LOL.