By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,656 Members | 1,882 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,656 IT Pros & Developers. It's quick & easy.

Query is not producing result

P: 2
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
Jan 28 '08 #1
Share this Question
Share on Google+
5 Replies


Expert 100+
P: 446
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
Jan 28 '08 #2

NeoPa
Expert Mod 15k+
P: 31,712
Another point to consider :
Dou you really want First(), or would maybe the DISTINCTROW predicate of the SELECT clause suit you better?
Jan 28 '08 #3

P: 2
This is the query I am trying to run
Expand|Select|Wrap|Line Numbers
  1. 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
Jan 31 '08 #4

jaxjagfan
Expert 100+
P: 254
This is the query I am trying to run
Expand|Select|Wrap|Line Numbers
  1. 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:
Expand|Select|Wrap|Line Numbers
  1. SELECT [CLIR] AS zCLIR, [LIR] AS zLIR, [Prefix] AS zPRE, [FirstName] AS zFN, [MI] AS zMI, [LastName] AS zLN, [StandardTitle] AS zTL
  2. FROM [sql_DDMContact]
  3. WHERE CLIR >= 0
  4. 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.
Jan 31 '08 #5

NeoPa
Expert Mod 15k+
P: 31,712
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT [CLIR] AS zCLIR,
  2.        First([LIR]) AS zLIR,
  3.        First([Prefix]) AS zPRE, 
  4.        First([FirstName]) AS zFN,
  5.        First([MI]) AS zMI,
  6.        First([LastName]) AS zLN,
  7.        First([StandardTitle]) AS zTL
  8. FROM [sql_DDMContact]
  9. WHERE CLIR >= 0
  10. 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.
Jan 31 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.