Query of Multiple Items in One Search

[AmySmith]

I have an Access database that links one form to one table, so a fairly simple database. The database will average 250k records. How do I query to pull up many (ex. 100) distinct records.

Example - I want to search in a field called 'debtornumber' and want to pull up lots of various account numbers (ex - 0458714 and 0587428 and 0578925 and....). I have hundreds of the individual items to query daily (that I can paste from Notepad or word) and need to pull up all items in the query at one time.

Help!! Thanks in advance!

 

[Mihail]

Let me understand, please.

In Notepad you have something like this:
0458714, 0587428, 0578925, ......

You hunt each number, select it, CTRL+C, switch to the query, CTRL+V

Is this your situation ?
If YES this can be easily handled.
Of course if you have a pattern in the Note Pad.

 

[Pat Hartman]

Welcome aboard
If you have "table" type data in notepad, excel, word you can import it into a temp table and then join that table to your query to select specific records all at once.

 

[AmySmith]

Mihail,
YES, correct. Can you explain how I go about it please?

 

[nanscombe]

Do you know if debtornumber is stored as a number or a text string?

If it is a number then it importing the data straight into an Access table would be Ok as it will simply be stored as a number.

If it is a string it may be slightly more complicated because Access will probably try and drop any leading "0" and store it as a number which won't match up with your debtornumber field.

If it is a number, you'll get a match:
Notepad: 0578925
Import: 578925
debtornumber: 578925

If it is a string, it won't match:
Notepad: 0578925
Import: 578925
debtornumber: "0578925"

ETA:
Ah, I see you've answered me below.

 

[AmySmith]

I have debtornumber set as PLAIN TEXT because when I tried to establish debtornumber as a number, it drops the leading zero and I need to keep that in the debtornumber field.

 

[nanscombe]

Do you mean the field in the table is set up as Data Type TEXT?

Setting a Textbox, on a form, to Text Format PLAIN TEXT is not the same thing.

 

[AmySmith]

Sorry, yes, the data type is text, not a number.

 

[Mihail]

Due to my English is not very easy to explain.
In certain conditions, Pat's approach is the best.
When I read your post, my first idea was to allow you to do a SINGLE copy and a SINGLE paste.

If you can, upload the database. No need for all records. 20 of them are more than enough. And no need for "true" data if that violates some confidential rules.
Also, upload the .txt file (the Note Pad file that you use).
I'll design an approach for you.
And I'll help you to embed in your database.

If you can't upload, please show us, at least, the query's SQL.
But, if you can upload what I have asked for, I can help you at maximum.

Cheers !

 

[AmySmith]

Mihail - do you have an email I could send to you directly?

 

[Mihail]

Of course. But I received some criticism about that.
This is my mail address : alt.mihai@yahoo.com

I am waiting for your message.

As an advice:
If you also upload your database here, on the forum, you will receive better input from more skilled guys than me.

 

[AmySmith]

Attached with dummy data and took everything out of the database that wasn't relevant (tables, queries, reports, etc.).

 

[Mihail]

Can't open your database.
I use Access 2007, so your database is in a newer version.
So, convert it in a previous version (better in 2003 version, as .mdb) and upload again.

 

[nanscombe]

Are the numbers in your file just separated by commas?

Example 1:
0000001, 0000002, 0000003, 0000004, 0000005, 0000006, 0000007, 0000008, 0000009, 0000010, 0000011, 0000012, 0000013, 0000014, 0000015, 0000016, 0000017, 0000018, 0000019, 0000020, 0000021, 0000022, 0000023, 0000024, 0000025, 0000026, 0000027, 0000028, 0000029, 0000030, 0000031, 0000032, 0000033, 0000034, 0000035, 0000036, 0000037, 0000038, 0000039, 0000040, 0000041, 0000042, 0000043, 0000044, 0000045, 0000046, 0000047, 0000048, 0000049, 0000050, 0000051, 0000052, 0000053, 0000054, 0000055, 0000056, 0000057, 0000058, 0000059, 0000060, 0000061, 0000062, 0000063, 0000064, 0000065, 0000066, 0000067, 0000068, 0000069, 0000070, 0000071, 0000072, 0000073, 0000074, 0000075, 0000076, 0000077, 0000078, 0000079, 0000080, 0000081, 0000082, 0000083, 0000084, 0000085, 0000086, 0000087, 0000088, 0000089, 0000090, 0000091, 0000092, 0000093, 0000094, 0000095, 0000096, 0000097, 0000098, 0000099, 0000100


Or are they neatly split into rows, some with commas, others just on a new line?

Example 2:
0000001, 0000002, 0000003, 0000004, 0000005, 0000006, 0000007, 0000008, 0000009, 0000010
0000011, 0000012, 0000013, 0000014, 0000015, 0000016, 0000017, 0000018, 0000019, 0000020
0000021, 0000022, 0000023, 0000024, 0000025, 0000026, 0000027, 0000028, 0000029, 0000030
0000031, 0000032, 0000033, 0000034, 0000035, 0000036, 0000037, 0000038, 0000039, 0000040
0000041, 0000042, 0000043, 0000044, 0000045, 0000046, 0000047, 0000048, 0000049, 0000050
0000051, 0000052, 0000053, 0000054, 0000055, 0000056, 0000057, 0000058, 0000059, 0000060
0000061, 0000062, 0000063, 0000064, 0000065, 0000066, 0000067, 0000068, 0000069, 0000070
0000071, 0000072, 0000073, 0000074, 0000075, 0000076, 0000077, 0000078, 0000079, 0000080
0000081, 0000082, 0000083, 0000084, 0000085, 0000086, 0000087, 0000088, 0000089, 0000090
0000091, 0000092, 0000093, 0000094, 0000095, 0000096, 0000097, 0000098, 0000099, 0000100

 

[nanscombe]

The database in the attached Zip file will allow to copy and paste text into a text box in form frmExtractDebtorNumbers and extract the numbers, as text strings, into the table tmpDebtor.

It doesn't matter which of the two above formats the numbers are in as it will cope with both.

Each time it is run it will clear out the old values before extracting the new ones.

This should get us off to a good start

ETA:
(Newer version over the page)

 

[nanscombe]

In this version I have added the parts from your database and created:

qrySCA_T_SelectedOnly - A query which joins the tables SCA_T and tmpDebtor
SCA_F_SelectedOnly - A screen which show data based on qrySCA_T_SelectedOnly

 

[AmySmith]

Nigel,
It looks like this will work! I will test this week with live data and let you know if I have any questions. Many, many thanks!!

 

[nanscombe]

You're welcome.

 

[AmySmith]

Nigel,
The query works very well with one exception and I can't figure out how to correct it. When the query runs it does pull all of the debtor numbers I am looking for, but it does a distinct find for the debtor numbers. Usually, the same debtor number is in my databases 3-4 times and I need it to pull up every occurrence of that debtor number and not just one. Can you tell me how to manipulate the last version you posted to correct that issue?

 

[nanscombe]

Are you talking about the numbers in the table my code produces?

If so, any one number in my table should be able to match multiple records in your table.

ETA:
Just tried manually adding a record, with a duplicate debtor number, to the SCA_T table and it does show more than one record.