"Running Sum" Comparisons by Date

[Haccess]

Hi. I need to add some functionality to an existing report that compares actual deliveries to required deliveries. The parent report is grouped by contract line item number ("CLIN"), with two subreports linked to the parent CLIN by CLIN_ID. To keep things simple, we'll call the parent report "DELIVERIES TO CONTRACT" and the two subreports "REQUIRED" and "ACTUALS."

Each subreport has a quantity field representing, respectively, the quantity required BY a given date and the actual quantity shipped ON a given date. Note that individual shipments are partial fulfillments toward the total CLIN requirement, and may be shipped in advance of contract need. Of course, we may also ship late and be penalized accordingly. Each subreport also includes a running sum on the same quantity field to show cumulative requirements and cumulative shipments with each subreport record.

The output looks something like this (forgive the font spacing):

CLIN 123, CLIN DESCRIPTION, TOTAL QTY
REQUIRED ACTUALS
DATE QTY CUM QTY DATE P/N QTY CUM QTY
Required shipment A Actual delivery A
Required shipment B Actual delivery B
Actual delivery C
Actual delivery D
Actual delivery E

CLIN 456, CLIN DESCRIPTION, TOTAL QTY
REQUIRED ACTUALS
DATE QTY CUM QTY DATE P/N QTY CUM QTY
Required shipment A Actual delivery A
Required shipment B Actual delivery B
Actual delivery C
Actual delivery D
Actual delivery E
[/color red]

What I need to do is to perform some sort of attention-getting format on each entry in the REQUIRED subreport where the running sum for quantity delivered BY THE DATE OF REQUIRED SHIPMENT is not >= the running sum of required quantity.

Any suggestions would be greatly appreciated.

Thanks,

Tom

 

[dhookom]

I would create a QtyDeliveredBefore column in the record source of the Required subreport. This would basically be a subquery that totals the Delivery Quantities for the CLIN where the Actual Delivery Date is less than or equal to the Required Delivery Date.

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]

 

[Haccess]

Sounds reasonable, but I'm afraid I'm over my head writing this query expression.

Here's what I tried (and failed miserably): To the underlying query for the required deliveries subreport, I added fields from the tables that supply the actual delivery date and quantity. Note that, because of the way our government customer makes us invoice on a DOD form document, the document date is the delivery date. However, a given document/invoice often includes more than one parent CLIN being shipped toward, called a "line item no." on the form. Therefore, I have a DELIVERIES table to capture the invoice data including the date, and a LINE_ITEMS table capturing the quantity delivered toward the parent CLIN. The table relational structure is CLINS > LINE_ITEMS (via CLINS_ID) and LINE_ITEMS > DELIVERIES (via DELIVERIES_ID).

I then set the actual delivery date criteria to "<= REQUIRED.DATE_DUE" and set the QTY_DELIVERED to sum. What I ended up with is a query dynaset record for every single delivery, regardless of how many required delivery records there are for any given CLIN. The subreport will run with all the duplicates showing. The parent report won't run at all, it simply freezes up.

How would you write an expression to sum actual deliveries for the parent CLIN up to and including the required delivery date of that particular required delivery record?

 

[dhookom]

I would not do anything with the delivery subreport. I would do something with the required report's record source. I really don't know the field names from the tables so I can't create the query for you.

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]