wrong answer in query

below is a small version of my query



the result I get is 4 in the column " invoiced " yet there is on 2

the "status" column only shows 2 records with the result " invoiced"





SELECT [order status].[Required], [order status].[OrderNo], [order status].[BatchNo], [order status].[Invoicenumber], [order status].[NCONo], [order status].[ProductNo], [order status].[Invoice old], [order status].[PCO Purchased old], [order status].[PCO No], [order status].[invoiced], [order status].[parts ordered], [order status].[Waiting purchase], [order status].[Status], [order status].[Waiting purchase1], [order status].[Status1], [order status].[Waiting purchase2]

FROM invoicedetails LEFT JOIN [order status] ON invoicedetails.Invoicenumber = [order status].Invoicenumber;

Code:

---

Status1: IIf(Nz([Invoicenumber],0)>0,"Invoiced",IIf(Nz([invoice old],0)>0,"Invoiced",IIf(Nz([pco No],"")<>"" Or Nz([pco purchased old],"")<>"","parts ordered","Waiting Purchasing")))

---

Code:

---

invoiced: Sum(DCount("*","order status","Status='Invoiced'"))

---

there are 4 orders required on the ORDER NUMBER but I have only invoiced 2 but it seems to be looking at the order details table

Every thing seems to be doubling up



steve

wrong answer in query