report running sum question

[bobby213]

okay I have a running sum to get a total each month at the end of the report it displays the correct total so thats fine , the rpoblem is I'd like the last running sum total on top of the form in the header

what I've done so far is created another set of text boxes in the header and tried to use teh DMAX or MAX functions to get the last of the running sum totals but its not working right is there another way I can approach this?

thanks for the time,
Bob

 

[Pat Hartman]

You'll need three queries to achieve the results you're looking for.
Query1 - summarize all data (by whatever break fields you need for the report) that has a transaction date less than the start date for the report.
Query2 - this is the current detail query you are now using.
Query3 - a union query to combine the recordsets from Query1 and Query2. Use this query as the recordsource for the report.

To get the row from query1 to sort before all the related rows in query2, you may need to add a literal field to query1 and query2 and include it in the sort sequence. You can do this in the Union query.

Select *, "1" as ExtraSortField from Query1
Union Select *, "2" as ExtraSortField from Query2;

Union queries require that the fields in all subordinate queries appear in the same order and have the same datatype although they don't necessarily need to have the same names. The column names will be taken from the first select statement.