query-refering to field in main report

[Sherry]

I have a main report that has a query that links to the
customer id. I have several subreports (linked via
customer id) that have queries that have the calculated
fields. Within the subreports I have a sum field in the
report footer. These sum fields are then copied into
fields on the main report where a remaining balance is
calculated. So I can tell if any one customer has a
remaining balance. The problem I only want to print the
customers with a balance not equal to zero. How can I
refer to this remaining balance field in the underlying
query of the main report?? I would think I could just
create a field in the query that assigns the value from
the field in the main report, but I have not been
sucessful.

Any suggestions will be appreciated.
Thanks so much
Sherry

 

[Jeff Boyce]

Sherry

The problem I can see with what you described is that you want a query to
run and provide the base data to a report, which then formats the details,
in the process calculating a balance, which you then want to have the
initial query look at and decide not to provide a row to the report (which
it already HAS!).

Wouldn't it be easier to create the data you need to check (totals and
balances) in your queries, and use a criterion in the query to limit the
rows before they're ever sent to the report?

Good luck

Jeff Boyce
<Access MVP>

 

[Sherry]

Jeff
I have thought and thought on this. There are five
subreports that contain sum fields in the report footer
and all these sum fields have to be added then actually
one sum field (an accumulation of payments) subtracted.
I want to look at the remaining balance (a calculated
field on the main report)to see if the customer's balance
is not equal to zero.
I don't know if I'm just overlooking something (which is
not new to me) I just can't figure how to create a query
that will provide me the remaining balance. Each
customer can have multiple details for each
query/subreport. I quess I don't understand how to do a
query that will list each customer then list the multiple
records that match that customer then sum a numeric
field. THis would probably be where subsheets come in.
Would it be simpler to create queries with subsheets or
just somehow be able to refer to the remaining balance in
the main report.
I appreciate your attention, this discussion group has
really helped me with issues that become all so time
consuming. Thanks so much
Sherry Happy Holidays

 

[Jeff Boyce]

Sherry

I am still puzzled and somewhat confused, perhaps because I don't understand
the underlying data and how you've organized it. To me, the starting point
for any report is what data is needed. The next issue is what data I have.
Finally, I need to figure if I can "get there from here". If so, then I
usually start building a query (or multiple queries).

Again, since I don't know your data/structure, the following is only a
guess:

I have a "parent" table, related one-to-many to a "child" table. An

example of this might be an Invoice (parent) with multiple InvoiceDetail
rows (child).

I want to know the sum of the InvoiceDetail rows for a given Invoice --

I create a totals query on the tblInvoiceDetail, grouped by the foreign key
that points back to the tblInvoice's primary key. Now I have, for each
Invoice, the total of the InvoiceDetail rows.

I want to create an Invoice (report). I can use the Invoice table to

fill in the Invoice-header level, and the InvoiceDetail table to fill in the
(related) Invoice Detail rows as a sub-report. If I need to total the
invoice, I could have the main report look to a total contained in the
subreport, I could use a DSum() function, or I could use the query I create
above.

I hope something in that offers an idea.

Good luck

Jeff Boyce
<Access MVP>

 

[Sherry]

Jeff
I wanted to say thank you for your help.
Thanks
Sherry