Stumped: 'Query too complex'

  • Thread starter Thread starter Richard Brightman
  • Start date Start date
R

Richard Brightman

I guess I'm overlooking an obvious solution to this one, but I'm stumped!

Our association put out an assessment to our members which would be paid in
eight quarterly installments. I build an assessment table with the standard
name/address stuff plus the monetary fields such as beginning balance,
payment, fees, etc. The monetary fields are repeated for each of the eight
quarters (Q1Bal, Q2Bal, etc.)

I want a form to show all of these fields (for all quarters), plus other
computed fields, such as Q1Interest and Q1EndBal. It would allow the
manager to see a member's payment history on a single screen and enter new
payments.

The query I built ends up with seven fields per quarter plus the
name/address stuff for a grand total of 68 fields. When I run the query I
get a "Query too complex" message. Since each quarter needs data from the
previous quarter to do the computations, I'm stumped how to do the query.
If this were a different life I'd create a matrix with one dimension being
the seven fields and the other dimension the eight quarters.

What limit am I hitting and is there some other way to structure the data to
get around the problem?
 
HI:

In your query, did you setup the calculated [fields] for each quarter
consecutively; in the order the computations are to occur? And what about
your handling of 'null' values in the query?.

food for thought
 
They are in order and all of the computations are Nz'd where they need to
be.

Bill Crawford said:
HI:

In your query, did you setup the calculated [fields] for each quarter
consecutively; in the order the computations are to occur? And what about
your handling of 'null' values in the query?.

food for thought


Richard Brightman said:
I guess I'm overlooking an obvious solution to this one, but I'm stumped!

Our association put out an assessment to our members which would be paid in
eight quarterly installments. I build an assessment table with the standard
name/address stuff plus the monetary fields such as beginning balance,
payment, fees, etc. The monetary fields are repeated for each of the eight
quarters (Q1Bal, Q2Bal, etc.)

I want a form to show all of these fields (for all quarters), plus other
computed fields, such as Q1Interest and Q1EndBal. It would allow the
manager to see a member's payment history on a single screen and enter new
payments.

The query I built ends up with seven fields per quarter plus the
name/address stuff for a grand total of 68 fields. When I run the
Click to expand...
query
I
get a "Query too complex" message. Since each quarter needs data from the
previous quarter to do the computations, I'm stumped how to do the query.
If this were a different life I'd create a matrix with one dimension being
the seven fields and the other dimension the eight quarters.

What limit am I hitting and is there some other way to structure the
Click to expand...
data
to
get around the problem?
Click to expand...
Click to expand...
 
OK.

Make a query for each quarter and add calculated fields for calculations in
that quarter, then a final query to bring them together for the final
calculations by adding calculated fields.


Richard Brightman said:
They are in order and all of the computations are Nz'd where they need to
be.

Bill Crawford said:
HI:

In your query, did you setup the calculated [fields] for each quarter
consecutively; in the order the computations are to occur? And what about
your handling of 'null' values in the query?.

food for thought


Richard Brightman said:
I guess I'm overlooking an obvious solution to this one, but I'm stumped!

Our association put out an assessment to our members which would be
Click to expand...
paid
in
eight quarterly installments. I build an assessment table with the standard
name/address stuff plus the monetary fields such as beginning balance,
payment, fees, etc. The monetary fields are repeated for each of the eight
quarters (Q1Bal, Q2Bal, etc.)

I want a form to show all of these fields (for all quarters), plus other
computed fields, such as Q1Interest and Q1EndBal. It would allow the
manager to see a member's payment history on a single screen and enter new
payments.

The query I built ends up with seven fields per quarter plus the
name/address stuff for a grand total of 68 fields. When I run the
Click to expand...
query
I
get a "Query too complex" message. Since each quarter needs data from the
previous quarter to do the computations, I'm stumped how to do the query.
If this were a different life I'd create a matrix with one dimension being
the seven fields and the other dimension the eight quarters.

What limit am I hitting and is there some other way to structure the
Click to expand...
data
to
get around the problem?
Click to expand...
Click to expand...
Click to expand...
 
Sorry for the delay in responding, was in Philly for a few days.

The problem is that any calculated field in subsequent quarters (queries)
requires the calculated Ending Balance field from the previous quarter
(query). This ends up nesting the queries and by the sixth quarter (five
nested queries) I still get the "Too Complex" message.



Bill Crawford said:
OK.

