KSID, Client, LOCATION, Finisher, NPB, sort_adj, SHIPDATE, numb, pay, P_Charge, Extra_Charge, Weight_Fee, WEIGHT, DEAD_PAY, DEAD_WEIGHT, DEAD, SLOWS_PAY, SLOWS_WEIGHT, NPPC, SLOWS, RESALES_PAY, RESALES_WEIGHT, RESALES, SLOW_NPPC, SLOW_NPB, slow_weight_fee, resale_nppc, resale_npb, salecode.Customer, salecode.SaleType, salecode.SALECODE, salecode.SALESACCT, salecode.[desc]
Invoice LEFT JOIN SALECODE ON Invoice.Client = salecode.customer
This select statement is returning the correct data(4 records for each invoice.ksid), but that causes a problem. The SALECODE table contains data used by an accounting program, and each record in the Invoice table can match 4 times in the SALECODE table. For example, the SALECODE table might have 4 records in it with the same customer field, but just a different salecode.
So it could be like this
Since my join finds all 4 matches, each query is returning 4 times the data that is needed. This is all used for an export file, and my actual code is doing all the data writing. The only fields I can join on are invoice.Client and Salecode.Customer. Is there a way to narrow it down to only returning 1 value for each invoice ID?