Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

unique records causing Access to loop 1

Status
Not open for further replies.
psimon88

psimon88

IS-IT--Management
Jul 21, 2005
66
US
Hello

I am running a report in Access 2003 off of DB2. I am pulling multiple records for an event and am using 'select unique' to pare them down.

Unfortunately, when I do this, it causes Access to loop. My time-out setting is 600 seconds and the data set isn't that large (2,000 records).

Anyone know why this is happening and what I can do to fix this?

Thanks in advance.

ps
 
Sort by date Sort by votes
if you paste the select statement into a query (just to test it) will it run there?

DougP, MCP, A+
 
Upvote 0 Downvote
It's a select query, not an update or MTQ one.

I'm not sure that I follow the question.
 
Upvote 0 Downvote
How are you running your query...thru code? If you have code for this, please post it.
 
Upvote 0 Downvote
OK...you asked for it! :)

Thanks

***************

SELECT DISTINCT qry_EMPLOYEE_FIELDTERMS4.COMPANY, qry_EMPLOYEE_FIELDTERMS4.STORE, qry_EMPLOYEE_FIELDTERMS4.EMPLOYEE, qry_EMPLOYEE_FIELDTERMS4.LAST_NAME, qry_EMPLOYEE_FIELDTERMS4.FIRST_NAME, qry_EMPLOYEE_FIELDTERMS4.EMP_STATUS, qry_EMPLOYEE_FIELDTERMS4.DEPARTMENT, qry_EMPLOYEE_FIELDTERMS4.[DEPT NAME], qry_EMPLOYEE_FIELDTERMS4.JOB_CODE, qry_EMPLOYEE_FIELDTERMS4.[JOB TITLE], qry_EMPLOYEE_FIELDTERMS4.[JOB CL], qry_EMPLOYEE_FIELDTERMS4.PAY_RATE, qry_EMPLOYEE_FIELDTERMS4.[ADJ HIRE], qry_EMPLOYEE_FIELDTERMS4.[Store Region], qry_EMPLOYEE_FIELDTERMS4.[Store District], qry_EMPLOYEE_FIELDTERMS4.TERM_DATE, qry_EMPLOYEE_FIELDTERMS4.TERMMONTH, qry_EMPLOYEE_FIELDTERMS4.TERMYEAR, LAWDB2_PERSACTHST.ACTION_CODE, LAWDB2_PERSACTHST.EFFECT_DATE, LAWDB2_PERSACTHST.REASON_01, qry_EMPLOYEE_FIELDTERMS4.EEO_CLASS, qry_EMPLOYEE_FIELDTERMS4.SEX, qry_EMPLOYEE_FIELDTERMS4.BIRTHDATE, qry_EMPLOYEE_FIELDTERMS4.ANNIVERS_DATE
FROM qry_EMPLOYEE_FIELDTERMS4 LEFT JOIN LAWDB2_PERSACTHST ON (qry_EMPLOYEE_FIELDTERMS4.EMPLOYEE = LAWDB2_PERSACTHST.EMPLOYEE) AND (qry_EMPLOYEE_FIELDTERMS4.COMPANY = LAWDB2_PERSACTHST.COMPANY)
WHERE (((LAWDB2_PERSACTHST.ACTION_CODE)="TERM") AND ((LAWDB2_PERSACTHST.REASON_01) Like "TER*") AND ((Month([EFFECT_DATE]))=[Forms]![frm_intro]![month]) AND ((Year([EFFECT_DATE]))=[Forms]![frm_intro]![year]));
 
Upvote 0 Downvote
Well, I meant VBA code...how are you kicking off the query? There's obviously a form (frm_intro) where the user enters a month and year. Is there any VBA code behind this form?
 
Upvote 0 Downvote
This may be a stupid question, but is your form (frm_intro) actually open when you run this query? I'm sure it is, since you're not getting an error message.

