Query anomaly

[shortone]

What would cause a query to only allow SQL and datasheet views, but not design view? I have a query, based off of a Union query, that, at one time, permitted me to see all views, in fact, I developed it in design view. Now, after correcting some errors in the union query and some Excel importing glitches (I had two fields in the query twice) , the query will not allow me back into design view and crashes the database (thankfully, it's a test db!). Is there a way to repair it? Have I given enough information?

Inquiring minds want to know..... JayeD
"I'm sure of two things: there IS a God, and I'm NOT Him!" - R. Williams

 

[DanJR]

I'm not sure why your query is crashing...have you tried copying the SQL syntax to a new query? Can you still view the query in 'SQL View'. Can you run the query without error?

Even with queries that are not classified as "SQL Specific" by MS Access, it is definately possible to build SQL queries that can not be viewed via the QBE (because the QBE can't represent some property within the SQL statement, in this case, the join), e.g:
[tt]
SELECT Min(A.OrderID+1) AS MinOrderID
FROM YourTableName AS A LEFT JOIN YourTableName AS B ON A.OrderID = B.OrderID-1
WHERE b.OrderID is Null;
[/tt]
However, the QBE just gives an error if you try to view it and doesn't crash.

Cheers,
Dan

 

[tlbroadbent]

A UNION query cannot be viewed in the Design View grid. It is SQL-Specific query that the Access Design view cannot interpret. I've never been able to view a SQL-Specific query in the design view.

I don't know how you could have created it except in the SQL view as there are no options for Unions in the Design view. As soon as you select one of the SQL specific queries, Access switches to SQL view. This has been my experience from Access 2 through Access 2000. I can't say if Access 2002 is different because I have not used it. If you want to get the best answer for your question read faq183-874.

Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[annsolomon]

JayeD, Terry is correct about getting Union queries only in the SQL view but I think you're telling us that you have a query which is built on a different (Union) query. If that's the case, you should be able to open the second query in design view without problem in most cases. (Note the example exception Dan mentioned.)

Have you tried Dan's suggestion yet? Are you getting an error message before the crash? Ann

 

[shortone]

Hi all!
I've built the query from an Union query. I've not tried Dan's suggestion yet, but I will. I don't get an error message just before crashing, though, and I can see it in SQL view. The thing that I find strangest is that I built the query in QBE view FIRST. Thankfully, I am able to run it without any problems and have successfully linked it to an Excel spreadsheet. I was just trying to figure out why this is happening, so I don't repeat the same mistake the next time.....

I will try Dan's suggestion and get back to you. Thanks for all your help! JayeD
"I'm sure of two things: there IS a God, and I'm NOT Him!" - R. Williams