Count Lines

[dz2k7]

Pick top$ lines for each item

Hi there,

LEts say I have a table

branch item $COGS

we have lots of same items having different $COGS in different branches and all of them are on one table

I sorted that descending by $COGS.

Now I want to pick for each item only 4 first lines having bigger $COGS.

The rest of the lines should be deleted.

How I can do that?

Thanks

 

[Bilbo_Baggins_Esq]

Having sorted the table by DESCENDING $COGS, I'm a little confused by your request.

"Now I want to pick for each item only 4 first lines having bigger $COGS."

Do you mean that you want the 4 HIGHEST value $COGS, or the 4 LOWEST value $COGS?

Also, do you want these for EACH branch (4 records for each branch remaining), or irregardless of the branch (4 records only remaining)?

Also, is $COGS a numeric, or text value?

 

[dz2k7]

$COGS is dollars of cost of goods sold.
thats a number

I need 4 highest values of $COGS for each item, dosen't matter what branch is that.

 

[Bilbo_Baggins_Esq]

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.

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.

 

[ByteMyzer]

The following query should do what you are asking (substitute highlighted text with actual name):

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
   )
 )
;

 

[dz2k7]

Here you go!
That's what I need.

Thanks

 

[dz2k7]

Let's make it more complicated!

OK Now.

Let's make it more complicated!

What if I want to make this TOP 4 adjustable.

Let's say I have a Form whith a feild to enter this number.

I tried to make it like
SELECT TOP [Forms].[FormName].[FeildName] T3.[$COGS] .....

But it dosen't work.

What should I do?

 

[Rabbie]

Try something along these lines



dim strSQL = "SELECT TOP " & [Forms].[FormName].[FeildName] & " T3.[$COGS] .....

docmd.runsql(strSQL)

 

[Banana]

I don't think you can issue a RunSQL for SELECT queries; only action queries.

If this is supposed to be a form's recordsource, just change the recordsource at runtime, perhaps with Open event.

 

[Rabbie]

Banana

I had what is called a senior moment. So much for the wisdom that comes with age.

 

[Banana]

Trust me, it's not age-related. I've had such moments myself.

 

[dz2k7]

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 .....

What I did wrong?

 

[Banana]

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"...