Need help with SQL statement

• rickyc1

  SSC Veteran

  Points: 271

  More actions

  February 11, 2004 at 7:55 am

  #65943

  The below sql adds up sales order item prices so I can create a sales order total based on an invoice date (Create sales order total based on month sales was invoiced). The problem is occurring when there are multiple invoices in the first month, the sales order is doubling. As you can see why, I’m inner joining all three tables. I guess I assumed this would never occur when I wrote it and didn't build for it. This is written is Access 2000.

  If anyone can help me grab only one occurrence from the ARmast table so the total doesn't get doubled that would be great? I'm hitting a brick wall.

  Code:

  INSERT INTO tblSOWTotal ( SO, CustNo, CustName, OrderDte, UpDteSOTot, ShowOnReport, SOTotal )

  SELECT DISTINCT dbo_sorels.fsono, dbo_somast.fcustno, dbo_somast.fcompany, dbo_somast.forderdate, Date() AS Expr1, "Y" AS ShowOnReport, Sum(dbo_sorels.fnetprice) AS SOTotal

  FROM dbo_armast INNER JOIN (dbo_somast INNER JOIN dbo_sorels ON dbo_somast.fsono = dbo_sorels.fsono)

  ON dbo_armast.fsono = dbo_somast.fsono

  GROUP BY dbo_sorels.fsono, dbo_somast.fcustno, dbo_somast.fcompany, dbo_somast.forderdate, dbo_armast.finvdate, dbo_armast.finvtype

  HAVING (((Sum(dbo_sorels.fnetprice))<>0) AND ((dbo_armast.finvdate) Between [Forms]![frmRunAppendQueries]![txtStartDte] And [Forms]![frmRunAppendQueries]![txtEndDte]) AND ((dbo_armast.finvtype)="N"))

  ORDER BY dbo_sorels.fsono;

• cwaddle

  SSC Veteran

  Points: 289

  More actions

  February 12, 2004 at 4:53 pm

  #493908

  Create a query that returns the distinct records from ARmast. Then use that query in the above query. That should work for you.