1 Attachment(s)
help with this rather large query designed to merge records...
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 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:
Code:
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
Re: help with this rather large query designed to merge records...
i fixed a raft of minor sql problems; fieldnames missing from queries (happens when you add 60 fields by hand :( )
and got this:
Code:
UPDATE tblHistory, (
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.HistMayAmt) AS SumOfHistMayAmt, 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.HistNovQty) AS SumOfHistNovQty, Sum(selTblHist.HistNovAmt) AS SumOfHistNovAmt, Sum(selTblHist.HistDecQty) AS SumOfHistDecQty, Sum(selTblHist.HistDecAmt) AS SumOfHistDecAmt, 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.HistMarAmt2) AS SumOfHistMarAmt2, Sum(selTblHist.HistAprQty2) AS SumOfHistAprQty2, Sum(selTblHist.HistAprAmt2) AS SumOfHistAprAmt2, Sum(selTblHist.HistMayQty2) AS SumOfHistMayQty2, Sum(selTblHist.HistMayAmt2) AS SumOfHistMayAmt2, Sum(selTblHist.HistJunQty2) AS SumOfHistJunQty2, Sum(selTblHist.HistJunAmt2) AS SumOfHistJunAmt2, Sum(selTblHist.HistJulQty2) AS SumOfHistJulQty2, Sum(selTblHist.HistJulAmt2) AS SumOfHistJulAmt2, 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.HistDecQty2) AS SumOfHistDecQty2, Sum(selTblHist.HistDecAmt2) AS SumOfHistDecAmt2, Sum(selTblHist.HistYearQty1) AS SumOfHistYearQty1, 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.HistYearAmt4) AS SumOfHistYearAmt4, Sum(selTblHist.HistYearQty5) AS SumOfHistYearQty5, Sum(selTblHist.HistYearAmt5) AS SumOfHistYearAmt5 INTO tmpHistory
FROM(
SELECT TblHistory.HistIndicator, TblHistory.HistJanQty, TblHistory.HistJanAmt, TblHistory.HistFebQty, TblHistory.HistFebAmt, TblHistory.HistMarQty, TblHistory.HistMarAmt, TblHistory.HistAprQty, TblHistory.HistAprAmt, TblHistory.HistMayQty, TblHistory.HistMayAmt, TblHistory.HistJunQty, TblHistory.HistJunAmt, TblHistory.HistJulQty, TblHistory.HistJulAmt, TblHistory.HistAugQty, TblHistory.HistAugAmt, TblHistory.HistSepQty, TblHistory.HistSepAmt, TblHistory.HistOctQty, TblHistory.HistOctAmt, TblHistory.HistNovQty, TblHistory.HistNovAmt, TblHistory.HistDecQty, TblHistory.HistDecAmt, TblHistory.HistJanQty2, TblHistory.HistJanAmt2, TblHistory.HistFebQty2, TblHistory.HistFebAmt2, TblHistory.HistMarQty2, TblHistory.HistMarAmt2, TblHistory.HistAprQty2, TblHistory.HistAprAmt2, TblHistory.HistMayQty2, TblHistory.HistMayAmt2, TblHistory.HistJunQty2, TblHistory.HistJunAmt2, TblHistory.HistJulQty2, TblHistory.HistJulAmt2, TblHistory.HistAugQty2, TblHistory.HistAugAmt2, TblHistory.HistSepQty2, TblHistory.HistSepAmt2, TblHistory.HistOctQty2, TblHistory.HistOctAmt2, TblHistory.HistNovQty2, TblHistory.HistNovAmt2, TblHistory.HistDecQty2, TblHistory.HistDecAmt2, TblHistory.HistYearQty1, TblHistory.HistYearAmt1, TblHistory.HistYearQty2, TblHistory.HistYearAmt2, TblHistory.HistYearQty3, TblHistory.HistYearAmt3, TblHistory.HistYearQty4, TblHistory.HistYearAmt4, TblHistory.HistYearQty5, TblHistory.HistYearAmt5
FROM TblHistory
WHERE (((TblHistory.HistCode)=[enter itemcode1] Or (TblHistory.HistCode)=[enter itemcode2]))
) AS selTblHist
GROUP BY selTblHist.HistIndicator) AS tmpHistory
SET TblHistory.HistJanQty = tmpHistory.SumOfHistJanQty, TblHistory.HistJanAmt = tmpHistory.SumOfHistJanAmt, TblHistory.HistFebQty = tmpHistory.SumOfHistFebQty, TblHistory.HistFebAmt = tmpHistory.SumOfHistFebAmt, TblHistory.HistMarQty = tmpHistory.SumOfHistMarQty, TblHistory.HistMarAmt = tmpHistory.SumOfHistMarAmt, TblHistory.HistAprQty = tmpHistory.SumOfHistAprQty, TblHistory.HistAprAmt = tmpHistory.SumOfHistAprAmt, TblHistory.HistMayQty = tmpHistory.SumOfHistMayQty, TblHistory.HistMayAmt = tmpHistory.SumOfHistMayAmt, TblHistory.HistJunQty = tmpHistory.SumOfHistJunQty, TblHistory.HistJunAmt = tmpHistory.SumOfHistJunAmt, TblHistory.HistJulQty = tmpHistory.SumOfHistJulQty, TblHistory.HistJulAmt = tmpHistory.SumOfHistJulAmt, TblHistory.HistAugQty = tmpHistory.SumOfHistAugQty, TblHistory.HistAugAmt = tmpHistory.SumOfHistAugAmt, TblHistory.HistSepQty = tmpHistory.SumOfHistSepQty, TblHistory.HistSepAmt = tmpHistory.SumOfHistSepAmt, TblHistory.HistOctQty = tmpHistory.SumOfHistOctQty, TblHistory.HistOctAmt = tmpHistory.SumOfHistOctAmt, TblHistory.HistNovQty = tmpHistory.SumOfHistNovQty, TblHistory.HistNovAmt = tmpHistory.SumOfHistNovAmt, TblHistory.HistDecQty = tmpHistory.SumOfHistDecQty, TblHistory.HistDecAmt = tmpHistory.SumOfHistDecAmt, TblHistory.HistJanQty2 = tmpHistory.SumOfHistJanQty2, TblHistory.HistJanAmt2 = tmpHistory.SumOfHistJanAmt2, TblHistory.HistFebQty2 = tmpHistory.SumOfHistFebQty2, TblHistory.HistFebAmt2 = tmpHistory.SumOfHistFebAmt2, TblHistory.HistMarQty2 = tmpHistory.SumOfHistMarQty2, TblHistory.HistMarAmt2 = tmpHistory.SumOfHistMarAmt2, TblHistory.HistAprQty2 = tmpHistory.SumOfHistAprQty2, TblHistory.HistAprAmt2 = tmpHistory.SumOfHistAprAmt2, TblHistory.HistMayQty2 = tmpHistory.SumOfHistMayQty2, TblHistory.HistMayAmt2 = tmpHistory.SumOfHistMayAmt2, TblHistory.HistJunQty2 = tmpHistory.SumOfHistJunQty2, TblHistory.HistJunAmt2 = tmpHistory.SumOfHistJunAmt2, TblHistory.HistJulQty2 = tmpHistory.SumOfHistJulQty2, TblHistory.HistJulAmt2 = tmpHistory.SumOfHistJulAmt2, TblHistory.HistAugQty2 = tmpHistory.SumOfHistAugQty2, TblHistory.HistAugAmt2 = tmpHistory.SumOfHistAugAmt2, TblHistory.HistSepQty2 = tmpHistory.SumOfHistSepQty2, TblHistory.HistSepAmt2 = tmpHistory.SumOfHistSepAmt2, TblHistory.HistOctQty2 = tmpHistory.SumOfHistOctQty2, TblHistory.HistOctAmt2 = tmpHistory.SumOfHistOctAmt2, TblHistory.HistNovQty2 = tmpHistory.SumOfHistNovQty2, TblHistory.HistNovAmt2 = tmpHistory.SumOfHistNovAmt2, TblHistory.HistDecQty2 = tmpHistory.SumOfHistDecQty2, TblHistory.HistDecAmt2 = tmpHistory.SumOfHistDecAmt2, TblHistory.HistYearQty1 = tmpHistory.SumOfHistYearQty1, TblHistory.HistYearAmt1 = tmpHistory.SumOfHistYearAmt1, TblHistory.HistYearQty2 = tmpHistory.SumOfHistYearQty2, TblHistory.HistYearAmt2 = tmpHistory.SumOfHistYearAmt2, TblHistory.HistYearQty3 = tmpHistory.SumOfHistYearQty3, TblHistory.HistYearAmt3 = tmpHistory.SumOfHistYearAmt3, TblHistory.HistYearQty4 = tmpHistory.SumOfHistYearQty4, TblHistory.HistYearAmt4 = tmpHistory.SumOfHistYearAmt4, TblHistory.HistYearQty5 = tmpHistory.SumOfHistYearQty5, TblHistory.HistYearAmt5 = tmpHistory.SumOfHistYearAmt5
WHERE ((TblHistory.HistCode = tmpHistory.HistCode) AND (TblHistory.HistIndicator = tmpHistory.HistIndicator));
and i now know that "An action query cannot be used as a row source"
Sigh.. well, at least it should be fairly easy to create a temporary table..
