How to move selected rows in a query into another query

[DrChocolate]

Greetings:

As the title suggests, I want to move selected rows of query #1 into query #2. The reason for this is that query #2 will "union" with other queries.

Here's the first query (for example)

12 1 5
13 1 6
18 2 9
23 3 10
38 4 38
22 4 39
28 4 40

I want to choose ONLY the rows that have the first occurrence of a number in column two. Thus, after scanning the list, query #2 would read

12 1 5
18 2 9
23 3 10
38 4 38

My solution is not very elegant. I'd iterate query #1, take it into an array in VBA, use VBA to choose only the first occurrence, move those values into a table, and then query that table. This isn't good because it forces me to create a dummy table as a transition from the array to query #2. Clumsy and ugly.

Is there a simple but more direct way to accomplish this? Thank you!

DrC

P.S. I'm a relative beginner at VBA so if you might please be fairly specific in an answer rather than give general directions, I'll put you in my will.

 

[Peter Reid]

Unless I'm missing something (like why VBA has been mentioned and what you mean by 'moving rows' from one query to another), can't you simply use query #1 as the source for query #2 and group on the 'column' two and return the First values of 'columns' one and three?

 

[plog]

I second Peter's questioning of VBA. This sounds like something that can be done with a query and possibly a sub query.

I would need specifics about Query 1 to help you generate the data you want. What is its actual name? What are its column names? How is it ordered--what field/fields?

Also, how complex is Query 1? How many underlying data sources does it have?

 

[DrChocolate]

Solved thanks to you

Dear Peter & plog (sorry I couldn't resist),

Thanks to you both. It looks like either solution would work nicely (and be much more elegant and simpler than VBA). I hadn't seen plog's note earlier so I happened to implement Peter's, but I appreciate both of your replies.

For anyone reading this thread, if you Google [How to use a query to filter unique data in Access], you'll find the Microsoft instructions on how to use first in a query. It's a bit contra-intuitive on first reading, so be sure to follow the instructions precisely.

Dr.C.