Report takes too long to open

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello, all,

I have a report that shows the total number of records in a stored query by
putting a text box in the report and setting its control source to a field in
the query, such as

=Dcount("InmateId", "qryHU1AUpper")

I have about 50 of these on the report in the detail section and the form
takes forever to open("formatting page . . . press control + break to stop").

I am at a loss how to overcome this. I have created separate queries that
perform the counting task

SLELECT Count(InmateId) FROM tblInmates etc. . .

but the report wizard (though it says I can choose from more that one table
or query) won't let me use them. ("You have chosen fields from record sources
which the wizard can't connect.") I then tried union-ing all of the count
queries together into one and that works except the duplicates are removed -
not what I want. The DISTINCT keyword has no effect.

I feel I'm missing the easier solution here. I need to create a report that
displays the count of records from multiple queries, but doesn't take 30
seconds to open. Thanx to any who can help.

Ripper
 
RipperT said:
I have a report that shows the total number of records in a stored query by
putting a text box in the report and setting its control source to a field in
the query, such as

=Dcount("InmateId", "qryHU1AUpper")

I have about 50 of these on the report in the detail section and the form
takes forever to open("formatting page . . . press control + break to stop").

I am at a loss how to overcome this. I have created separate queries that
perform the counting task

SLELECT Count(InmateId) FROM tblInmates etc. . .

but the report wizard (though it says I can choose from more that one table
or query) won't let me use them. ("You have chosen fields from record sources
which the wizard can't connect.") I then tried union-ing all of the count
queries together into one and that works except the duplicates are removed -
not what I want. The DISTINCT keyword has no effect.

I feel I'm missing the easier solution here. I need to create a report that
displays the count of records from multiple queries, but doesn't take 30
seconds to open. Thanx to any who can help.
Click to expand...


I think you need to show us a sample of these 50 queries and
explain more about what you're trying to count. Especially,
let us know if these queries are essentially the same except
for different criteria.

Is the report an unbound report (i.e. its Record Source is
blank)? If the Record Source is not blank, how does the
table/query relate to all these other queries?

Are these counts of the same field but with different
values? If so, there are better ways to do that than use a
sequence of DCounts.

Note that, in my opinion, using a wizard to create anything
beyond a trivial report is more complicated/troublesome than
just creating the desired report yourself.
 
Thank you for the response. Yes, the queries are essentially the same except
for different criteria. They are not counts from the same field; they are
counts from the InmateId field of different but similar queries:

SELECT tblInmates.InmateId, tblInmates.InmateName, tblInmates.Lock
FROM tblInmates
WHERE tblInmates.Lock BETWEEN “1-101B†and “1-130Bâ€

That’s pretty much it. 1 is a housing unit number, 101 is a cell, B is the
bottom bunk. 101B-130B is one floor of one wing of one housing unit. All
told, I have 1800 bunks in this database. The report is just meant to show
how many convicts live on each floor of each wing of each of 9 units. The
above query returns all the inmates who live in bunks 101B thru 130B in Unit
1. The expression =Dcount("InmateId", "qryHU1ALower") counts them. Placing
this expression in the control source of a textbox in a report shows the
total of inmates who live in bunks 101B thru 130B, but this causes the report
to open very slowly.

The report’s record source is blank because I can only set one table/query
to it, but I need data from multiple queries.

I hope I’ve provided all the info you need to assist me. Can’t thank you
enough!

Rip
 
Do you have tables that describe your various wings, floors, and units? You
should have a value stored in a table some place that describes all of this
so that you can create a report with one record source.
 
OK. I obviously have a bad design here. I should have the lock field split
up. Instead of "1-101B" I should have separate fields for unit, cell and
bunk. My first question would be, how do I keep users from entering the same
unit, cell, bunk combination for any one inmate? The inmates ID number is
the primary key.

Thanx for getting me started.

Rip
 
You can create a unique index on a field or combination of fields. This
index does not have to be the primary key.

--
Duane Hookom
MS Access MVP
--

RipperT @comcast.net> said:
OK. I obviously have a bad design here. I should have the lock field split
up. Instead of "1-101B" I should have separate fields for unit, cell and
bunk. My first question would be, how do I keep users from entering the
same unit, cell, bunk combination for any one inmate? The inmates ID
number is the primary key.

Thanx for getting me started.

Rip
Click to expand...
 
Back
Top