Cannot switch from SQL view to Design View

[Finance_User]

Hello,

I inherited an Access 2007 DB that was created by someone who is no longer here. A particular query comes up in SQL VIEW when I go to design mode and it is not letting me view it in DESIGN VIEW.

Any ideas why that would be and how to get around that?

Thanks!

 

[bob fitz]

I may be wrong, but I don't there is a way around it. Some types of query can not be viewed in design view.

 

[Finance_User]

Thanks for your reply Bob but I hope you are wrong There is nothing special about this query. I just think the creator may have saved it this way for some reason. Thanks again.

 

[bob fitz]

Hi
I'm sorry to say that I was correct in thinking that some queries can not be created or viewed with the disign grid, but I don't know for sure that that is your proplem here. This link will give you more information on the types of query to which this applies.
http://office.microsoft.com/en-us/access-help/about-sql-queries-mdb-HP005188412.aspx

 

[Finance_User]

Thank you, sir. I appreciate your prompt replies and assistance in this matter.

 

[Finance_User]

Frustrated though because these are not pass-through or Union queries, just regular select queries.

 

[bob fitz]

Frustrated though because these are not pass-through or Union queries, just regular select queries.

Perhaps you could post one so that we can take a look at it. Preferably a short one .

 

[Finance_User]

Yes, thanks.

Hopefully you can see this attachment "picture 1.jpg" which is a screenshot that shows it is just a regular append query.

It does not even allow me to change it to a select query...thanks again!

In case you cannot see the attached, here is the code:

INSERT INTO DETAIL ( DIVISION, ID, AU, ACCOUNT, ACCOUNT_NAME, ACCOUNT_TYPE, [POSITION], JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, Oct, NOV, [DEC], 2011_TOTAL, ANNUALIZED_2010, YTD_2010_ADJ, ADJ, YTD_2010 )
SELECT "ER" AS DIVISION, "00100" & [F1] AS ID, "00100" AS AU, ER00100_Summary.F1 AS ACCOUNT, ACCOUNT.ACCOUNT_NAME, ACCOUNT.ACCOUNT_TYPE, ACCOUNT.POSITION, ER00100_Summary.F4 AS JAN, ER00100_Summary.F5 AS FEB, ER00100_Summary.F6 AS MAR, ER00100_Summary.F7 AS APR, ER00100_Summary.F8 AS MAY, ER00100_Summary.F9 AS JUN, ER00100_Summary.F10 AS JUL, ER00100_Summary.F11 AS AUG, ER00100_Summary.F12 AS SEP, ER00100_Summary.F13 AS OCT, ER00100_Summary.F14 AS NOV, ER00100_Summary.F15 AS [DEC], ER00100_Summary.F16 AS 2011_TOTAL, ER00100_Summary.F18 AS ANNUALIZED_2010, ER00100_Summary.F19 AS YTD_2010_ADJ, ER00100_Summary.F20 AS ADJ, ER00100_Summary.F21 AS YTD_2010
FROM ACCOUNT INNER JOIN ER00100_Summary ON ACCOUNT.ACCOUNT = ER00100_Summary.F1;

 

[Galaxiom]

Detail is a reserved word, being the name of a Form/Report section. It is one of the most untolerated of all reserved words. I would change that to start with.

Also try creating a new query and paste the sql into it.

BTW. That data structure is denormalized.

 

[Finance_User]

tried that. still cannot view in design mode

 

[GinaWhipp]

What is the message, if any? I know there was an issue, I thought one of the SP's fixed it but... If there was a missing table and/or field then the query would not allow Design View and you would have to fix first.

 

[Galaxiom]

I have A2007 with all updates. If a table is missing, the switch to Design View results in a "fake" table being included.

The Field box just shows
Expr1:badtablename.fieldname

 

[GinaWhipp]

Right, that is what is should happen but for a while there it didn't then an SP fixed that. Hopefully, that's all it is for the OP.