Solved

Access Stops Responding When Creating a Report from a Query

Posted on 2006-06-14
13
285 Views
Last Modified: 2008-01-09
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.status_id And a.expected_status_id=s2.status_id And a.td_id=t.td_id;

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!


0
Comment
Question by:roarteam
  • 5
  • 5
  • 3
13 Comments
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 16903753
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.
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 16903788
also, when you run the query by itself does it work ok?
0
 

Author Comment

by:roarteam
ID: 16904080
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:roarteam
ID: 16904141
I tried removing all the aliases and it still locks up.
0
 
LVL 34

Accepted Solution

by:
jefftwilley earned 250 total points
ID: 16904219
Does the Simple push button Auto-Report work ?
J
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 16904346
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.
0
 
LVL 38

Assisted Solution

by:puppydogbuddy
puppydogbuddy earned 250 total points
ID: 16905699
After you have gone thru the exercise with the footers  and have ruled out the footers as being part of the problem, unload your report without saving, then place report i design view again, then highlight and delete one  control (textbox) in the detail section, run the report - look for improvement....none??....highlight and delete next detail control without saving...repeat the process until you find the problematic one(s)....................
0
 

Author Comment

by:roarteam
ID: 16912836
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.
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16912967
Are you able to create a report based on another table or query? There could be some kind of corruption going on.
J
0
 

Author Comment

by:roarteam
ID: 16913420
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!
0
 

Author Comment

by:roarteam
ID: 16913444
Not sure how Accepted Answer and Assisted Answer affects your ratings, but I hope the way I did it is ok.

Thanks again.
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 16914138
You're welcome..this was a strange one.
J
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 16916249
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
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question