Well, my method may be more clunky and less elegant than what others may provide, but here it goes:
Write a GROUP BY query based off your origin table
The first column should be Group BY the Item field, and set the second column to be the MAX of $COGS
Next, (assuming there could be duplicate MAX of $COGS, but also assuming the origin table has a Unique_ID column) write another GROUP BY query based off the origin table AND the first group by query with JOINS on the Item and $COGS columns. Make the first column GROUP BY on the ITEM column, and the second column should be MAX of Unique_ID.
Next write an APPEND query based off the Origin table and the second Group By query with a JOIN on the Unique_ID column, and set it to APPEND to an empty copy of the Origin table (if the Unique_ID column in the origin table is an AutoNumber column, be sure to CHANGE it in the copy).
Next write a DELETE query based off the origin table and the FIRST Group By query, with JOINS on the Item and $COGS columns. This will delete ALL of the MAX $COGS for each respective item (including duplicates, if any).
If you actually want the 4 highest $COGS for each item to potentially be duplicated, then base this delete query off the SECOND GROUP BY query with a single JOIN on the Unique_ID column. This will delete the MAX Unique_ID for each MAX $COGS for each Item.
Now you'll want to run this from VBA code
Create a variable "Record_Counter"
Define its value as 0
In a Do While Record_Counter < 5
Keep repeating the Append and Delete queries and raise the Record Counter by 1 after each cycle.
Then, after you exit the Loop, you can run anothre query that deletes ALL records from the origin table.
You'll be left with a seperate table that has the MAX $COGS for each item and a clean origin table.
Code:
Dim Record_Counter as long
Record_Counter = 0
Do While Record_Counter < 5
DoCmd.OpenQuery "Append_Query", acNormal, acEdit
DoCmd.OpenQuery "Delete_Query", acNormal, acEdit
Record_Counter = Record_Counter + 1
Loop
DoCmd.OpenQuery "Delete_All_Origin_Records_Query", acNormal, acEdit
As I mentioned, there may be more elegant methods using all VBA, but I'm not that well versed in VBA (but learning all the time) and don't know how well versed you are in VBA either.
The following query should do what you are asking (substitute highlighted text with actual name):
Code:
DELETE T1.*
FROM [b][i]MyTable[/i][/b] T1
WHERE T1.[$COGS] <
(SELECT MIN(T2.[$COGS])
FROM [b][i]MyTable[/i][/b] T2
WHERE T2.item = T1.item
AND T2.[$COGS] IN
(SELECT TOP 4 T3.[$COGS]
FROM [b][i]MyTable[/i][/b] T3
WHERE T3.item = T1.item
ORDER BY T3.[$COGS] DESC
)
)
;
This is actually Delete quiery
So I tried to put the whole thing in there and make something like this:
dim strSQL = "DELETE T1.* FROM MyTable T1 WHERE T1.[$COGS] < (SELECT MIN(T2.[$COGS]) FROM MyTable T2 WHERE T2.item = T1.item AND T2.[$COGS] IN (SELECT TOP " & [Forms].[FormName].[FeildName] & "T3.[$COGS] FROM MyTable T3 WHERE T3.item = T1.item ORDER BY T3.[$COGS] DESC ) ) ;"
When I tried to put this in a module, it doesn't take the line .....
hm, you're trying to delete something that will meet all four criteria? From one same table aliased three time?
If you view the query in query builder, and click "Show Datasheet" (not Run!), does it show you anything at all? If nothing, the query is probably overconstrained.
But I don't understand what you mean by "it doesn't take the line"...