Query is not producing result

[sahilsoni]

Whenever I am running a query in which FIRST function of access is used, qury just hangs up and does not produce any result. could any one guide on this?
I am using MS Access 2000 and OS is windows server 2003 and SQL 8.0

[sierra7]

Hi
You will need to post the details of your query. Open it in Design View, then click View, then SQL View

Then copy and paste the test to the forum

An idication of whet you are truing to achieve might also be useful

S7

[NeoPa]

Another point to consider :
Dou you really want First(), or would maybe the DISTINCTROW predicate of the SELECT clause suit you better?

[sahilsoni]

This is the query I am trying to run

SELECT [CLIR] AS zCLIR, First([LIR]) AS zLIR, First([Prefix]) AS zPRE, First([FirstName]) AS zFN, First([MI]) AS zMI, First([LastName]) AS zLN, First([StandardTitle]) AS zTL FROM [sql_DDMContact] GROUP BY CLIR HAVING CLIR >= 0

This query always hangs but when I remove the first() function it works out.
Please guide on this. this table sql_DDMContact is a link table in mdb

[jaxjagfan]

This is the query I am trying to run

SELECT [CLIR] AS zCLIR, First([LIR]) AS zLIR, First([Prefix]) AS zPRE, First([FirstName]) AS zFN, First([MI]) AS zMI, First([LastName]) AS zLN, First([StandardTitle]) AS zTL FROM [sql_DDMContact] GROUP BY CLIR HAVING CLIR >= 0

This query always hangs but when I remove the first() function it works out.
Please guide on this. this table sql_DDMContact is a link table in mdb

Does the CLIR value occur multiple times, assigned to different people and you are trying to get the 1st person it was assigned to?

Otherwise:

SELECT [CLIR] AS zCLIR, [LIR] AS zLIR, [Prefix] AS zPRE, [FirstName] AS zFN, [MI] AS zMI, [LastName] AS zLN, [StandardTitle] AS zTL
FROM [sql_DDMContact]
WHERE CLIR >= 0
GROUP BY CLIR, LIR, Prefix, FirstName, MI, LastName, StandardTitle

If it is a very large dataset in your linked tables and you are using First, Last, Min, Max type values you may want to go to the properties of the query and set TimeOut to 0. This will prevent the 60 sec timeout.

[NeoPa]

Firstly, a reminder to everyone to use [ CODE ] tags where possible. This is a requirement of the site and it is there for very good reasons.

Moving on :)
I would only make a minor amendment to your SQL which is to use WHERE instead of HAVING. This may make little difference as the optimiser should catch it for you. The DISTINCT predicate won't help you as there is no (discernable) Primary Index.

SELECT [CLIR] AS zCLIR,
       First([LIR]) AS zLIR,
       First([Prefix]) AS zPRE, 
       First([FirstName]) AS zFN,
       First([MI]) AS zMI,
       First([LastName]) AS zLN,
       First([StandardTitle]) AS zTL
FROM [sql_DDMContact]
WHERE CLIR >= 0
GROUP BY CLIR

As Jax has said though, change the default timeout value. 0 is fine or I often use 300. That is 5 minutes and in most cases that should return some results or something is wrong. Another setting I'd change would be the Recordset Type from Dynaset to Snapshot.