How to include Sub totals without grouping 1

[goolawah]

I have a simple ACCESS application that outputs a text file in a particular (odd) format required for importing into another application.

The output file has Header and Detail records. The Header contains information on customer code and order number while the following Detail records have information on products codes, descriptions, and qty shipped.

I need to sub total the qty shipped field on the Detail records for each new order number. i.e. after the last detail record for that Order Number and before the Header record for the next Order Number.

I have managed to have a grand total of Qty Shipped for the whole report, but cannot find a way to include a sub-total for each set of Detail records. Unfortunately I don't have any choice about the format of the file contents.

Is there any way of creating sub-total with more complex criteria?

Any help with this would be greatly appreciated.

 

[dhookom]

Would you mind typing in some records (relevant fields) and then how you would expect these to display in your report?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[THWatson]

goolawah
The best way would be to have a Group Footer. However, from your post, it appears that you can't have that. So, if you can't allow a Group Footer to show, you could try this as a work-around...

1. Group on the Order Number, and make sure there is a Group Footer.
2. In that Group Footer, put a text box control to sum the Quantity Shipped for that Order Number. For sake of example, let's call that text box txtQtyShipped.
3. Put an additional text box control in the Detail section. Set the Control Source for that to =txtQtyShipped. Set its properties to Hide Duplicates.
4. Once you are satisfied you are getting the correct results, make the txtQtyShipped in the Group Footer really small in height (shrink it to nothing if you wish), and make it not visible. Shrink the entire Group Footer section up...you can make that section not visible too if you wish.

Tom

 

[goolawah]

Thanks for helpful responses above. Perhaps a bit more detail would be helpful.

Following is a snapshot from the output table showing relevant columns -

In the header records the relevant fields are CustCode and Order_Disc. In the Detail records the relevant fields are InvDesc_Item Code, InMsg1_Descrip, and Qty (which has falled off the edge, but you get the idea.

Folowing is a snapshot of the output check report

What I am trying to do is create a sub-total of the Qty column for each order (i.e. for all the detail records between header records.

I don't think I can use grouping (by order number) because the detail records don't include the order number. The are only associated with it by virtue of the fact that they follow it.

I am hoping for someting like a running total that accumulates for each detail record, and resets when it comes to a header record.

Any suggestions appreciated.

 

[dhookom]

I don't see anything in your data that relates header records with detail records.

Your query/output/report doesn't have to display all columns.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[goolawah]

Hi Duane,

Yes, you're absolutely right. The only thing that relates the details to the headers is that they follow after them. Unfortunately I don't have a choice as my output table format is dictated by the application into which I have to import the data.

I have a similar report to preview my incoming data and because that is a proper "flat" file all records have all fields, so my report on that table has sub-totals based on groups with no problems.

I am hoping I can find a "novel" way around this problem, possibly by being a bit clever about the expression I use in the "Control Source | Data" property of the Sub-total text box.

Any ideas?

 

[dhookom]

In Access tables, there is no such thing as "follow after them". You need to have some field that links a header to the details.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]