Query works from Design View, but not when you directly open it

[MarrowGenx]

Below is a query that I put in the SQL view and appears to work perfectly when I view it in design view then open it. If I try to open it directly or reference it in another query it does not work. Any ideas?

SELECT F.Project_Number
FROM tblSpending AS F LEFT JOIN [SELECT Project_Number FROM tblSpending WHERE Spending_Type='forecast - previous month'; ]. AS P ON F.Project_Number = P.Project_Number
WHERE (((F.Spending_Type)='forecast') AND ((P.Project_Number) Is Null))
GROUP BY F.Project_Number;

Marrow

 

[sxschech]

In SQL view change the square [] brackets to curved () brackets and remove the semicolon that you have before the square bracket as well as the dot.

SELECT F.Project_Number
FROM tblSpending AS F LEFT JOIN [red]([/red]SELECT Project_Number FROM tblSpending WHERE Spending_Type='forecast - previous month'[red])[/red] AS P ON F.Project_Number = P.Project_Number
WHERE (((F.Spending_Type)='forecast') AND ((P.Project_Number) Is Null))
GROUP BY F.Project_Number;

For some reason, access converts the ( to [ but when you try to view it in design view, it doesn't like it, so you have to go in and change them back to ( in order to see it. Also, sometimes access puts in funny characters for an alias and you may need to delete those as well.

 

[MarrowGenx]

Thank you for your help, but even with your updated SQL I am still having the same issues. Here is exactly what is happening:

1. When I am in design view or SQL view and run/open the query it comes up with no problems.

2. When I am at the query listing in access and run the query or reference the query in another query, I get the following: "The Microsoft Jet database engine cannot find the input table or query 'SELECT Project_Number FROM tblSpending WHERE Spending_Type='forecast - previous month';'.

When I make the changes you have suggested, it always reverts back to the original SQL statement (as seen in my first post), including the brackets and semi-colon.

Any other ideas/suggestions?

Marrow

 

[sxschech]

"The Microsoft Jet database engine cannot find the input table or query 'SELECT Project_Number FROM tblSpending WHERE Spending_Type='forecast - previous month'[red];[/red]'.

The error message shows a semicolon, so am wondering if you were able to remove the semicolon or did access put it back along with the square brackets? Please try one more time to remove the semicolon and see if that works.

I tried a sample query in Access 2007 using the MDB 2003 file format based on your select statement and didn't produce an error message. If that still doesn't work, perhaps you could provide some sample data and I'll see what I can do. I

 

[MarrowGenx]

Taking out the semicolon did not produce any differences. My original post on this topic was located below. This shoud show you the tables I am trying to combine and what I am trying to do with this subquery:

http://www.tek-tips.com/viewthread.cfm?qid=1642580&page=1

Perhaps there is a better way to write the SQL query than what I am currently using.

Marrow

 

[lameid]

You didn't by chance have a query with name that begin's with select? If so Access is confused by it.

Copy and paste the SQL text from above into a new query.
Save the query.
Switch to design view.
Save the Query.
Close the query.
Run the query.

Confirm it works or post the resultant SQL statement from the query.


Alternately...

Save the SQL that is the subquery to a new query.
Make sure it runs save and save it with an approriate name.
Replace the subquery including parenthesis with this query name in your SQL statement.

 

[MarrowGenx]

The name of the query does not start with "Select" and does not have "Select" in the name. I even copied the SQL and moved it to a new query, but the results are the same. Here are the 3 queries that I made that give me the same results:

qryZZZCalculation-NewForecast#1

SELECT tblSpending.Project_Number
FROM tblSpending
WHERE (((tblSpending.Spending_Type)="Forecast"))
GROUP BY tblSpending.Project_Number;

qryZZZCalculation-NewForecast#2

SELECT tblSpending.Project_Number
FROM tblSpending
WHERE (((tblSpending.Spending_Type)="Forecast - Previous Month"))
GROUP BY tblSpending.Project_Number;

qryZZZCalculation-NewForecast#3

SELECT [qryZZZCalculation-NewForecast#1].Project_Number
FROM [qryZZZCalculation-NewForecast#1] LEFT JOIN [qryZZZCalculation-NewForecast#2] ON [qryZZZCalculation-NewForecast#1].Project_Number = [qryZZZCalculation-NewForecast#2].Project_Number
WHERE ((([qryZZZCalculation-NewForecast#2].Project_Number) Is Null))
GROUP BY [qryZZZCalculation-NewForecast#1].Project_Number;

Is there a simple way to rewrite this? These three queries work perfectly, I just wanted a simpler way to run them. Please excuse the query names, as they are just temporary.

Thanks again for everyone's help.


Marrow

 

[PHV]

What about this ?

SELECT Project_Number
FROM tblSpending
WHERE Spending_Type='forecast' AND Project_Number NOT IN
 (SELECT Project_Number FROM tblSpending WHERE Spending_Type='forecast - previous month')
GROUP BY Project_Number

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MarrowGenx]

This one works every way I try to open it. Thanks again, PHV.

Also, thanks to lameid and sxschech for helping me try to resolve this issue.

Marrow

 

[TheAceMan1]

How are ya MarrowGenx . . .

Just for reference, if you break your origional SQL down for easier viewing the following missing parenthese in [red]red[/red] is revealed:

[blue]SELECT F.Project_Number
FROM tblSpending AS F 

LEFT JOIN 

  (SELECT Project_Number 
   FROM tblSpending 
   WHERE Spending_Type='forecast - previous month') AS P 
   ON F.Project_Number = P.Project_Number[red][b])[/b][/red]

WHERE (((F.Spending_Type)='forecast') AND ((P.Project_Number) Is Null))
GROUP BY F.Project_Number;[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]