I have a query that removes multiple records from my table, mostly for when we recieve stock for existing products in the database
i'm using the code below to identify the records and i have another query which removes these records.
I have one little bug where if i have a new product on multiple invoices over the same period they show up in the duplicate records query.
ie, an old product with new stock recieved will have 2 records with the same productID (but a unique StocktakeAutoID),
one of those records will have quantites in stockenv/fold/cpt from the previous report - the other record will have 0 quantity.
whereas a new product, with quantites recieved more than once in the same period will have multiple records with 0 quantity;
While this is technically correct, i still need to retain only one of these.
Each of these duplicate records are autoID, so ultimately i need to select all duplicates, except for the one with the lowest autoID
This is what is returned from the query:
All the products except for 'CSO08MS' are there correctly.
CSO08MS is a new product, so I need to only keep one of these records (eg autoID 40666)
if product count is >1 and the qty's for both are 0, then i need to SELECT only the one(s) with the higher StocktakeAutoID.
Is this possible within this query or will i have to create another query?
i'm using the code below to identify the records and i have another query which removes these records.
Code:
SELECT product, ReportID, StocktakeAutoID, StockEnv, StockFold, StockCpt, StockQtyA, stocktakedate, StockReturned
FROM tblstocktake
WHERE (((product) In (SELECT [product] FROM [tblstocktake] As Tmp GROUP BY [product],[ReportID] HAVING Count(*)>1 And [ReportID] = [ReportID])) AND ((ReportID)=[forms]![frmnavigation]![masterreportfilter]) AND ((StockEnv)=0) AND ((StockFold)=0) AND ((StockCpt)=0) AND ((StockQtyA)=0) AND ((StockReturned)=0))
ORDER BY product, ReportID;ie, an old product with new stock recieved will have 2 records with the same productID (but a unique StocktakeAutoID),
one of those records will have quantites in stockenv/fold/cpt from the previous report - the other record will have 0 quantity.
whereas a new product, with quantites recieved more than once in the same period will have multiple records with 0 quantity;
While this is technically correct, i still need to retain only one of these.
Each of these duplicate records are autoID, so ultimately i need to select all duplicates, except for the one with the lowest autoID
This is what is returned from the query:
Code:
StocktakeAutoID product ReportID Envelope Folder Compactus On Hand Date Returned
40684 CSB08MSO 84 0 0 0 0 28/01/2009 9:53:06 AM 0
40668 CSF08AST 84 0 0 0 0 28/01/2009 9:53:06 AM 0
40669 CSF08MS 84 0 0 0 0 28/01/2009 9:53:06 AM 0
40683 CSG08AST 84 0 0 0 0 28/01/2009 9:53:06 AM 0
40682 CSO08MS 84 0 0 0 0 28/01/2009 9:53:06 AM 0
40666 CSO08MS 84 0 0 0 0 28/01/2009 9:53:06 AM 0
40674 DSF07SBND 84 0 0 0 0 28/01/2009 9:53:06 AM 0All the products except for 'CSO08MS' are there correctly.
CSO08MS is a new product, so I need to only keep one of these records (eg autoID 40666)
if product count is >1 and the qty's for both are 0, then i need to SELECT only the one(s) with the higher StocktakeAutoID.
Is this possible within this query or will i have to create another query?
