The problem is that the Customer Contacts and Customer Ship-to Contacts are both stored in this table and the "connum" field starts over with each distinct ship to. So if your orderhed.prcconnum was equal to 1 and you had 8 ship-to's, you could get up to 9 records. To solve this, you need to specify that custcnt.shiptonum = "".
--- In vantage@yahoogroups.com, "Emily" <emverbeke@...> wrote:
>
> I'm pretty sure trying to write any kind of query/report with the CustCnt table is the worst!
>
> I'm just trying to write a report with sales orders and the customer contact associated with the order.
>
> Initial I wrote:
> for each OrderHed where ( OrderHed.OrderDate >= 08/27/2010) no-lock , each OrderDtl where (OrderHed.Company = OrderDtl.Company and OrderHed.CustNum = OrderDtl.CustNum and OrderHed.OrderNum = OrderDtl.OrderNum) no-lock , each Customer where (OrderHed.Company = Customer.Company and OrderHed.CustNum = Customer.CustNum) no-lock , each CustCnt outer-join where (OrderHed.Company = CustCnt.Company and OrderHed.CustNum = CustCnt.CustNum and OrderHed.PrcConNum = CustCnt.ConNum) no-lock .
>
> At this point I was getting some lines that were fine and other lines that were repeated. The number of times a line was repeated was not correlated to the number of contacts, line releases, or anything else I could readily see. We also don't have any contacts that are identified as the Primary Purchasing Contact.
>
> Then in my trial and error method of problem-solving I changed the table relationship between OrderHed and CustCnt to "first" and it looks like it is working. Will someone explain why switching it from each to first fixed it? I'm trying to learn so it wouldn't take me so long in the future to write this queries. Thanks in advance.
>
> Emily
>