concatenate 2 queries (1 Viewer)

[casey]

Hello,

Is there a way to concatenate two queries into one statement?

I have 2 queries with the same field, but one selects records from all types that are not ="R" and the second selects all from a single specified type = "R":

select fld1 from tbl1 where fldType !="R";
select fld1 from tbl1 where fldType ="R";
(I'm not sure if ! or Not is appropriate here?????)

I would like to join these two into one query. The reason for doing this is so that fields with type ="R" will be listed after those that are not ="R". Type "R" are less important so I would like them listed last. Sort by fldType doesn't help because it sorts fldType alphabetically and doesn't necessarily list type "R" last.

I haven't included the queries because they're long. If needed I can do so. I appreciate any ideas.

 

[TessB]

Are these 2 queries identical but just being filtered, one with the R type and the other with everything else not R? If so, I can't understand why you wouldn't just remove the criteria on that field. So these queries much return different data in each Yes?

But the sorting question, I may have an answer. How about an expression in your query such as this:

TypeR : IIf ([Type] is like "R", [Type], "")
And then you can sort descending on that field instead of the original Type field, bringing all your R's to the bottom for you.


glad hopes,
Tess

 

[Opengrave]

I am pretty sure what you are looking for is a Union query. Basically it says take the results of query2 and append them to the results of query1. There are some catches as to exactly how this can be done but it sounds lke it might work. Use Access help and search on Union Query for help/rules. To actually use this go into the query in design view then from the menu bar select QUERY->SQL SPECIFIC->UNION QUERY. You have to manually enter the SQL but that sounds like it should just be a cut-and-paste.

 

[RichMorrison]

Based on the conditions in the original post, Tess's solution is better.

RichM

 

[casey]

Thanks for your advice.

TessB, I see what you're saying about using the iif statement. Instead of fldType"R", nothing shows up in the fldType field when that condition is met. Then sort on that field. That makes the solution simple for me. Thanks for setting me straight.

 

[TessB]

Wonderful! I'm so happy I could help.