Complex Query

[Guest]

I'm building a Fiscal Report to sum data at various levels. There are 12 different kinds of data. Each kind of data is collected on an individual basis. Each individual collects the same kind of data every month of the Fiscal year. Each individual is a member of a group. There may be several groups. Groups are assigned to an office. Office generate reports that contain group data. There may be more than one office in a country. Countries generate reports that contain Office(s) data. Each country is assigned to a region. There may be several countries assigned to a Region. Regions generate reports that contain Country data. There are several regions. The World is the sum of all regions. A world report contains Region data.

At the Home office, I build a SQL 2000 query via a VB application that collects the individual data from each office, sums the individual reported data to a group level, passes those group totals to a Crystal Report (which is imbedded within the VB application) and the use may select Crystal Reports at Office, Country, Region and of course, World level. This query is a Union Query

Now, the offices throughout the world need to perform a similar operation. Although each office uses a similar VB application, away from the home office it is linked to an Access 2000 database. I have converted the SQL2000 query to an Access 2000 SQL Query, but when I run it, Access reports "Query is too complex". I have broken the Union into Parts and some parts run successfully.

I still have one Part that when run Access reports "Query is too complex". When I remove some parts of this query (from 12 kinds of data to 9 kinds of data), the Access 2000 SQL Query runs and correctly reports that data

Does any one know what are the Access 2000 query complexity limits

Thank yo

 

[Tom Ellison]

Dear Joe:

I strongly recommend you simply install MSDE to serve this and simply
use your existing VB/SQL Server application. This assumes the
application wil scale to MSDE, but the 2 GB limit for MSDE is the same
as for Access Jet. You can also run an Access application from tables
in MSDE. Your existing MSDE queries will run without modification,
except that the queries must be specified as Pass Through.

There are a few details of this, but let's take care of convincing you
of the ease and feasibility of making this work first.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Gary Walter]

I'm building a Fiscal Report to sum data at various levels. There are 12

different kinds of data. Each kind of data is collected on an individual
basis. Each individual collects the same kind of data every month of the
Fiscal year. Each individual is a member of a group. There may be several
groups. Groups are assigned to an office. Office generate reports that
contain group data. There may be more than one office in a country.
Countries generate reports that contain Office(s) data. Each country is
assigned to a region. There may be several countries assigned to a Region.
Regions generate reports that contain Country data. There are several
regions. The World is the sum of all regions. A world report contains
Region data.

At the Home office, I build a SQL 2000 query via a VB application that

collects the individual data from each office, sums the individual reported
data to a group level, passes those group totals to a Crystal Report (which
is imbedded within the VB application) and the use may select Crystal
Reports at Office, Country, Region and of course, World level. This query
is a Union Query

Now, the offices throughout the world need to perform a similar operation.

Although each office uses a similar VB application, away from the home
office it is linked to an Access 2000 database. I have converted the
SQL2000 query to an Access 2000 SQL Query, but when I run it, Access reports
"Query is too complex". I have broken the Union into Parts and some parts
run successfully.

I still have one Part that when run Access reports "Query is too complex".

When I remove some parts of this query (from 12 kinds of data to 9 kinds of
data), the Access 2000 SQL Query runs and correctly reports that data.

Does any one know what are the Access 2000 query complexity limits?

Hi Joe,

In addition to Tom's sage advice...

Your Union query is exceeding the 64KB limit
for a compiled query in Access.

Another workaround would be to set up
a report table, empty it when you start to
run report, then break up your Union
query into manageable append queries
going into your report table.

Of course, as Tom said, MSDE may be the best option.
(I don't know if Access PassThrough queries are limited
by the 64KB ceiling or not.)

Good luck,

Gary Walter