Summary of two tables

[Krusher]

I would basically like to summarize two summaries between two tables. I have a parent-child work order hierarchy report that is grouped by parent. Within each group / hierarchy, I would like to provide a summary total of materials. Meaning, I need to summarize all the CHILD work orders and the PARENT. The issue is that the materials from the PARENT work order are from an ALIAS table. So, how can I create a summary for each group, and then a grand total summary at the end of the report that encompaces all the groups and those work orders that are oustide of those groups. The work orders outside of the groups are those that do not have a PARENT work order. Any help would be greatly appreciated.

 

[IdoMillet]

One option is to point each Parent WO record to itself (so it becomes its own parent). This requires a simple UPDATE query that replaces Null parent fields with the WorkOrder_N
in that record.

Another option is to use a UNION query.

Another option is to create a formula that adds the group total (from the Child records) to the info in the parent WO.
The grand total will be computed as the Grand total from the Child records plus the Grand total from the Parent records.

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[Krusher]

Ido,
I think I am going to go with creating the formula that adds the group total from the child records to the parent. Could you give me an idea of what this forumula would look like. I appreciate your help.

 

[IdoMillet]

Krusher,

Assuming you are grouping on the Parent_WO_Number,
It should looks Like this:
-----------------------------------------------------------
{Parent_WO.Materials} + SUM({WO.Materials}, {WO.Parent_WO_Number})
-----------------------------------------------------------

Cheers,
- Ido


CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com