How to paste many criteria in query

[Dave H]

I would like to copy and paste a large number of criteria in access. Is there a way to paste the criteria in without entering each one?

Thanks

 

[Moniker]

I explained pretty thoroughly (including an example) exactly what you're trying to do a bit ago.

You'll find it here.

 

[nate11000]

(Same person as original poster, corrected login)

Thanks Moniker. The link you provided was on a similar track, but I don't think it's quite what I'm looking for. Please bear with me, as I have experience with Excel VBA, but I'm fairly new to Access...

Here's a more specific explanation of what I'm trying to do. I have a data dump table along the lines of this:
Field1....... Field2........
AAAA....AA 123 / 12345
AAAA....AB 098 / 87654
AAAA....AC 534 / 54321
BBBB....AA 764 / 76453
BBBB....AB 987 / 98763
etc.

I then have a list of queries I need to pull similar to:
Like "AAAA....%%"

My goal is to get a list of Field2 values that match up to each of the queries and somehow get those into a table, file, or Excel row. It has to be automated because I've got ~600 queries to run on a 300,000 row table and will have to be done regularly. How can I automate running these queries and saving the results to a manageable format? Any insight or direction would be much appreciated.

 

[Moniker]

I'm not sure I understand the output you're looking for. Is this it?

FROM THIS:
Field1....... Field2........
AAAA....AA 123 / 12345
AAAA....AB 098 / 87654
AAAA....AC 534 / 54321
BBBB....AA 764 / 76453
BBBB....AB 987 / 98763

TO THIS:
Field1....... Field2........
AAAA....AA 123 / 12345....AB 098 / 87654....AC 534 / 54321
BBBB....AA 764 / 76453....AB 987 / 98763

If I don't have that right, make a quick example of the output you want.

 

[nate11000]

Yes, that's pretty much it; just drop the last two characters of your field1 (AAAA.... and BBBB....). I'd need them in separate cells in excel, but it'd be trivial to split that up once I got to that point.

 

[nate11000]

Oops, I just realized we had some misunderstanding due to formatting. Hopefully this will clear it up:

Field1 = AAAA....AA
Field2 = 123 / 12345

and so on. The periods are part of the literal text of Field1. So your output should then be:
Field1 = AAAA....
Field2 = 123 / 12345 098 / 87654 534 / 54321

and so on, or some form of delimiting text or additional fields to split up the ### / ##### text.

 

[nate11000]

Bueller? Anyone?

 

[neileg]

Do a search on Concatenate Many in these forums. This will get you sarted.

 

[nate11000]

Thanks, Neil!

 

[nate11000]

Ok, I'm still stuck on this. I'm able to run a query which pulls all the results from the larger table, but I haven't figured out how to concatenate the results of each individual 'Like' value into one row.

Using the following SQL:
SELECT [Data table].Field1, [Data table].Field2
FROM [Data table] INNER JOIN [Query table] ON ([Data table].Field1 Like [Query table].Field1) AND ([Data table].Field2 Like [Query table].Field2);

I've pulled all rows matching AAAA....?? and BBBB....??, but it returns multiple rows for each, rather than 1 row with all the necessary results. Searching for concatenation hasn't given any results, as it seems geared more towards concatenating fields 2 and 3 from one result rather than just field 2 from multiple results.

Any more guidance? Thanks!

 

[neileg]

OK, try searching for 'concatenate many side'.

 

[nate11000]

Thanks again, Neil. I found what I needed this time, here: http://www.access-programmers.co.uk/forums/showthread.php?t=117386

It's been a long learning process, but I've got the code working, finally.