Access Statement not Compatible with SQL server

[CharlieT302]

Hi Folks,

I am having a problem retrieving data from an SQL server database. The data was originally and Access database. The front-end uses Access (it has to stay that way) The back-end is an Access database converted to SQL.

I have a query that displays records only if two or more contain the same client number. My statement is below.

In (SELECT [CLIENT_NUMBER] FROM [Qry_Report_ODA_Waiting_List_Names_ON] As Tmp GROUP BY [CLIENT_NUMBER] HAVING Count(*)>1 )

Here is my question:

Apparently there is something about this statement that the SQL server doesn't like. Is there another way of writing this that is compatible?

Thanks

 

[SQLDenis]

this part should run just fine, what happens when you run this in query analyzer?

SELECT [CLIENT_NUMBER] FROM [Qry_Report_ODA_Waiting_List_Names_ON] As Tmp
GROUP BY [CLIENT_NUMBER]
HAVING Count(*)>1




Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[bborissov]

This kind of query runs perfectly here. Can yo upost whole query and tell us what is the error message.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[CharlieT302]

Hi,

Actually, we never get an error message. The query simply hangs up and never produces anything. We have sat and watched it for over twenty minutes. There were only 400 records in the underlying data source.

On another note, we actually have two versions of the database. One is completely in Access (front end and backend). It works fine and the report only takes a few seconds. Only the one using the SQL backend hangs up.

There are additional criteria on other fields, but nothing complex. Ex: "Is Null" The query in question uses another query as the data source. That query runs perfectly on its own; and quickly. Although, there is really no criteria placed upon it.

I am not at the office right now, otherwise I would upload the entire SQL criteria. But, honestly, not much else to tell. I am perplexed that the critera ran for you. I was sure it was an incompatibility issue.

 

[alaniane]

Your problem maybe that your surrounding the query with []. SQL Server thinks that the query is a table name. Try removing the [] and using () for the inner query in the from statement.