I have to build a query that interrogates two tables. THe first table, ArCustStkXref, has columns CustomerCode, StockCode, PartNumber and the second table, invWarehouse, has columns StockCode and Warehouse.
In Table 1 a stock code will occur more than once as part numbers change over time. In Table 2 each StockCode only appears once.
The query I have written is:
SELECT TOP 100 PERCENT ArCustStkXref.Customer, InvWarehouse.Warehouse, InvWarehouse.StockCode
FROM ArCustStkXref FULL OUTER JOIN InvWarehouse ON
ArCustStkXref.StockCode = InvWarehouse.StockCode
WHERE (ArCustStkXref.Customer LIKE 'AS001') AND
(InvWarehouse.Warehouse LIKE 'FG')
but this returns an instance of invWarehouse.StockCode for every instance of corresponding ArCustStkXref.StockCode (>1000 rows) when I want a single instance of each invWarehouse.StockCode for Customer AS001 - should be around 180 rows.