Make a query for each quarter and add calculated fields for calculations in
that quarter, then a final query to bring them together for the final
calculations by adding calculated fields.


Richard Brightman said:
They are in order and all of the computations are Nz'd where they need to
be.

Bill Crawford said:
HI:

In your query, did you setup the calculated [fields] for each quarter
consecutively; in the order the computations are to occur? And what about
your handling of 'null' values in the query?.

food for thought


"Richard Brightman" <brightr AinaCircle hautoweb period com> wrote in
message I guess I'm overlooking an obvious solution to this one, but I'm stumped!

Our association put out an assessment to our members which would be paid
in
eight quarterly installments. I build an assessment table with the
standard
name/address stuff plus the monetary fields such as beginning balance,
payment, fees, etc. The monetary fields are repeated for each of the
eight
quarters (Q1Bal, Q2Bal, etc.)

I want a form to show all of these fields (for all quarters), plus other
computed fields, such as Q1Interest and Q1EndBal. It would allow the
manager to see a member's payment history on a single screen and
Click to expand...
enter
new
payments.

The query I built ends up with seven fields per quarter plus the
name/address stuff for a grand total of 68 fields. When I run the query
I
get a "Query too complex" message. Since each quarter needs data
Click to expand...
from
the
previous quarter to do the computations, I'm stumped how to do the query.
If this were a different life I'd create a matrix with one dimension being
the seven fields and the other dimension the eight quarters.

What limit am I hitting and is there some other way to structure the data
to
get around the problem?
Click to expand...
Click to expand...
Click to expand...
 
HI:

Did you try to make a report based on the initial query and make groupings
for each quarter with the calculated fields. You might set it up like this.
In the Grouping panel just enter the names of the
header/footer

First Quarter Header***************************************
--------------------------------------------------------------------------
'Name' 'Address' 'Account ID' 'Beginning Balance' 'Payments' 'Fees'
--------------------------------------------------------------------------
________________________________________________________
detail section**********************************************
---------------------------------------------------------------------------
[Name] [Address] [AccountID] [BeginingBalance] [Payments] [Fees]
________________________________________________________
Second Quarter Footer**************************************
---------------------------------------------------------------------------
'Second Quarter' [Q2Balance]
etc.............
________________________________________________________
Third Quarter Footer****************************************
---------------------------------------------------------------------------
'Third Quarter' [Q3Balance] etc........
________________________________________________________
..
..
..
..
________________________________________________________
First Quarter Footer*****************************************
---------------------------------------------------------------------------
'Closing Balance ' [Closing Balance] etc.....
_________________________________________________________

regards,

Richard Brightman said:
Sorry for the delay in responding, was in Philly for a few days.

The problem is that any calculated field in subsequent quarters (queries)
requires the calculated Ending Balance field from the previous quarter
(query). This ends up nesting the queries and by the sixth quarter (five
nested queries) I still get the "Too Complex" message.



Bill Crawford said:
OK.

Make a query for each quarter and add calculated fields for calculations in
that quarter, then a final query to bring them together for the final
calculations by adding calculated fields.


Richard Brightman said:
They are in order and all of the computations are Nz'd where they need to
be.

HI:

In your query, did you setup the calculated [fields] for each quarter
consecutively; in the order the computations are to occur? And what about
your handling of 'null' values in the query?.

food for thought


"Richard Brightman" <brightr AinaCircle hautoweb period com> wrote in
message I guess I'm overlooking an obvious solution to this one, but I'm
stumped!

Our association put out an assessment to our members which would
Click to expand...
be
paid
in
eight quarterly installments. I build an assessment table with the
standard
name/address stuff plus the monetary fields such as beginning balance,
payment, fees, etc. The monetary fields are repeated for each of the
eight
quarters (Q1Bal, Q2Bal, etc.)

I want a form to show all of these fields (for all quarters), plus other
computed fields, such as Q1Interest and Q1EndBal. It would allow the
manager to see a member's payment history on a single screen and enter
new
payments.

The query I built ends up with seven fields per quarter plus the
name/address stuff for a grand total of 68 fields. When I run the
query
I
get a "Query too complex" message. Since each quarter needs data from
the
previous quarter to do the computations, I'm stumped how to do the
query.
If this were a different life I'd create a matrix with one dimension
being
the seven fields and the other dimension the eight quarters.

What limit am I hitting and is there some other way to structure the
data
to
get around the problem?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top