Exhausted My Resources

[realnine]

I have need help on something I can't solve.
I have a main table Dispatch (I have sence learned that I should have named it tblDispatch plus other nomenclature no-no's) which has pertinent fields of source, date , AmtBilled, Amt Credit and AmtPaid. The other table is Source which has all the mailing info for the source. I use a querry Invoice to select records for which the AmtBilled - AmtCredit-AmtPaid > 0. SQL follows:

SELECT Source.Source, Dispatch.Date, Source.[Vendor #], Source.Street, Source.City, Source.St, Source.Zip, Source.BillingInfo, Dispatch.AmtBilled, Dispatch.AmtCredit, Dispatch.AmtPaid, Dispatch.Invoice, Dispatch.[PO Number], Dispatch.Description, Dispatch!AmtBilled-Dispatch!AmtCredit-Dispatch!AmtPaid AS Expr1

FROM Dispatch RIGHT JOIN Source ON Dispatch.Source = Source.Source

WHERE ((([Dispatch]![AmtBilled]-[Dispatch]![AmtCredit]-[Dispatch]![AmtPaid])>0))
ORDER BY Source.Zip;

This querry feeds a Report writer which uses the source as a group. The group footer has the sum of the billed that have not been paid. I need to include in the Report group footer the sum of (AmtBilled-AmtCredit-AmtPaid) which are over one month (or 30 days) old. I can write a querry on Invoice Querry to lists these over one month sources but I don't know how to put it is in the group footer of the source group.

So the desired group footer would read something like this:
Amount Overdue:
TotalAmount Due:

Thanks

Just leraning,
RealNine

 

[Calvin]

Hmmm.... if I understand you right, and read your query correctlly your already calculating the records (three fields) value into a new field called "Expr1"... If this is correct you already have the Sum of the three fields for the individual record...

So add a textbox to the report in the details section and bind the text box to the Expr1 field from the query (set the Control Source = [Expr1]) then copy the text box into the group or form footer and change the Control Source to be "=Sum([Expr1])" and that should give you a running sum over the group or whole report...

And then I reread your problem and note:

I need to include in the Report group footer the sum of (AmtBilled-AmtCredit-AmtPaid) which are over one month (or 30 days) old. I can write a querry on Invoice Querry to lists these over one month sources

...and I have to wonder is this a second seperate query that does the math already and all you need is to insert the end sum of this query into an independant field that makes no calculations or ties to the report it is to be displayed on.... then if this is the case then I suggest inserting a list box into the group or report footer but only the height of one record and set the ROW Source = the name of the second query. The reason you use a list box is because a text box does not have the property or ability to be set equal to a query or table.

If I'm way off the mark, please reach through the screen an smack me once and I will shut up.

 

[realnine]

Thanks Calvin,

You gave me the road map to do what I wanted to do
I made one querry which selects the records which are overdue. Then using that querry as the source for a second querry, I grouped by the source and summed the overdue field Then I included and linked this second querry to the invoice querry which is the source for the report writer. Then, as you suggested, I put I text box in the group footer and bound it to the summed overdue field. This may not make a lot of sense without seeig the entire picture but the bottom line it works.

Thanks again,
RealNine

 

[Calvin]

I think I can visualize it, I've done some pretty funky reports myself.
Glad it worked out and I could be of help.