BAQ with summary fields from 2 different tables

Yahoo Archive
1
Good morning.

If you do a BAQReport, it might be easier to handle these calculations and format in Crystal, with the same BAQ you created.

One thing you could do at the BAQ level -if you decide not to go to BAQReport-, is to get your comparison between Std and Invoiced at the Release level; I would base my query on OrderRel table, linked to InvcDtl and OrderHed independently (InvcDtl table "naturally" links to OrderRel) and this way you wouldn't get duplicates, unless you Invoice single Order Releases more than once that is........ Just an idea....

Regards.

Roel Martinez
ERP Analyst

SABRE TUBULAR STRUCTURES (tm)
www.sabretubularstructures.com<http://www.sabretubularstructures.com/>
8653 E. Hwy 67 | Alvarado, TX 76009
Phone: 817-852-1700 ext. 11858 | Direct Line: 817-852-1858 |Fax: 817-852-1705



[Non-text portions of this message have been removed]
2
I am trying to setup a BAQ which will calculate the total costs for the parts sold and total cost for invoiced items for each sales order.

In the BAQ, I have table OrderHed (main), PartTran (summary) & InvcDtl (summary). Also has a criteria setup for PartTran table to filter only Trans Type STK-CUS only.

"for each OrderHed where ( OrderHed.OrderNum >= 10 AND OrderHed.OrderNum <= 30) no-lock , each PartTran where ( PartTran.TranType = 'STK-CUS') and (OrderHed.Company = PartTran.Company and OrderHed.OrderNum = PartTran.OrderNum) no-lock , each InvcDtl where (OrderHed.Company = InvcDtl.Company and OrderHed.OrderNum = InvcDtl.OrderNum) no-lock ."


In the Display sheet, I create only 3 fields, OrderHed.OrderNum & 2 calculate fields as follow:

PartCost:
TOTAL(PartTran.ExtCost)

InvCost:
TOTAL(InvcDtl.SellingShipQty * (InvcDtl.MtlUnitCost + InvcDtl.MtlBurUnitCost + InvcDtl.LbrUnitCost + InvcDtl.SubUnitCost + InvcDtl.BurUnitCost))

The result turn out for both calculate fields is equal to multiple times (depends on the number of line in the table) of the total cost of 1 order.

Example of the result: order #1 has total 4 lines, the total cost for order #1 is actually $10, the BAQ above will turn out with $40.

Any idea ?