HI @glennyboy ,
Please refer below query:
SELECT
MAX([BBI$Transfer Shipment Line].[Transfer-to Code]) AS [Loc Code]
,MAX([BBI$Transfer Shipment Line].[Item No_]) AS [Item No_]
,MAX([BBI$Transfer Shipment Line].[Description]) AS [Description]
,MAX([BBI$Transfer Shipment Line].[Description 2]) AS [Description2]
,MAX([BBI$Transfer Shipment Line].[Variant Code]) AS [Variant Code]
,SUM([BBI$Transfer Shipment Line].[Quantity]) AS [Transfer(Qty)]
,COALESCE(SUM(coalesce([BBI$Trans_ Sales Entry].[Quantity],0)),0) AS [Sales(Qty)]
,SUM([BBI$Transfer Shipment Line].[Quantity])+COALESCE(SUM(coalesce([BBI$Trans_ Sales Entry].[Quantity],0)),0) AS Total
FROM [BBI$Transfer Shipment Line]
LEFT JOIN (SELECT DISTINCT * FROM [BBI$Trans_ Sales Entry]) [BBI$Trans_ Sales Entry]
ON [BBI$Transfer Shipment Line].[Transfer-to Code]=[BBI$Trans_ Sales Entry].[Store No_] AND
[BBI$Transfer Shipment Line].[Item No_]=[BBI$Trans_ Sales Entry].[Item No_] AND
[BBI$Transfer Shipment Line].[Variant Code]=[BBI$Trans_ Sales Entry].[Variant Code]
WHERE
[BBI$Transfer Shipment Line].[Transfer-to Code]='LOCATION1' AND
[BBI$Transfer Shipment Line].[Item No_]='ITEM01' --AND
-- [BBI$Transfer Shipment Line].[Variant Code]='VARIANT01'
GROUP BY
[BBI$Transfer Shipment Line].[Transfer-to Code]
,[BBI$Transfer Shipment Line].[Item No_]
,[BBI$Transfer Shipment Line].[Variant Code]
Output:
If the response is helpful, please click "Accept Answer" and upvote it.
Best regards
Melissa