Stumped: 'Query too complex'

[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?

 

[Bill Crawford]

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]

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

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?

 

[Bill Crawford]

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.

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

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?

 

[Richard Brightman]

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.

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.

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 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?

 

[Bill Crawford]

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,

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.

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.

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

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?