Solved

Access Stops Responding When Creating a Report from a Query

Posted on 2006-06-14
13
287 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

623 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