SQL Command

darrenl · #1 (**permalink**) 10-29-09, 11:37

Hi There,

I have a command that links 4 tables together that works ok. I really need to group a few fields to limit the records being returned.

I want to make the "Item_Location_File.QtyOnHandPrimaryUn as QTY" field a sum and I need group the Item_Cost_File.LedgType.

HTML Code:

SELECT DISTINCT
Item_Master.IdentifierShortItem,
Item_Master.Identifier2ndItem, 
Item_Master.DescriptionLine1,




Item_Location_File.QtyOnHandPrimaryUn as QTY, 
Item_Location_File.CostCenter,
Item_Location_File.IdentifierShortItem,


Item_Cost_File.IdentifierShortItem,
Item_Cost_File.AmountUnitCost AS COST,
Item_Cost_File.LedgType,
Item_Cost_File.CostCenter,



Sales_Order_Detail_File.IdentifierShortItem,
Sales_Order_Detail_File.PurchasingReportCode1,
Sales_Order_Detail_File.DtForGlAndVouch1 AS InvoiceDate



FROM Item_Master, Item_Cost_File, Item_Location_File, Sales_Order_Detail_File



WHERE 

Item_Master.IdentifierShortItem = 27814
AND

Item_Cost_File.LedgType = '07'
AND

Item_Cost_File.CostCenter in ('         20', '         C20', '         21', '         C21')


AND
Item_Location_File.CostCenter in ('         20', '         C20', '         21', '         C21')

AND

Item_Master.IdentifierShortItem =Item_Location_File.IdentifierShortItem
AND   Item_Master.IdentifierShortItem = 
Item_Cost_File.IdentifierShortItem
AND Item_Master.IdentifierShortItem = 
Sales_Order_Detail_File.IdentifierShortIteM

Thanks in advance

Darren

darylmark · #2 (**permalink**) 11-21-09, 09:54

Darren,

Are you trying to do the grouping on the SQL side or within Crystal Reports. Given the SQL above, you can create a CR Running Total which will sum the value of QTY. You could also create a group for LedgType and then use the Record Selector to limit what records are displayed in the group. I hope this helps.

Internet Services

Web Development

Digital Marketing

Consumer Tech