Solved

Access Stops Responding When Creating a Report from a Query

Posted on 2006-06-14
13
280 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
Comment Utility
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
Comment Utility
also, when you run the query by itself does it work ok?
0
 

Author Comment

by:roarteam
Comment Utility
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
 

Author Comment

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

Accepted Solution

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

Expert Comment

by:puppydogbuddy
Comment Utility
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 38

Assisted Solution

by:puppydogbuddy
puppydogbuddy earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
You're welcome..this was a strange one.
J
0
 
LVL 38

Expert Comment

by:puppydogbuddy
Comment Utility
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now