If you have not created Summary Layout Parts, I suggest starting there.Summary layout partsTo include summary data on a layout (for example, subtotals, grand totals, averages, counts, and so on), you place summary fields in summary layout parts. Summary parts include grand summary and subsummary parts.Defining summary fieldsUse summary fields to calculate values such as subtotals, averages, and grand totals across multiple records. For example, a summary field can display the grand total of all sales in the month of May in a report.
Thank you for your comments. I have already setup Summary Layout Parts to summarize data for the individual reports that I am running for the "Molding Entry" and "Stamping Entry" tables. From what little experience I have with these reports, I'm not sure they'd be useful for the function I'm attempting. Allow me to present a more detailed scenario.
Here is an example of how my data is set up in the tables.
Date Order Number Qty M Qty Left S Qty Left 08/20/2012 100050001 20,000 08/25/2012 100050002 100,000
Date Order Number Qty Comp 08/21/2012 100050001 5,000 08/21/2012 100050001 5,000 08/21/2012 100050002 5,000
As you can see in Molding Data, there are two entries for order number 100050001. What I would like to find a way to do is to have the two numbers in "Qty Comp" for order number 100050001 added together, then subtracted from the "Qty" field in "Table", and then the resulting calculation inserted into the "M Qty Left" field next to the appropriate "Order Number." I would be adding data for each order into "Molding Data" until the resulting calculation equals "0".
I'm sure that the reporting functions including subtotals and grand totals would be effective when running a report, however, I will be doing a large amount of lookups in "Table" to check on the status of individual orders, so it would be beneficial to have this calculation done directly in the "Molding Data" table.
Can the function I am searching for be done? Or should I simply set up a new layout that will search for an order number and then set up a script to perform the calculation? I assume that's a possibility?? If so, I may need some assistance with that. I've set up a few scripts, but i
Thank you again for all of your help. Once I'm done with this urgent project, I'll be sure to take more time to review all of the appropriate training materials so that I don't have to ask what are probably simple, mindless questions like this. :)
Assuming that you have this relationship:
Table::Order Number = Molding Data::OrderNumber
Quantity left can be defined as:
Qty - Sum ( Molding Data::Qty )
or you can use
Qty - Molding Data::sTotalQty
where sTotalQty is a summary field defined in molding data.
Note, it looks from here like you have one table for Molding Data ordered items and one for Stamping Entry ordered items. Reporting would be easier if you had a combined table for all order line items where each record has a fields for Order Number, Qty, ItemID,Description, UnitPrice and extended cost. (Imagine a report listing all ordered items for a specified dat range with subtotals for each item and type of item.)
Thank you for your help! It turns out, for THIS particular issue I'm having, setting up the proper relationship and using the "Qty - Sum ( Molding Data::Qty )" function works perfectly.
As I do a little more programming, I'm sure I'll come up with something new that I need to work on.
But, again, thank you both for your help! It is truly appreciated!!!!