Non-SQL Specific union queries

[DonJ]

Hello all,

I'm trying to combine the results of 2 Access gui-created queries across the same tables using slightly different criteria. Is it possible to do this without rewriting the whole thing in SQL? I'll admit up front I can do that, but the queries are pretty complex and I'd prefer not to. At least when I'm lazy I admit it.

Thanks,
Don

 

[Jon K]

Create a Union Query based on your two queries as follows:-

SELECT Field1, Field2, ... etc
FROM query1
UNION
SELECT Field1, Field2, ... etc
FROM query2

 

[DonJ]

Hi Jon,

I apprecate the help, but I'm afraid I need more. What I have is 2 very successful, fairly complex querieries built using Jet SQL that I need to join. I can rewrite them and use a simple join in Transact SQL with a high degree of effort, but I'd like to avoid that. Is it possible to build a query in Acces by simply joining 2 other Access queries?

Thanks,
Don

 

[Pat Hartman]

That is exactly what Jon's sample is showing you how to do. Leave your queries as they are and reference them in the Join.

Select * From query1
Union Select * from query2;

If you need to convert the queries to Transact SQL, you can open the queries in SQL view and copy/paste them to a new query in SQL view. I am not familiar with the syntax differences between Access SQL and Transact SQL so I don't know if you'll need to make any changes.

A join is NOT the same as a union. If you want a join, just open the QBE grid and select your two queries. Draw the appropriate join line and select the fields you want from both queries.

Access SQL allows queries and tables to be used interchangeably. Think of Access querydefs as views.

 

[DonJ]

Thanks to you both

Good morning,

First, thanks so much for the advice. It works perfectly! Second, I apologize for my misuse of syntax and my inability to see the forest for the trees. I definitely meant union and not join. It's pretty hard to help someone who can't even get his questions straight. Finally, I had no idea I could call an internal Access query from a SQL Specific query. I thought that was reserved for direct access to tables in a server resident DB. It's very valuable information in my world.

Many thanks,
Don

 

[Pat Hartman]

It is Pass-Through queries that cannot use any Access objects. If the union query were also a Pass-Through query, to Oracle for example. It would need to be in Oracle SQL syntax and it could not use any Access objects. The reason that Pass-Through queries cannot use Access objects is obvious if you think about it. How would Oracle or DB2 or whatever know anything about Access.

Union queries are SQL specific (meaning that the syntax is potentially different for each implementation of SQL), but you are using Access SQL so since it is an Access query, it can use Access objects.

 

[DonJ]

Installed today. It worked like a charm & we're heroes. I have no option but to take all the credit, though. I hope you don't mind. I hope you don't mind.

DJ