cjard
December 9th, 2004, 08:04 AM
microsoft access:
I have a table of statistics. Items in the table are identified by a unique (to the item) code, and a code indicating the type of the statistic. Combined, these two elements are the PK
i wish to delete an item but first, i wish to preserve the accumulated statistics for that item, by loading any relevant ones into another item. The database tech is access (i.e. lame)
First i created a query that would return the records for only the two items i was concerned about (the item to store the stat totals, and the item to be deleted)
it was of the form:
SELECT (all fields except the ItemCode) FRM tblHistory WHERE itemCode = ? OR itemcode = ?
this query is coloured blue in the code box below
running this i see a few records of numbers, with the statistics type indicator at the start
I made another query that summed every column, and grouped by the stats indicator. so far so good. now, i want those numbers in those SumOfXXX columns, to replace the relevant numbers in the tblHistory.XXX
this query is green in the box below
I found a similar update query (http://www.experts-exchange.com/Databases/MS_Access/Q_21038845.html) in the form of:
UPDATE Leads INNER JOIN Salespeople ON Leads.Lead_Owner = Salespeople.Original SET Leads.Lead_Owner = [Salespeople].[Replacement];
SalesPeople is a list of people being hired and fired. two columns Original and Replacement, means this query is essentially a Find/Replace on the Leads table.. find (original name) replace with (replacement name)
so i am attempting it, and access cant do it. when i run the query it asks "enter itemcode1" which is expected (also expected for itemcode 2), but then it asks for me to enter the parameter [combinedHist].[HistJanQty] and so on.
i dont know how to tell access that the data it needs to use has been found by the green select statement.
the update statement i have written is shown below in red. data that access cannot find is shown in bold. (i have only marked the first four fields in bold.. it cant find any of them). if my explanation was not clear, hopefully this sql might show you want im trying to achieve:
UPDATE tblHistory INNER JOIN(
SELECT [enter itemcode1] AS HistCode, selTblHist.HistIndicator, Sum(selTblHist.HistJanQty) AS SumOfHistJanQty, Sum(selTblHist.HistJanAmt) AS SumOfHistJanAmt, Sum(selTblHist.HistFebQty) AS SumOfHistFebQty, Sum(selTblHist.HistFebAmt) AS SumOfHistFebAmt, Sum(selTblHist.HistMarQty) AS SumOfHistMarQty, Sum(selTblHist.HistMarAmt) AS SumOfHistMarAmt, Sum(selTblHist.HistAprQty) AS SumOfHistAprQty, Sum(selTblHist.HistAprAmt) AS SumOfHistAprAmt, Sum(selTblHist.HistMayQty) AS SumOfHistMayQty, Sum(selTblHist.HistJunQty) AS SumOfHistJunQty, Sum(selTblHist.HistJunAmt) AS SumOfHistJunAmt, Sum(selTblHist.HistJulQty) AS SumOfHistJulQty, Sum(selTblHist.HistJulAmt) AS SumOfHistJulAmt, Sum(selTblHist.HistAugQty) AS SumOfHistAugQty, Sum(selTblHist.HistAugAmt) AS SumOfHistAugAmt, Sum(selTblHist.HistSepQty) AS SumOfHistSepQty, Sum(selTblHist.HistSepAmt) AS SumOfHistSepAmt, Sum(selTblHist.HistOctQty) AS SumOfHistOctQty, Sum(selTblHist.HistOctAmt) AS SumOfHistOctAmt, Sum(selTblHist.HistNovAmt) AS SumOfHistNovAmt, Sum(selTblHist.HistDecQty) AS SumOfHistDecQty, Sum(selTblHist.HistJanQty2) AS SumOfHistJanQty2, Sum(selTblHist.HistJanAmt2) AS SumOfHistJanAmt2, Sum(selTblHist.HistFebQty2) AS SumOfHistFebQty2, Sum(selTblHist.HistFebAmt2) AS SumOfHistFebAmt2, Sum(selTblHist.HistMarQty2) AS SumOfHistMarQty2, Sum(selTblHist.HistAprQty2) AS SumOfHistAprQty2, Sum(selTblHist.HistAprAmt2) AS SumOfHistAprAmt2, Sum(selTblHist.HistMayQty2) AS SumOfHistMayQty2, Sum(selTblHist.HistMayAmt2) AS SumOfHistMayAmt2, Sum(selTblHist.HistJunAmt2) AS SumOfHistJunAmt2, Sum(selTblHist.HistJulQty2) AS SumOfHistJulQty2, Sum(selTblHist.HistAugQty2) AS SumOfHistAugQty2, Sum(selTblHist.HistAugAmt2) AS SumOfHistAugAmt2, Sum(selTblHist.HistSepQty2) AS SumOfHistSepQty2, Sum(selTblHist.HistSepAmt2) AS SumOfHistSepAmt2, Sum(selTblHist.HistOctQty2) AS SumOfHistOctQty2, Sum(selTblHist.HistOctAmt2) AS SumOfHistOctAmt2, Sum(selTblHist.HistNovQty2) AS SumOfHistNovQty2, Sum(selTblHist.HistNovAmt2) AS SumOfHistNovAmt2, Sum(selTblHist.HistDecAmt2) AS SumOfHistDecAmt2, Sum(selTblHist.HistYearAmt1) AS SumOfHistYearAmt1, Sum(selTblHist.HistYearQty2) AS SumOfHistYearQty2, Sum(selTblHist.HistYearAmt2) AS SumOfHistYearAmt2, Sum(selTblHist.HistYearQty3) AS SumOfHistYearQty3, Sum(selTblHist.HistYearAmt3) AS SumOfHistYearAmt3, Sum(selTblHist.HistYearQty4) AS SumOfHistYearQty4, Sum(selTblHist.HistYearQty5) AS SumOfHistYearQty5, Sum(selTblHist.HistYearAmt5) AS SumOfHistYearAmt5
FROM(
SELECT TblHistory.HistIndicator, TblHistory.HistJanQty, TblHistory.HistJanAmt, TblHistory.HistFebQty, TblHistory.HistFebAmt, TblHistory.HistMarQty, TblHistory.HistMarAmt, TblHistory.HistAprQty, TblHistory.HistAprAmt, TblHistory.HistMayQty, TblHistory.HistJunQty, TblHistory.HistJunAmt, TblHistory.HistJulQty, TblHistory.HistJulAmt, TblHistory.HistAugQty, TblHistory.HistAugAmt, TblHistory.HistSepQty, TblHistory.HistSepAmt, TblHistory.HistOctQty, TblHistory.HistOctAmt, TblHistory.HistNovAmt, TblHistory.HistDecQty, TblHistory.HistDecAmt, TblHistory.HistJanQty2, TblHistory.HistJanAmt2, TblHistory.HistFebQty2, TblHistory.HistFebAmt2, TblHistory.HistMarQty2, TblHistory.HistAprQty2, TblHistory.HistAprAmt2, TblHistory.HistMayQty2, TblHistory.HistMayAmt2, TblHistory.HistJunQty2, TblHistory.HistJunAmt2, TblHistory.HistJulQty2, TblHistory.HistAugQty2, TblHistory.HistAugAmt2, TblHistory.HistSepQty2, TblHistory.HistSepAmt2, TblHistory.HistOctQty2, TblHistory.HistOctAmt2, TblHistory.HistNovQty2, TblHistory.HistNovAmt2, TblHistory.HistDecAmt2, TblHistory.HistYearQty1, TblHistory.HistYearAmt1, TblHistory.HistYearQty2, TblHistory.HistYearAmt2, TblHistory.HistYearQty3, TblHistory.HistYearAmt3, TblHistory.HistYearQty4, TblHistory.HistYearQty5, TblHistory.HistYearAmt5
FROM TblHistory
WHERE (((TblHistory.HistCode)=[enter itemcode1] Or (TblHistory.HistCode)=[enter itemcode2]))
) AS selTblHist
GROUP BY selTblHist.HistIndicator) AS combinedHist
ON TblHistory.HistCode = combinedHist.HistCode AND tblHistory.HistIndicator = combinedHist.HistIndicator
SET TblHistory.HistJanQty = combinedHist.SumOfHistJanQty, TblHistory.HistJanAmt = combinedHist.SumOfHistJanAmt, TblHistory.HistFebQty = combinedHist.SumOfHistFebQty, TblHistory.HistFebAmt = combinedHist.SumOfHistFebAmt, TblHistory.HistMarQty = combinedHist.SumOfHistMarQty, TblHistory.HistMarAmt = combinedHist.SumOfHistMarAmt, TblHistory.HistAprQty = combinedHist.SumOfHistAprQty, TblHistory.HistAprAmt = combinedHist.SumOfHistAprAmt, TblHistory.HistMayQty = combinedHist.SumOfHistMayQty, TblHistory.HistJunQty = combinedHist.SumOfHistJunQty, TblHistory.HistJunAmt = combinedHist.SumOfHistJunAmt, TblHistory.HistJulQty = combinedHist.SumOfHistJulQty, TblHistory.HistJulAmt = combinedHist.SumOfHistJulAmt, TblHistory.HistAugQty = combinedHist.SumOfHistAugQty, TblHistory.HistAugAmt = combinedHist.SumOfHistAugAmt, TblHistory.HistSepQty = combinedHist.SumOfHistSepQty, TblHistory.HistSepAmt = combinedHist.SumOfHistSepAmt, TblHistory.HistOctQty = combinedHist.SumOfHistOctQty, TblHistory.HistOctAmt = combinedHist.SumOfHistOctAmt, TblHistory.HistNovAmt = combinedHist.SumOfHistNovAmt, TblHistory.HistDecQty = combinedHist.SumOfHistDecQty, TblHistory.HistDecAmt = combinedHist.SumOfHistDecAmt, TblHistory.HistJanQty2 = combinedHist.SumOfHistJanQty2, TblHistory.HistJanAmt2 = combinedHist.SumOfHistJanAmt2, TblHistory.HistFebQty2 = combinedHist.SumOfHistFebQty2, TblHistory.HistFebAmt2 = combinedHist.SumOfHistFebAmt2, TblHistory.HistMarQty2 = combinedHist.SumOfHistMarQty2, TblHistory.HistAprQty2 = combinedHist.SumOfHistAprQty2, TblHistory.HistAprAmt2 = combinedHist.SumOfHistAprAmt2, TblHistory.HistMayQty2 = combinedHist.SumOfHistMayQty2, TblHistory.HistMayAmt2 = combinedHist.SumOfHistMayAmt2, TblHistory.HistJunQty2 = combinedHist.SumOfHistJunQty2, TblHistory.HistJunAmt2 = combinedHist.SumOfHistJunAmt2, TblHistory.HistJulQty2 = combinedHist.SumOfHistJulQty2, TblHistory.HistAugQty2 = combinedHist.SumOfHistAugQty2, TblHistory.HistAugAmt2 = combinedHist.SumOfHistAugAmt2, TblHistory.HistSepQty2 = combinedHist.SumOfHistSepQty2, TblHistory.HistSepAmt2 = combinedHist.SumOfHistSepAmt2, TblHistory.HistOctQty2 = combinedHist.SumOfHistOctQty2, TblHistory.HistOctAmt2 = combinedHist.SumOfHistOctAmt2, TblHistory.HistNovQty2 = combinedHist.SumOfHistNovQty2, TblHistory.HistNovAmt2 = combinedHist.SumOfHistNovAmt2, TblHistory.HistDecAmt2 = combinedHist.SumOfHistDecAmt2, TblHistory.HistYearQty1 = combinedHist.SumOfHistYearQty1, TblHistory.HistYearAmt1 = combinedHist.SumOfHistYearAmt1, TblHistory.HistYearQty2 = combinedHist.SumOfHistYearQty2, TblHistory.HistYearAmt2 = combinedHist.SumOfHistYearAmt2, TblHistory.HistYearQty3 = combinedHist.SumOfHistYearQty3, TblHistory.HistYearAmt3 = combinedHist.SumOfHistYearAmt3, TblHistory.HistYearQty4 = combinedHist.SumOfHistYearQty4, TblHistory.HistYearQty5 = combinedHist.SumOfHistYearQty5, TblHistory.HistYearAmt5 = combinedHist.SumOfHistYearAmt5;
here is the tblHistory, if you wish to load this up yourself and see some example data (
i am using 0002691 and 0003426 as the item codes. the II type statistic for september for these itemcodes are 2 and 1 respectively. the IS type indicators are 3 and 3. im hoping to get it to the point where 0002691 has semptember II stat of 3 (two plus one) and IS stat of 6 (three plus three)
once i can get the table thus updated, i can DELETE * .. WHERE ItemCode = 0003426 and ergo the item codes are merged; it was as if 0003426 never existed
thanks in advance
I have a table of statistics. Items in the table are identified by a unique (to the item) code, and a code indicating the type of the statistic. Combined, these two elements are the PK
i wish to delete an item but first, i wish to preserve the accumulated statistics for that item, by loading any relevant ones into another item. The database tech is access (i.e. lame)
First i created a query that would return the records for only the two items i was concerned about (the item to store the stat totals, and the item to be deleted)
it was of the form:
SELECT (all fields except the ItemCode) FRM tblHistory WHERE itemCode = ? OR itemcode = ?
this query is coloured blue in the code box below
running this i see a few records of numbers, with the statistics type indicator at the start
I made another query that summed every column, and grouped by the stats indicator. so far so good. now, i want those numbers in those SumOfXXX columns, to replace the relevant numbers in the tblHistory.XXX
this query is green in the box below
I found a similar update query (http://www.experts-exchange.com/Databases/MS_Access/Q_21038845.html) in the form of:
UPDATE Leads INNER JOIN Salespeople ON Leads.Lead_Owner = Salespeople.Original SET Leads.Lead_Owner = [Salespeople].[Replacement];
SalesPeople is a list of people being hired and fired. two columns Original and Replacement, means this query is essentially a Find/Replace on the Leads table.. find (original name) replace with (replacement name)
so i am attempting it, and access cant do it. when i run the query it asks "enter itemcode1" which is expected (also expected for itemcode 2), but then it asks for me to enter the parameter [combinedHist].[HistJanQty] and so on.
i dont know how to tell access that the data it needs to use has been found by the green select statement.
the update statement i have written is shown below in red. data that access cannot find is shown in bold. (i have only marked the first four fields in bold.. it cant find any of them). if my explanation was not clear, hopefully this sql might show you want im trying to achieve:
UPDATE tblHistory INNER JOIN(
SELECT [enter itemcode1] AS HistCode, selTblHist.HistIndicator, Sum(selTblHist.HistJanQty) AS SumOfHistJanQty, Sum(selTblHist.HistJanAmt) AS SumOfHistJanAmt, Sum(selTblHist.HistFebQty) AS SumOfHistFebQty, Sum(selTblHist.HistFebAmt) AS SumOfHistFebAmt, Sum(selTblHist.HistMarQty) AS SumOfHistMarQty, Sum(selTblHist.HistMarAmt) AS SumOfHistMarAmt, Sum(selTblHist.HistAprQty) AS SumOfHistAprQty, Sum(selTblHist.HistAprAmt) AS SumOfHistAprAmt, Sum(selTblHist.HistMayQty) AS SumOfHistMayQty, Sum(selTblHist.HistJunQty) AS SumOfHistJunQty, Sum(selTblHist.HistJunAmt) AS SumOfHistJunAmt, Sum(selTblHist.HistJulQty) AS SumOfHistJulQty, Sum(selTblHist.HistJulAmt) AS SumOfHistJulAmt, Sum(selTblHist.HistAugQty) AS SumOfHistAugQty, Sum(selTblHist.HistAugAmt) AS SumOfHistAugAmt, Sum(selTblHist.HistSepQty) AS SumOfHistSepQty, Sum(selTblHist.HistSepAmt) AS SumOfHistSepAmt, Sum(selTblHist.HistOctQty) AS SumOfHistOctQty, Sum(selTblHist.HistOctAmt) AS SumOfHistOctAmt, Sum(selTblHist.HistNovAmt) AS SumOfHistNovAmt, Sum(selTblHist.HistDecQty) AS SumOfHistDecQty, Sum(selTblHist.HistJanQty2) AS SumOfHistJanQty2, Sum(selTblHist.HistJanAmt2) AS SumOfHistJanAmt2, Sum(selTblHist.HistFebQty2) AS SumOfHistFebQty2, Sum(selTblHist.HistFebAmt2) AS SumOfHistFebAmt2, Sum(selTblHist.HistMarQty2) AS SumOfHistMarQty2, Sum(selTblHist.HistAprQty2) AS SumOfHistAprQty2, Sum(selTblHist.HistAprAmt2) AS SumOfHistAprAmt2, Sum(selTblHist.HistMayQty2) AS SumOfHistMayQty2, Sum(selTblHist.HistMayAmt2) AS SumOfHistMayAmt2, Sum(selTblHist.HistJunAmt2) AS SumOfHistJunAmt2, Sum(selTblHist.HistJulQty2) AS SumOfHistJulQty2, Sum(selTblHist.HistAugQty2) AS SumOfHistAugQty2, Sum(selTblHist.HistAugAmt2) AS SumOfHistAugAmt2, Sum(selTblHist.HistSepQty2) AS SumOfHistSepQty2, Sum(selTblHist.HistSepAmt2) AS SumOfHistSepAmt2, Sum(selTblHist.HistOctQty2) AS SumOfHistOctQty2, Sum(selTblHist.HistOctAmt2) AS SumOfHistOctAmt2, Sum(selTblHist.HistNovQty2) AS SumOfHistNovQty2, Sum(selTblHist.HistNovAmt2) AS SumOfHistNovAmt2, Sum(selTblHist.HistDecAmt2) AS SumOfHistDecAmt2, Sum(selTblHist.HistYearAmt1) AS SumOfHistYearAmt1, Sum(selTblHist.HistYearQty2) AS SumOfHistYearQty2, Sum(selTblHist.HistYearAmt2) AS SumOfHistYearAmt2, Sum(selTblHist.HistYearQty3) AS SumOfHistYearQty3, Sum(selTblHist.HistYearAmt3) AS SumOfHistYearAmt3, Sum(selTblHist.HistYearQty4) AS SumOfHistYearQty4, Sum(selTblHist.HistYearQty5) AS SumOfHistYearQty5, Sum(selTblHist.HistYearAmt5) AS SumOfHistYearAmt5
FROM(
SELECT TblHistory.HistIndicator, TblHistory.HistJanQty, TblHistory.HistJanAmt, TblHistory.HistFebQty, TblHistory.HistFebAmt, TblHistory.HistMarQty, TblHistory.HistMarAmt, TblHistory.HistAprQty, TblHistory.HistAprAmt, TblHistory.HistMayQty, TblHistory.HistJunQty, TblHistory.HistJunAmt, TblHistory.HistJulQty, TblHistory.HistJulAmt, TblHistory.HistAugQty, TblHistory.HistAugAmt, TblHistory.HistSepQty, TblHistory.HistSepAmt, TblHistory.HistOctQty, TblHistory.HistOctAmt, TblHistory.HistNovAmt, TblHistory.HistDecQty, TblHistory.HistDecAmt, TblHistory.HistJanQty2, TblHistory.HistJanAmt2, TblHistory.HistFebQty2, TblHistory.HistFebAmt2, TblHistory.HistMarQty2, TblHistory.HistAprQty2, TblHistory.HistAprAmt2, TblHistory.HistMayQty2, TblHistory.HistMayAmt2, TblHistory.HistJunQty2, TblHistory.HistJunAmt2, TblHistory.HistJulQty2, TblHistory.HistAugQty2, TblHistory.HistAugAmt2, TblHistory.HistSepQty2, TblHistory.HistSepAmt2, TblHistory.HistOctQty2, TblHistory.HistOctAmt2, TblHistory.HistNovQty2, TblHistory.HistNovAmt2, TblHistory.HistDecAmt2, TblHistory.HistYearQty1, TblHistory.HistYearAmt1, TblHistory.HistYearQty2, TblHistory.HistYearAmt2, TblHistory.HistYearQty3, TblHistory.HistYearAmt3, TblHistory.HistYearQty4, TblHistory.HistYearQty5, TblHistory.HistYearAmt5
FROM TblHistory
WHERE (((TblHistory.HistCode)=[enter itemcode1] Or (TblHistory.HistCode)=[enter itemcode2]))
) AS selTblHist
GROUP BY selTblHist.HistIndicator) AS combinedHist
ON TblHistory.HistCode = combinedHist.HistCode AND tblHistory.HistIndicator = combinedHist.HistIndicator
SET TblHistory.HistJanQty = combinedHist.SumOfHistJanQty, TblHistory.HistJanAmt = combinedHist.SumOfHistJanAmt, TblHistory.HistFebQty = combinedHist.SumOfHistFebQty, TblHistory.HistFebAmt = combinedHist.SumOfHistFebAmt, TblHistory.HistMarQty = combinedHist.SumOfHistMarQty, TblHistory.HistMarAmt = combinedHist.SumOfHistMarAmt, TblHistory.HistAprQty = combinedHist.SumOfHistAprQty, TblHistory.HistAprAmt = combinedHist.SumOfHistAprAmt, TblHistory.HistMayQty = combinedHist.SumOfHistMayQty, TblHistory.HistJunQty = combinedHist.SumOfHistJunQty, TblHistory.HistJunAmt = combinedHist.SumOfHistJunAmt, TblHistory.HistJulQty = combinedHist.SumOfHistJulQty, TblHistory.HistJulAmt = combinedHist.SumOfHistJulAmt, TblHistory.HistAugQty = combinedHist.SumOfHistAugQty, TblHistory.HistAugAmt = combinedHist.SumOfHistAugAmt, TblHistory.HistSepQty = combinedHist.SumOfHistSepQty, TblHistory.HistSepAmt = combinedHist.SumOfHistSepAmt, TblHistory.HistOctQty = combinedHist.SumOfHistOctQty, TblHistory.HistOctAmt = combinedHist.SumOfHistOctAmt, TblHistory.HistNovAmt = combinedHist.SumOfHistNovAmt, TblHistory.HistDecQty = combinedHist.SumOfHistDecQty, TblHistory.HistDecAmt = combinedHist.SumOfHistDecAmt, TblHistory.HistJanQty2 = combinedHist.SumOfHistJanQty2, TblHistory.HistJanAmt2 = combinedHist.SumOfHistJanAmt2, TblHistory.HistFebQty2 = combinedHist.SumOfHistFebQty2, TblHistory.HistFebAmt2 = combinedHist.SumOfHistFebAmt2, TblHistory.HistMarQty2 = combinedHist.SumOfHistMarQty2, TblHistory.HistAprQty2 = combinedHist.SumOfHistAprQty2, TblHistory.HistAprAmt2 = combinedHist.SumOfHistAprAmt2, TblHistory.HistMayQty2 = combinedHist.SumOfHistMayQty2, TblHistory.HistMayAmt2 = combinedHist.SumOfHistMayAmt2, TblHistory.HistJunQty2 = combinedHist.SumOfHistJunQty2, TblHistory.HistJunAmt2 = combinedHist.SumOfHistJunAmt2, TblHistory.HistJulQty2 = combinedHist.SumOfHistJulQty2, TblHistory.HistAugQty2 = combinedHist.SumOfHistAugQty2, TblHistory.HistAugAmt2 = combinedHist.SumOfHistAugAmt2, TblHistory.HistSepQty2 = combinedHist.SumOfHistSepQty2, TblHistory.HistSepAmt2 = combinedHist.SumOfHistSepAmt2, TblHistory.HistOctQty2 = combinedHist.SumOfHistOctQty2, TblHistory.HistOctAmt2 = combinedHist.SumOfHistOctAmt2, TblHistory.HistNovQty2 = combinedHist.SumOfHistNovQty2, TblHistory.HistNovAmt2 = combinedHist.SumOfHistNovAmt2, TblHistory.HistDecAmt2 = combinedHist.SumOfHistDecAmt2, TblHistory.HistYearQty1 = combinedHist.SumOfHistYearQty1, TblHistory.HistYearAmt1 = combinedHist.SumOfHistYearAmt1, TblHistory.HistYearQty2 = combinedHist.SumOfHistYearQty2, TblHistory.HistYearAmt2 = combinedHist.SumOfHistYearAmt2, TblHistory.HistYearQty3 = combinedHist.SumOfHistYearQty3, TblHistory.HistYearAmt3 = combinedHist.SumOfHistYearAmt3, TblHistory.HistYearQty4 = combinedHist.SumOfHistYearQty4, TblHistory.HistYearQty5 = combinedHist.SumOfHistYearQty5, TblHistory.HistYearAmt5 = combinedHist.SumOfHistYearAmt5;
here is the tblHistory, if you wish to load this up yourself and see some example data (
i am using 0002691 and 0003426 as the item codes. the II type statistic for september for these itemcodes are 2 and 1 respectively. the IS type indicators are 3 and 3. im hoping to get it to the point where 0002691 has semptember II stat of 3 (two plus one) and IS stat of 6 (three plus three)
once i can get the table thus updated, i can DELETE * .. WHERE ItemCode = 0003426 and ergo the item codes are merged; it was as if 0003426 never existed
thanks in advance