The only thing I can see from your SQL that could slow this query down is the Like "TER*". I would suggest posting this to the Access Queries and Jet SQL forum (
 
Upvote 0 Downvote
What is "qry_EMPLOYEE_FIELDTERMS4"? I'm guessing that's another query. Have you tried running that query on it's own? Does it take forever to run? If so, can you post the SQL for that query?
 
Upvote 0 Downvote
Yes, it is another query

SELECT qry_EMPLOYEE_FIELD1.COMPANY, qry_EMPLOYEE_FIELD1.PROCESS_LEVEL AS STORE, qry_EMPLOYEE_FIELD1.EMPLOYEE, qry_EMPLOYEE_FIELD1.LAST_NAME, qry_EMPLOYEE_FIELD1.FIRST_NAME, qry_EMPLOYEE_FIELD1.EMP_STATUS, qry_EMPLOYEE_FIELD1.DEPARTMENT, qry_EMPLOYEE_FIELD1.[DEPT NAME], qry_EMPLOYEE_FIELD1.JOB_CODE, qry_EMPLOYEE_FIELD1.[JOB TITLE], qry_EMPLOYEE_FIELD1.[JOB CL], qry_EMPLOYEE_FIELD1.PAY_RATE, qry_EMPLOYEE_FIELD1.[ADJ HIRE], qry_EMPLOYEE_FIELD3.[Store Region], qry_EMPLOYEE_FIELD2.[Store District], qry_EMPLOYEE_FIELD1.TERM_DATE, qry_EMPLOYEE_FIELD1.TERMMONTH, qry_EMPLOYEE_FIELD1.TERMYEAR, qry_EMPLOYEE_FIELD1.EEO_CLASS, qry_EMPLOYEE_FIELD1.SEX, qry_EMPLOYEE_FIELD1.BIRTHDATE, qry_EMPLOYEE_FIELD1.ANNIVERS_DATE
FROM (qry_EMPLOYEE_FIELD1 LEFT JOIN qry_EMPLOYEE_FIELD2 ON (qry_EMPLOYEE_FIELD1.EMPLOYEE = qry_EMPLOYEE_FIELD2.EMPLOYEE) AND (qry_EMPLOYEE_FIELD1.COMPANY = qry_EMPLOYEE_FIELD2.COMPANY)) LEFT JOIN qry_EMPLOYEE_FIELD3 ON (qry_EMPLOYEE_FIELD1.EMPLOYEE = qry_EMPLOYEE_FIELD3.EMPLOYEE) AND (qry_EMPLOYEE_FIELD1.COMPANY = qry_EMPLOYEE_FIELD3.COMPANY)
WHERE (((qry_EMPLOYEE_FIELD1.TERMMONTH)=[Forms]![frm_intro]![month]) AND ((qry_EMPLOYEE_FIELD1.TERMYEAR)=[Forms]![frm_intro]![year]));




 
Upvote 0 Downvote
So does this query (which I see is based on yet another query) run ok???
 
Upvote 0 Downvote
The root of the problem could be that you have query on top of query on top of query. Nested queries will generally take longer, since all layers of queries need to run. Each layer of query you add will add to the length of time to run.
 
Upvote 0 Downvote
I think what rjoubert is trying to say here, and was one of my first thoughts, is that the query you originally asked about is probably running poorly due to the relational data.

You seem to be using several sequential queries and all of these seem to be related out to other queries and tables. The time it takes to process all that is adding up and causing the final query to "appear" slow.

You need to start from the first query and see how much time each level takes. Look for the first one that has a major time jump and see if it can be optimized. Then continnue on...

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer
 
Upvote 0 Downvote
Yes.... that's the crazy thing. I removed the TER* and left(reason,3) = TER and tied it to another query. I'm really baffled. Thanks again.

 
Upvote 0 Downvote
So, would you guys recommend creating a temp table?
Reporting off of static data would solve the problem, no?

 
Upvote 0 Downvote
That might help...but I would take a look at the root query and work my way out, as I described.

What is the table structure like and what are the expected results? Let's start from the beginning and see if we can help you correct the root instead of using temp tables if we can...

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer
 
Upvote 0 Downvote
Yes.... that's the crazy thing. I removed the TER* and left(reason,3) = TER and tied it to another query. I'm really baffled. Thanks again.

 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

caykamanj1966
  • Locked
  • Question
Duplicate Records in DataSheet View
Replies
5
Views
259
Andrzejek
Andrzejek
Xavierparkash
  • Locked
  • Question
MS Access Slow Search in Linked Database
Replies
0
Views
149
Xavierparkash
Xavierparkash
PrimoSteve
  • Locked
  • Question
Cannot add Image to Command Button?
Replies
9
Views
321
PrimoSteve
PrimoSteve
renigar
  • Locked
  • Question
Trying to add hyperlinks to pdfs through form entry
Replies
0
Views
161
renigar
renigar
TheresAlwaysAWay
  • Locked
  • Question
"There isn't enough memory to perform this operation..." error after upgrading from Access
Replies
2
Views
161
Axworthy
Axworthy

Part and Inventory Search

Sponsor

Back
Top