Help with Design to Calculate Correctly

[ramnewbietoaccess]

I have a table which stores products, a table which stores the number of each product a territory office has projected it will need, and a table which stores actual orders. On a report I am trying to show the number projects by month, the actual number ordered by month, and then the unused amount remaining by month. My problem is this....each time a new order number is entered it creates a new line so that the unused is calculated per line instead of as some sort of running sum as this:
(Report is grouped by store number and then by product)
This is an example of orders for one store

Projected Ordered Unused
Ord 1 Staplers 4 2 2
Ord 2 Staplers 4 2 2 (instead of zero)

I have played around with running sums but I can't seem to stumble on the correct way to do it.

Any suggestions? I thank you in advance. Sorry if this is confusing.

 

[dhookom]

Could you show the actual data that results in the display above? How does month/date enter into this?

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]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[ramnewbietoaccess]

Im sorry I dont know what you mean could i show the actual data? the month enters into it only in that it shows the number projected for the month and it shows the numbered ordered in that month.

 

[dhookom]

The actual data would be similar to a datasheet view of the records from the tables that result in your sample from the first posting.

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]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[ramnewbietoaccess]

Thank you so much for your help. i was finally able to come up with the correct formula in an unbound text box to do the appropriate calculation based on a running sum for the field. Thanks again