Query problem

[Timtropolis]

Greetings all,

I am running a query (see attached) that runs fine on its own.

However, as soon as I make it a recordsource for my report. It hangs and locks up Access.

I've tried to get around this problem by making this query into a "make table" or "append" query but with the same results. I further attempted to use a separate query to take the results of this first query and either make a table or use it as record source, but again, Access locks up.

I'm baffled as to why this is happening. Why would it work in datasheet mode/run mode on its own but crash when it becomes attached to a report?

Any ideas??

TIA,
Tim

 

[Timtropolis]

The query DOES make sense and as I stated , it DOES work. It just hangs as I explained above and that does not make sense. I'm looking for an opinion here as to why this occurs, I'm not looking to make whole sale changes to the query or the report.

Anyone else?

 

[The_Doc_Man]

When you say

It hangs and locks up Access.

This is ambiguous to folks who have experience in troubleshooting. "Hang" means many things.

1. You cannot get Windows to respond to anything including CTL-ALT-DEL, and sometimes even the mouse's pointer becomes stationary. The only "out" is the power switch. (Old programmer's axiom: A computer's attention span is never longer than its power cord.)

2. Windows is fine but Access isn't responding. Eventually you get a pop-up that actually SAYS "Access is not responding" and asks for permission to kill it. The first attempt might or might not work but eventually Access really does go away.

3. You cannot get any output, no report page comes up, but Windows eventually responds to the CTL-Break key combination.

Please clarify which kind of "hang" it is.

Here is food for thought. If you can run the Windows Task Manager before you start Access, or at least before you start the report, you would have a way to see what is going on inside the machine. A flare-lit tip-off that you are having problems with the scope of that query - as Pat suggested - would be that when you open the report, your Access task suddenly catapults into the 95%+ usage category even though you get no output. If you have a performance monitor and start that, looking at disk reads (non-system), the "disk reads" category goes up to some peak that represents the maximum I/O rate for that disk on your system.

Here is something else to look at. A report, particularly if built by a wizard, often includes an implied filtration or ordering or grouping query based on the original table or query identified as its basis. Like, you told the Wizard you wanted headers/footers of a particular field, and the only way that can possibly work is a GROUP BY on that field, which might not be in your "raw" query. The wizard takes care of that for you by writing the query you need to organize the data you want.

Look at the report properties to see what its recordsource really is. If it is merely the name of the query you built, I have no advice. But if it is an implied query that "reads" your query, copy that implied query to become a "real" query. (It was "real" anyway but it wasn't quite so obviously visible while in the recordsource of the report.) Now open that copy of the query and see what it really does. If your "raw" query runs but this copy of the report's hidden query hangs, you have been analyzing the wrong query.