roarteam
asked on
Access Stops Responding When Creating a Report from a Query
I have created a set of tables in Access 2003. The tables are as follows...
DEVELOPERS
DEV_ID - Number
DEV_NAME - Text
TD_ITEMS
TD_ID - Number
DESCR - Text
STATUSES
STATUS_ID - Number
DESCR - Text
STATUS_ENTRIES
STATUS_ENTRY_ID - AutoNumber
TD_ID - Number
CURRENT_STATUS_ID - Number
EXPECTED_STATUS_ID - Number
STATUS_DATE - Date/Time
PROGRESS_DESCR - Memo
EXPECTED_DESCR - Memo
ISSUES - Memo
DEV_ID - Number
The tables currently each have less than 10 rows, so there is a very small amount of data. I have written a query to gather the information from which I want to create a report. The query is as follows.
SELECT a.TD_ID AS [TD Item], t.descr AS [TD Item Descr], a.status_date AS [Status Date], a.progress_descr AS [Progress Descr], a.expected_descr AS [Expected Status Descr], a.issues AS Issues, d.dev_name AS Developer, s1.descr AS [Current Status], s2.descr AS [Expected Status]
FROM status_entries AS a, developers AS d, statuses AS s1, statuses AS s2, td_items AS t
WHERE a.dev_id=d.dev_id And a.current_status_id=s1.sta
I am using the report wizard to create a report from the query. I select every field in the query to be shown on the report. When I click Next from the screen where I have selected the fields, the Access process pegs the CPU at 99% and the Status in the Task Manager changes to Not Responding. I have let it sit like that for 15+ minutes and the application seems to be locked up. I have tried it several times with the same results.
Has anyone seen this before? Is there a solution to this problem?
Thanks!
DEVELOPERS
DEV_ID - Number
DEV_NAME - Text
TD_ITEMS
TD_ID - Number
DESCR - Text
STATUSES
STATUS_ID - Number
DESCR - Text
STATUS_ENTRIES
STATUS_ENTRY_ID - AutoNumber
TD_ID - Number
CURRENT_STATUS_ID - Number
EXPECTED_STATUS_ID - Number
STATUS_DATE - Date/Time
PROGRESS_DESCR - Memo
EXPECTED_DESCR - Memo
ISSUES - Memo
DEV_ID - Number
The tables currently each have less than 10 rows, so there is a very small amount of data. I have written a query to gather the information from which I want to create a report. The query is as follows.
SELECT a.TD_ID AS [TD Item], t.descr AS [TD Item Descr], a.status_date AS [Status Date], a.progress_descr AS [Progress Descr], a.expected_descr AS [Expected Status Descr], a.issues AS Issues, d.dev_name AS Developer, s1.descr AS [Current Status], s2.descr AS [Expected Status]
FROM status_entries AS a, developers AS d, statuses AS s1, statuses AS s2, td_items AS t
WHERE a.dev_id=d.dev_id And a.current_status_id=s1.sta
I am using the report wizard to create a report from the query. I select every field in the query to be shown on the report. When I click Next from the screen where I have selected the fields, the Access process pegs the CPU at 99% and the Status in the Task Manager changes to Not Responding. I have let it sit like that for 15+ minutes and the application seems to be locked up. I have tried it several times with the same results.
Has anyone seen this before? Is there a solution to this problem?
Thanks!
check the field list in the report designer and compare it to the column names used in your query. Are the aliases on the report field list? The wizard may have a glitch when it comes to picking up aliases.
also, when you run the query by itself does it work ok?
ASKER
I know that it is a little jumbled, but the results of the query are below. It does work and it returns what I expect. I am trying the alias thing now.
TD Item TD Item Descr Status Date Progress Descr Expected Status Descr Issues Developer Current Status Expected Status
369 Session Refresh 6/9/2006 Waiting on BEA Waiting on BEA BEA reassigned our case and is now suggesting solutions and asking questions that have been delt with in the past. I sent them all our source code and some previous emails today. They wanted me to make another test case and I said I didn't have time. They currently have a test case that fails everytime it is run. Marshall Mooney On Hold On Hold
420 CSR/Service Order Lookup 6/9/2006 Almost done Surely this thing will stop dragging out. Marshall Mooney Working - Main Assignee Completed
378 Release Details Docs 6/9/2006 I am assisting Trey with design decisions. It is going well. Trey works pretty quickly Marshall Mooney Working - Assisting Completed
427 Release Notes Sort/Preview 6/9/2006 I haven't officially started this one, yet. I may or may not start this one next week Marshall Mooney N/A N/A
469 Queue Monitor Interface 6/9/2006 Assigned to Uday. We spent some time discussing design This will take some time. Marshall Mooney Working - Assisting Working - Assisting
420 CSR/Service Order Lookup 6/9/2006 Completed the entity bean. There were changes about the site id. Created a new web service which retrieves a service order given a shak. The existing retrieve SO service uses CBS which needs the service order number. Vikas Boyanapalli Working - Assisting N/A
404 Generic Cris Memo 6/9/2006 Started on the design Should have about 10 hours work left Vikas Boyanapalli Working - Main Assignee Working - Main Assignee
348 BBWR Temp Output 6/9/2006 Jeff Nicholson Working - Main Assignee N/A
462 SOEC 3-char socs ids 6/9/2006 Had to go back and make enhancement per Johnnie Davis Jeff Nicholson Completed N/A
479 % Total Column 6/9/2006 Had to go back and make more efficient code changes Jeff Nicholson Completed N/A
482 5194 Daily Report 6/13/2006 Still sitting in IT Review status Jeff Nicholson N/A N/A
469 Queue Monitor Interface 6/9/2006 Uday Karnum N/A Working - Assisting
378 Release Details Docs 6/13/2006 Almost finished - Should be done with construction today Totally complete on Monday Trey Edwards Working - Main Assignee N/A
TD Item TD Item Descr Status Date Progress Descr Expected Status Descr Issues Developer Current Status Expected Status
369 Session Refresh 6/9/2006 Waiting on BEA Waiting on BEA BEA reassigned our case and is now suggesting solutions and asking questions that have been delt with in the past. I sent them all our source code and some previous emails today. They wanted me to make another test case and I said I didn't have time. They currently have a test case that fails everytime it is run. Marshall Mooney On Hold On Hold
420 CSR/Service Order Lookup 6/9/2006 Almost done Surely this thing will stop dragging out. Marshall Mooney Working - Main Assignee Completed
378 Release Details Docs 6/9/2006 I am assisting Trey with design decisions. It is going well. Trey works pretty quickly Marshall Mooney Working - Assisting Completed
427 Release Notes Sort/Preview 6/9/2006 I haven't officially started this one, yet. I may or may not start this one next week Marshall Mooney N/A N/A
469 Queue Monitor Interface 6/9/2006 Assigned to Uday. We spent some time discussing design This will take some time. Marshall Mooney Working - Assisting Working - Assisting
420 CSR/Service Order Lookup 6/9/2006 Completed the entity bean. There were changes about the site id. Created a new web service which retrieves a service order given a shak. The existing retrieve SO service uses CBS which needs the service order number. Vikas Boyanapalli Working - Assisting N/A
404 Generic Cris Memo 6/9/2006 Started on the design Should have about 10 hours work left Vikas Boyanapalli Working - Main Assignee Working - Main Assignee
348 BBWR Temp Output 6/9/2006 Jeff Nicholson Working - Main Assignee N/A
462 SOEC 3-char socs ids 6/9/2006 Had to go back and make enhancement per Johnnie Davis Jeff Nicholson Completed N/A
479 % Total Column 6/9/2006 Had to go back and make more efficient code changes Jeff Nicholson Completed N/A
482 5194 Daily Report 6/13/2006 Still sitting in IT Review status Jeff Nicholson N/A N/A
469 Queue Monitor Interface 6/9/2006 Uday Karnum N/A Working - Assisting
378 Release Details Docs 6/13/2006 Almost finished - Should be done with construction today Totally complete on Monday Trey Edwards Working - Main Assignee N/A
ASKER
I tried removing all the aliases and it still locks up.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I didn't mean for you to remove the aliases, sorry you went to all the trouble. Even with the aliases removed, you still need to compare the field names in the report designer to the column names used in the query. To see the field names in the report designer, click the button near the sorting/grouping button.
However, try a couple of quick things, first to see if you can block out the cause of the problem:
Make a backup copy of the report.
Take one of your copies and place it in design view. While you have it in design view, highlight the bottom footer section, delete the section and run the report without saving your changes. Any better? ...no...place report in design view again...highlight the next footer......delete that section and run report again without saving. Repeat the process for each section of the report, exept the detail section......and do not save the changes........hopefully by the end of this process, you will have identified a report section or sections as problem areas.
However, try a couple of quick things, first to see if you can block out the cause of the problem:
Make a backup copy of the report.
Take one of your copies and place it in design view. While you have it in design view, highlight the bottom footer section, delete the section and run the report without saving your changes. Any better? ...no...place report in design view again...highlight the next footer......delete that section and run report again without saving. Repeat the process for each section of the report, exept the detail section......and do not save the changes........hopefully by the end of this process, you will have identified a report section or sections as problem areas.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
jefftwilley: I tried the autoreport and it locked up, as well.
puppydogbuddy: I can't create a report at all. That is the problem. The wizard never gets that far.
puppydogbuddy: I can't create a report at all. That is the problem. The wizard never gets that far.
Are you able to create a report based on another table or query? There could be some kind of corruption going on.
J
J
ASKER
OK.... Here's what I did. I ditched the wizard and created a report from scratch using the designer. That worked. It seems the wizard may be a little flakey. I'm not sure exactly how to handle the points, but since you guys (jefftwilley and puppydogbuddy) gave it the old gung hoe try and I am going another route before you had a real chance to solve the original problem, I'll split up the points. Thanks!
ASKER
Not sure how Accepted Answer and Assisted Answer affects your ratings, but I hope the way I did it is ok.
Thanks again.
Thanks again.
You're welcome..this was a strange one.
J
J
roarteam,
Just got home. I am glad you got it resolved. Thanks for the points and grade. Sorry that I misunderstood your problem...I thought that your problem was that you couldn't get the report to execute, but that you had a report definition with textboxes that you could view in the designer.
PDB
Just got home. I am glad you got it resolved. Thanks for the points and grade. Sorry that I misunderstood your problem...I thought that your problem was that you couldn't get the report to execute, but that you had a report definition with textboxes that you could view in the designer.
PDB