Query too large

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

Guest

I need to build a query from 8 or 10 other queries so that I can build a
form from all that information. The 8 or 10 queries are based on (total)
probably 15 different tables. I can build the query ok but it has 800,000
items in it and runs slowly. I don't want my form based on this query to
drag. I build a form from the 8 or 10 queries with a mainform and then a
bunch of subforms and it got cumbersome. Any suggestions? Thanks!!!
 
Your description is just too fuzzy. I suspect you have a problem of table
design: do you UNION ALL the main tables? if so, you destroy the
possibility to use indexes and the solution is to make one huge table,
rather than union-ing them. Do you use cross join between all these tables,
then use a WHERE clause implying different tables? if so, use INNER JOINs
rather that the cross joins.



Vanderghast, Access MVP
 
I'll build on Michel's response...

It all starts with the data...

Unless you give us some idea of what data and how it is organized, we'll be
hard-pressed to offer specific suggestions...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
All of them use inner joins. Thanks!

Michel Walsh said:
Your description is just too fuzzy. I suspect you have a problem of table
design: do you UNION ALL the main tables? if so, you destroy the
possibility to use indexes and the solution is to make one huge table,
rather than union-ing them. Do you use cross join between all these tables,
then use a WHERE clause implying different tables? if so, use INNER JOINs
rather that the cross joins.



Vanderghast, Access MVP
Click to expand...
 
Even if you get this Kludge to work, you will most likely not be able to edit
or add data.

Give your description, I really believe you need to step back and rethink
your design.
 
I have over 1 million pieces of data for all the K - 12 facilities in my
state. That data is in roughly 250 tables. The data is laid out like
campuses, school districts, buildings, systems (HVAC, Plumbing, Structural,
Site, Interior, Exterior, etc) that are used at that site. We also have a
record of deficiencies (systems that need to be replaced.) I need campus,
district, square footage, growth (growth means they need more facility,) and
whether each system (there are 12 total) needs to be replaced all on one
form. I wrote 8 or 10 queries to gather the data in that I need to include on
the form. I wrote one query from the 8 or 10 but it contains 800,000 peices
of data. It runs slow and sucks up a lot of space. Any help would be greatly
appreciated. Thanks!
 
The data is organized into tables like this: One table for campus, one table
for district, one table for building, one table for each floor of the
buildings, one table for HVAC (types and sizes) one table for site (and all
site items like parking lots, fences, outdoor lighting), one table for
plumbing (faucets, urinals, toilets, water closets, piping), one table for
interior (type of paint, ceilings, flooring, etc), one table for
exterior(paint, siding, eaves, etc), one table for each thing. Then there are
tables for each deficiency, One for site deficiencies, one for HVAC
deficiencies, on table for interior deficiencies, etc.
 
I doubt you need to display all that at once!

Have you tried to use a form with a tabs-control, one sub-from per tab-page?
If so, DO NOT FILL the source-object of the sub-forms you DON'T SEE (on an
invisible tab-page). Once a tab-page become active, fill the sub-form source
object, and LIMIT the number of records to be displayed. May be a simpler
alternative is to open a new form (instead of having the user clicking on a
tab of the tabs-control) given what DETAILS the user want to see, and so on,
and so on. So, the 'main' form would be just about ONE of the table, and you
won't use explicitly a huge query, but small ones, implying (portion of) one
new table at a time.


Vanderghast, Access MVP
 
I am pretty sure I can do what you are talking about but give me a hint to
get me started on tab controls and tab-page and source-object. Haven't used
those lately. I'm a little blurry. Thanks so much!!!!
 
That solution is harder than having multiple independent forms, but the idea
is the same, with the exception that you can have many forms open, but just
one page of a tabs-control 'open'.

A tabs-control is not really a container, but a selector. Each "page" is a
kind of group of controls to be displayed (or make invisible) accordingly to
the page that is active. The design would be to drag a sub-form on each
page, but to NOT fill the source object of the sub-form control. The Source
Object is the first property on the data tab in the property sheet of the
sub-form. When the page owning subforms become active ( you know that
through the onChange event of the tab control, and the actual active page is
the tab control value property ), you then assign the source object of the
sub-form control.



Private Sub TabCtl0_Change()
Select Case TabCtl0.Value
Case 0:
...

Case 1:
' by default, Access call the subform control Child###
Me.Child5.SourceObject = "FormNameToBeUsedForThatSubForm"
...

Case ...
...

End Select

End Sub



Note that you have to supply the right RecordSource to the form use as
subform (see the Form property RecordSource, in design mode of the said
form).





After all that work, ONLY the relevant sub-forms would be filled, with
relevant restricted data, rather than having ALL the subform filled with ALL
the data and filter after every thing is loaded to display just part of that
huge monster.


There is a lot of details to add, but that is the main idea.



Hoping it may help,
Vanderghast, Access MVP
 
As Jeff indicated, it all begins with the data, but almost as important is
what you want to do with the data. Do you really need all of this
information at once, for all the records? I doubt it.

If you want to use this form to view information on a particular school,
then you should probably start out with a couple of combo boxes that allow
you to select District and from there filter the Campus combo box based on
the District selection.

Once you have selected a Campus, you might want to give your user the option
of viewing building, Site, Utilities, or Deficiencies data (these could be on
separate tabs as indicated by Michel Walsh).

Using this technique, you only retrieve the information you need, when you
need it.

HTH
Dale
 
Back
Top