Summary report question

[Megan F via AccessMonster.com]

I have a complicated invoicing system.
The design wasn't that great to begin with and when I took the project over I
wasn't allowed to modify tables.
Right now there is a customer table, a non-customer table, an invoice table,
an invoice details table, special details table.

I'm trying to create a summary report with information that lists the
driver, date, invoice number, name on the invoice, product detail, special
details, shipping cost and grand total,

this should be sorted by driver and date.

the trouble is, the only way i can get the product and special product totals
is to create sub reports.
i look up the invoice and determine the customer name by check the customer
id number on the invoice against the customer tabel (custID is the key). I
display the name from the correct table.
Then I was originally able to display the totals that go with the products
and special details and the totals.

I was trying to add subtotals after grouping by driver.

Now I'm getting a "too complex" error. I'm not even clear on the error. It
says it's too complex and I may need to use variables.

Is there a reference I can look at for this kind of stuff. I guess it's just
too many checks on too much data.
Only about 1500 rows but I guess it can't handle it.

Thanks for the help.

 

[David Lloyd]

Megan:

I don't know all the details of how you have set up your report, however, I
can make some general comments. The "too complex" error message can result
from a number of sources. Sometimes it is related to having Null values in
the field you evaluating. Another source can be using an incorrect datatype
in a calculation. It does not necessarily mean that your expression is too
complex to be evaluated.

Related to the subtotals and totals you are calculating, if you are only
using subreports to calculate totals, another option would be to use the
DSum function. It allows you to calculate totals separate from the report
record source, and can be used with a query or table. See Access Help for
more information on the DSum function.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I have a complicated invoicing system.
The design wasn't that great to begin with and when I took the project over
I
wasn't allowed to modify tables.
Right now there is a customer table, a non-customer table, an invoice table,
an invoice details table, special details table.

I'm trying to create a summary report with information that lists the
driver, date, invoice number, name on the invoice, product detail, special
details, shipping cost and grand total,

this should be sorted by driver and date.

the trouble is, the only way i can get the product and special product
totals
is to create sub reports.
i look up the invoice and determine the customer name by check the customer
id number on the invoice against the customer tabel (custID is the key). I
display the name from the correct table.
Then I was originally able to display the totals that go with the products
and special details and the totals.

I was trying to add subtotals after grouping by driver.

Now I'm getting a "too complex" error. I'm not even clear on the error. It
says it's too complex and I may need to use variables.

Is there a reference I can look at for this kind of stuff. I guess it's just
too many checks on too much data.
Only about 1500 rows but I guess it can't handle it.

Thanks for the help.